Local databases (`prorm db up`)

$ prorm db up --dialect postgres --ext vector,postgis
$ prorm db url
postgres://prorm:****@localhost:5432/postgres
$ prorm db status
$ prorm db down                    # keeps the data; --volumes deletes it

docker/
  local-db/
    postgres/
      docker-compose.yml               # no secrets in it
      .env                             # 0600, git-ignored
      Dockerfile                       # only when an extension has to be added
      initdb/
        10-prorm-extensions.sql        # PostgreSQL, when extensions were asked for
    mysql/                             # a second database gets its own directory
      docker-compose.yml
      .env
.gitignore                             # gains docker/local-db/*/.env

$ prorm db up --dialect postgres --ext vector --no-start
✓ Wrote ./docker/local-db/postgres/docker-compose.yml (pgvector/pgvector:0.7.4-pg16).
Credentials: ./docker/local-db/postgres/.env (0600; added docker/local-db/*/.env to .gitignore).
Start it with: prorm db up --name postgres   (or: docker compose -f ./docker/local-db/postgres/docker-compose.yml up -d)
$ prorm db status
name      dialect   image                         port  state        url
postgres  postgres  pgvector/pgvector:0.7.4-pg16  5432  not running  postgres://prorm:****@localhost:5432/postgres

$ prorm db up --name postgres
✓ postgres is up on 127.0.0.1:5432 (docker).
Extensions installed: vector
URL: postgres://prorm:****@localhost:5432/postgres
The full URL: prorm db url --name postgres --show-secret   (or DATABASE_URL in ./docker/local-db/postgres/.env)

$ export DATABASE_URL=$(prorm db url --show-secret)
$ prorm migration:run
$ prorm db:seed

import { Prorm } from 'ts-prorm-orm';

export const prorm = new Prorm({ dialect: 'postgres', url: process.env.DATABASE_URL });
await prorm.connect();

$ prorm db down                     # container gone, volume kept
$ prorm db down --volumes --yes     # container and data gone

# Generated by `prorm db up` for the local postgres database "postgres".
# Local credentials: git-ignored, never commit. `prorm db url` prints the URL (redacted).
PRORM_DB_DIALECT=postgres
PRORM_DB_IMAGE=pgvector/pgvector:0.7.4-pg16
PRORM_DB_SERVICE=postgres
PRORM_DB_CONTAINER=prorm-postgres
PRORM_DB_PORT=5432
PRORM_DB_USER=prorm
PRORM_DB_PASSWORD=****
PRORM_DB_ROOT_PASSWORD=****
PRORM_DB_NAME=postgres
DATABASE_URL=postgres://prorm:****@localhost:5432/postgres
# Read by the image:
POSTGRES_USER=prorm
POSTGRES_PASSWORD=****
POSTGRES_DB=postgres

# ── would run ──
docker compose -f ./docker/local-db/postgres/docker-compose.yml up -d --wait
docker compose -f ./docker/local-db/postgres/docker-compose.yml exec -T postgres psql -v ON_ERROR_STOP=1 -U prorm -d postgres -c 'CREATE EXTENSION IF NOT EXISTS "vector"'
docker compose -f ./docker/local-db/postgres/docker-compose.yml exec -T postgres psql -v ON_ERROR_STOP=1 -U prorm -d postgres -c 'CREATE EXTENSION IF NOT EXISTS "pg_trgm"'

$ prorm db up --dialect postgres
$ prorm db url
postgres://prorm:****@localhost:5432/postgres

$ prorm db up --dialect mysql
$ prorm db url
mysql://prorm:****@localhost:3306/mysql

$ prorm db up --dialect mssql
$ prorm db url
mssql://sa:****@localhost:1433/mssql

$ prorm db up --dialect postgres
$ prorm db up shop --dialect mysql --port 3307
$ prorm db status
$ export DATABASE_URL=$(prorm db url shop --show-secret)

$ prorm db init app                                   # SQLite ./app.db, and ./.klock
$ prorm db init shop --docker --dialect postgres      # a docker-compose.yml, and ./.klock with the URL
$ prorm db init --url postgres://user:pw@localhost/db --klock ./gate.klock
$ prorm db serve ./.klock --port 7070                 # a passwordless gate on 127.0.0.1:7070 (alias db gate)

import { LocalKlock } from 'ts-prorm-orm';

const klock = LocalKlock.fromFile('./.klock');

const res = await fetch('http://127.0.0.1:7070/query', {
  method: 'POST',
  headers: {
    authorization: `Klock ${klock.token()}`,
    'content-type': 'application/json',
  },
  body: JSON.stringify({ sql: 'SELECT count(*) AS n FROM orders' }),
});
const { rows } = (await res.json()) as { rows: Array<{ n: number }> };