Connection Strings

sh0 does not wire databases into your apps for you. You read the connection details, create a database and a user for the app, and set DATABASE_URL yourself. This page shows each step with the commands as they run.

Finding the connection details

In the dashboard, open Databases, then the server: Connection Info shows the Internal Host, the port, the Root Password and a Connection URL to copy.

From the CLI, sh0 db connection-info takes the server ID printed at creation (or by sh0 db list):

$ sh0 db create --name my-pg --engine postgres
Database server 'my-pg' created (id: e8afafa4-2ff1-4088-8276-fccb839734a8, engine: postgres 16)

$ sh0 db connection-info e8afafa4-2ff1-4088-8276-fccb839734a8
Connection Info:
  Host:     my-pg.internal
  Port:     32962
  Username: postgres
  Password: <password>
  Internal: postgresql://postgres:<password>@my-pg.internal:5432/
  External: postgresql://postgres:<password>@localhost:32962/

Use the Internal URL from your apps. The External URL points to a port bound to 127.0.0.1 on the server: it only answers from the server itself (see “Connecting from your machine”).

A database and a user for each app

A server starts with no database and only the administrator account. Give each app its own database and its own user rather than the administrator password. In the dashboard: Create Database, Create User, then Grant Access. From the CLI:

$ sh0 db create-database e8afafa4-2ff1-4088-8276-fccb839734a8 --name appdb
Database 'appdb' created (id: 503a447c-f9f5-44b8-87cf-22c0905f075c).

$ sh0 db create-user e8afafa4-2ff1-4088-8276-fccb839734a8 --username appuser --password '<password>'
User 'appuser' created (id: 86de6179-7e97-463a-93d8-dfd89b1da6e2).

$ sh0 db grant e8afafa4-2ff1-4088-8276-fccb839734a8 \
    --user 86de6179-7e97-463a-93d8-dfd89b1da6e2 \
    --database 503a447c-f9f5-44b8-87cf-22c0905f075c --privileges all
Access granted.

• Password rules -- at least one uppercase letter and one symbol, among others; a password that misses one is refused with the rule it breaks. The dashboard's Generate button produces a compliant one; save it, it is not shown again.
• Privileges -- all or readonly. Other values are refused.

Setting DATABASE_URL on the app

Build the URL from the internal host, the engine's port, the user and the database, and store it as an environment variable of the app. Variables are applied when the app is deployed, so redeploy:

$ sh0 env my-api set "DATABASE_URL=postgresql://appuser:<password>@my-pg.internal:5432/appdb"
[ok] Set DATABASE_URL for 'my-api'

$ sh0 deploy my-api
[*] Deploying my-api...
  Deployment ID: 64492c39-dad0-409f-ad6d-29b71f79bab3
  Status:        queued

The URL shapes by engine (replace the names with yours; the internal port is the engine's default port, not the port shown under Port):

• PostgreSQL -- postgresql://appuser:<password>@my-pg.internal:5432/appdb
• MySQL and MariaDB -- mysql://appuser:<password>@my-mysql.internal:3306/appdb
• Redis -- redis://:<password>@my-redis.internal:6379

Internal network

Apps and database servers share the sh0 Docker network. An app reaches a server by <server-name>.internal on the engine's port -- measured from an app container: a TCP connection to my-pg.internal:5432 opens, and appuser logs into appdb with the URL above.

Connecting from your machine

To reach a database from your workstation (pgAdmin, TablePlus, DBeaver, psql), there are two ways. Prefer the first.

SSH tunnel (recommended)

The port shown under Port (here 32962) is bound to 127.0.0.1 on the server. Forward it over SSH: nothing is exposed to the internet, and access is that of your SSH key.

ssh -N -L 5432:127.0.0.1:32962 root@your-server
# in another terminal
psql "postgresql://appuser:<password>@127.0.0.1:5432/appdb"

External access

Enable External Access on the server opens a public port through sh0's proxy. What the code of the published version enforces:

• Allowed IPs are mandatory -- individual addresses or CIDR ranges; 0.0.0.0/0 and ::/0 are refused.
• TLS required by default.
• Expiry -- 1 h, 6 h, 24 h, 7 days, 30 days, or permanent.
• Port -- the first free one in the 10000-10999 range; your firewall must let it through.

Connection Pooling

Each database has a connection limit (100 by default for PostgreSQL). Size your pools so that all replicas together stay below it.

• Use your framework's built-in pool -- most ORMs and drivers have one.
• Divide the limit between replicas -- with 3 replicas of an app and a limit of 100, about 30 connections per replica, minus what other apps use.
• Set idle timeouts so stale connections give their slot back.

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,              // Maximum connections in the pool
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail if connection takes > 5s
});

Common Framework Examples

Every example below reads the DATABASE_URL you set on the app.

production:
  url: <%= ENV['DATABASE_URL'] %>

import dj_database_url

DATABASES = {"default": dj_database_url.config(conn_max_age=600)}

let pool = PgPoolOptions::new()
    .max_connections(20)
    .connect(&std::env::var("DATABASE_URL")?)
    .await?;

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))