PostgreSQL

The PostgreSQL integration lets you declare a server, databases, and SQL scripts. Schema and seed data are baked into the snapshot, so databases are ready the moment a VM boots.

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. Each script runs with ON_ERROR_STOP=1, so the snapshot fails fast if any SQL errors.

Server Options

new VmPostgres({
  version: "18",        // PostgreSQL major version
  user: "postgres",     // superuser name
  password: "postgres", // superuser password
});

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

VmPostgres sets the superuser password and enables md5 password auth on TCP and the local socket.

Databases

`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)`

Runs a SQL query against the database. Results are returned as a JSON array (psql wraps the query in json_agg(row_to_json(...))).

const result = await vm.db.query<{ id: number; name: string }>(
  `SELECT id, name FROM users ORDER BY id`,
);

console.log(result.rows);      // [{ id: 1, name: 'Alice' }, ...]
console.log(result.rowCount);  // 2

Returns: Promise<{ rows: T[]; rowCount: number; error?: string }>

`vm.<name>.exec(sql)`

Runs a SQL command without returning rows.

await vm.db.exec(`UPDATE users SET name = 'Carol' WHERE id = 1`);

Returns: Promise<{ success: boolean; error?: string }>

Scripts

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

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

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

Scripts write their SQL to /opt/pg-scripts/<db>/<name>.sql and run as psql -f oneshots. They automatically depend on the database's create service (or on install-postgres when create: false) and on every script listed in after.

All setup oneshots use deleteAfterSuccess: true, so they don't re-run when the VM reboots from the snapshot.

`vm.<name>.logs()`

Returns the journalctl output for the script's systemd service as string[]. Useful for inspecting seed output or diagnosing failures.

console.log((await vm.seed.logs())?.join("\n"));

Example: Schema + Seed

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

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

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

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

const seed = db.script("seed", {
  sql: `
    INSERT INTO users (name, email) VALUES
      ('Alice', 'alice@example.com'),
      ('Bob', 'bob@example.com');
  `,
  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;
  email: string;
}>(`SELECT * FROM users ORDER BY id`);

console.log("Users:", result.rows);
console.log("\nSeed service logs:");
console.log((await vm.seed.logs())?.join("\n"));