@freestyle-sh/with-postgres

@freestyle-sh/with-postgres

PostgreSQL runtime extension for Freestyle VMs. Declaratively configure a PostgreSQL server, databases, and schema/seed scripts that run during VM snapshot setup.

Installation

npm install @freestyle-sh/with-postgres freestyle

Usage

import { freestyle, VmSpec } from "freestyle";
import { VmPostgres } from "@freestyle-sh/with-postgres";

const pg = new VmPostgres({ password: "secret" });

const db = pg.database({ name: "myapp", create: true });

const schema = db.script("schema", {
  sql: `
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100)
    );
  `,
});

const seed = db.script("seed", {
  sql: `INSERT INTO users (name) VALUES ('Alice'), ('Bob');`,
  after: [schema],
});

const spec = new VmSpec()
  .with("postgres", pg)
  .with("db", db)
  .with("schema", schema)
  .with("seed", seed)
  .snapshot();

const { vm } = await freestyle.vms.create({ spec });

const result = await vm.db.query<{ id: number; name: string }>(
  `SELECT * FROM users`
);
console.log(result.rows); // [{ id: 1, name: 'Alice' }, { id: 2, name: 'Bob' }]

The install, database creation, and each script run as ordered systemd oneshot services during snapshot setup, so everything is baked into the snapshot.

API

new VmPostgres(options?)

Configures the PostgreSQL server. Pure config — has no runtime methods.

Option	Type	Default	Description
version	string	"18"	PostgreSQL major version (installed from the official PGDG apt repo)
password	string	"postgres"	Password for the postgres superuser
user	string	"postgres"	PostgreSQL superuser name

pg.database({ name, create? })

Declares a database. Returns a Database you can attach scripts to and query at runtime.

Option	Type	Default	Description
name	string	—	Database name
create	boolean	false	If true, idempotently creates the database during snapshot setup

vm.<name>.query<T>(sql)

Run a SQL query against this database. Returns { rows: T[], rowCount, error? }. Results are returned as a JSON array (psql wraps the query in json_agg(row_to_json(...))).

vm.<name>.exec(sql)

Run a SQL command without returning rows. Returns { success, error? }.

db.script(name, { sql, after? })

Declares a SQL script that runs once during snapshot setup against this database.

Option	Type	Default	Description
sql	string	—	Inline SQL to execute
after	DatabaseScript[]	[]	Scripts that must run before this one

Each script becomes a systemd oneshot with ON_ERROR_STOP=1, so the snapshot fails fast if any SQL errors. Scripts depend automatically on the database's create service (or on install-postgres if create: false), and on every script listed in after.

vm.<name>.logs()

Returns the journalctl output for the script's systemd service as string[].

How it works

• VmPostgres adds the official PGDG apt repository and installs the requested PostgreSQL version (see https://www.postgresql.org/download/linux/debian/), then sets the superuser password and enables md5 password auth on TCP and the local socket.
• Each database({ create: true }) adds a oneshot that creates the database if it doesn't exist.
• Each script(...) writes its SQL to /opt/pg-scripts/<db>/<name>.sql and adds a oneshot that runs psql -f against the right database, in the order you declared via after.
• All setup oneshots use deleteAfterSuccess: true, so they don't re-run on reboot from the snapshot.