Managed PostgreSQL

Provision a managed PostgreSQL database for your application with zero configuration.

Overview

sota.io can provision a dedicated PostgreSQL 17 instance for each project. The database includes automatic connection pooling via PgBouncer, daily backups with 7-day retention, and auto-injected credentials.

Provisioning

Auto-Provisioning

Databases are auto-provisioned on your first deploy. The deploy engine calls ensureDatabase automatically, so you do not need to provision manually. The DATABASE_URL will be injected into your app container before it starts.

Via API

If you want to provision a database before your first deployment, you can use the API explicitly:

curl -X POST https://api.sota.io/v1/projects/550e8400-.../database \
  -H "Authorization: Bearer <token>"

That is it. No configuration needed.

What Happens

1. A PostgreSQL 17 container is created (sota-db-{slug})
2. A PgBouncer sidecar is started for connection pooling (sota-pgbouncer-{slug})
3. A cryptographically random password is generated
4. DATABASE_URL is automatically injected into your app container

Connecting

The DATABASE_URL environment variable is available in your application without any manual configuration:

// Node.js with pg
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

# Python with psycopg2
import os
import psycopg2

conn = psycopg2.connect(os.environ["DATABASE_URL"])

DATABASE_URL Format

The DATABASE_URL injected into your app follows this format:

postgresql://app_{slug}:{password}@sota-pgbouncer-{slug}:5432/{slug}?sslmode=disable

The connection URL points to PgBouncer (port 5432), not directly to PostgreSQL. This provides automatic connection pooling.

Connection Pooling

Every database includes PgBouncer for connection pooling:

This means your application can open up to 100 connections, and PgBouncer will multiplex them over 20 actual PostgreSQL connections.

Database Migrations

Why Migrations Run on Startup

sota.io containers have no shell access — you cannot SSH in and run npx prisma migrate deploy manually. Your app's start command must handle migrations before starting the server.

Every deploy creates a fresh container, so migrations run on each deploy. This is safe because migrations are idempotent by design — already-applied migrations are skipped automatically.

Node.js ORMs

Prisma:

{
  "scripts": {
    "start": "npx prisma db push && node server.js"
  }
}

PgBouncer pitfall: Prisma's migrate deploy uses advisory locks, which require a direct PostgreSQL connection — not PgBouncer in transaction mode. Since sota.io routes connections through PgBouncer, use prisma db push instead of prisma migrate deploy. db push does not use advisory locks and works through PgBouncer. Also add ?pgbouncer=true to your DATABASE_URL in prisma/schema.prisma to avoid prepared statement conflicts.

Drizzle:

{
  "scripts": {
    "start": "npx drizzle-kit push && node server.js"
  }
}

drizzle-kit push applies schema changes without migration files — works well with PgBouncer.

TypeORM:

{
  "scripts": {
    "start": "npx typeorm migration:run -d dist/data-source.js && node dist/server.js"
  }
}

Or use synchronize: true in development (NOT production).

Sequelize:

{
  "scripts": {
    "start": "npx sequelize-cli db:migrate && node server.js"
  }
}

Knex:

{
  "scripts": {
    "start": "npx knex migrate:latest && node server.js"
  }
}

Python Frameworks

Django:

Create a start.sh entrypoint:

#!/bin/bash
python manage.py migrate --noinput
gunicorn myproject.wsgi:application --bind 0.0.0.0:$PORT

Then in your Procfile or Dockerfile CMD: bash start.sh

SQLAlchemy + Alembic:

#!/bin/bash
alembic upgrade head
uvicorn app.main:app --host 0.0.0.0 --port $PORT

Flask-Migrate:

#!/bin/bash
flask db upgrade
gunicorn "app:create_app()" --bind 0.0.0.0:$PORT

Custom Dockerfile

Chain the migration command before your server start in the CMD:

CMD ["sh", "-c", "npx prisma db push && node server.js"]

Common Pitfalls

Backups

Databases are automatically backed up daily:

List Backups

curl https://api.sota.io/v1/projects/550e8400-.../database/backups \
  -H "Authorization: Bearer <token>"

Trigger Manual Backup

curl -X POST https://api.sota.io/v1/projects/550e8400-.../database/backups \
  -H "Authorization: Bearer <token>"

Creates an on-demand backup in addition to the automatic daily backups.

Security

• Database containers run on runc (not gVisor) as trusted system services
• Passwords are generated using crypto/rand (32 hex characters)
• The DATABASE_URL is encrypted at rest using the same AES-256-GCM encryption as other environment variables
• Network access is restricted to containers on the same Docker network

Why sslmode=disable?

The DATABASE_URL uses sslmode=disable. This is safe because of the network architecture:

• All containers (your app, PgBouncer, PostgreSQL) run on the same Docker bridge network (traefik-public) on the same physical host
• Traffic between containers never leaves the machine or traverses any external network
• There is no network path where an attacker could intercept the connection
• PgBouncer uses AUTH_TYPE=plain for the same reason -- all communication is internal-only

Despite SSL being disabled for the internal connection, credentials are still protected:

• Passwords are cryptographically strong (32 hex characters generated via crypto/rand)
• The DATABASE_URL environment variable is encrypted at rest using AES-256-GCM
• Database passwords are never exposed via the API