PostgreSQL quickstart

This path takes a small PostgreSQL application from an existing database connection to one checked and executed query. The grammar and compiler are stable; the optional editor integration is not part of this path.

1. Check the prerequisites

Use Node.js 22.11 or newer, TypeScript 7.0.2, and a PostgreSQL 14–18 database. The repository tests specific patches within that range; review PostgreSQL compatibility before selecting a production version.

Set a connection string for an empty development database:

export DATABASE_URL=postgresql://user:password@127.0.0.1:5432/app

The database and its lifecycle remain yours.

Start an empty ESM project:

mkdir typed-sql-postgres && cd typed-sql-postgres
pnpm init
pnpm pkg set type=module
mkdir src

2. Install the packages

pnpm add @typed-sql/core @typed-sql/postgres pg
pnpm add -D @typed-sql/cli typescript tsx

pg is an explicit application dependency. Installing @typed-sql/postgres does not install it.

Create tsconfig.json:

{
  "compilerOptions": {
    "module": "NodeNext",
    "moduleResolution": "NodeNext",
    "target": "ES2024",
    "strict": true,
    "noEmit": true
  },
  "include": ["src/**/*.ts", "typed-sql.config.ts"]
}

3. Create a minimal table

Create src/setup.ts:

import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

try {
  await pool.query(`
    CREATE TYPE account_status AS ENUM ('active', 'suspended');
    CREATE TABLE users (
      id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      email text NOT NULL UNIQUE,
      status account_status NOT NULL DEFAULT 'active'
    );
    INSERT INTO users (email) VALUES ('ada@example.com');
  `);
} finally {
  await pool.end();
}

Run it once against the empty database:

pnpm exec tsx src/setup.ts

4. Create a minimal config

Create typed-sql.config.ts:

import { defineConfig } from "@typed-sql/core";
import { postgres, typePolicy } from "@typed-sql/postgres";
import { pg } from "@typed-sql/postgres/pg";

const connectionString = () => process.env.DATABASE_URL!;

export default defineConfig({
  dialect: postgres({ typePolicy }),
  schema: {
    file: "generated/db/schema.json",
    provider: pg({ connectionString, schemas: ["public"], typePolicy }),
  },
  outDir: "generated/db",
  projects: ["tsconfig.json"],
  typePolicy,
});

The same typePolicy controls introspection, compile-time inference, and adapter decoding.

5. Generate the snapshot

pnpm exec typed-sql generate

This introspects PostgreSQL and writes deterministic compiler input under generated/db. Commit the snapshot so schema changes are reviewable; application code does not import it.

6. Write one parameterized query

Create src/account.ts:

import { sql } from "@typed-sql/postgres";

export const accountById = (accountId: bigint) => sql`
  SELECT account.id, account.email, account.status
  FROM users AS account
  WHERE account.id = ${accountId}
`;

accountId remains a value segment and renders as $1.

7. Check the inferred contract

pnpm exec typed-sql check --project tsconfig.json

Against the table above, the compiler proves:

type AccountByIdQuery = Query<
  { "id": bigint; "email": string; "status": "active" | "suspended"; },
  readonly [bigint]
>;

The stable check is authoritative. An ordinary TypeScript server can still show the conservative published Query<unknown> declaration; compiler and editor workflow explains why.

8. Execute the query

Create src/run.ts:

import { typePolicy } from "@typed-sql/postgres";
import { createPgDatabase } from "@typed-sql/postgres/pg";
import { accountById } from "./account.js";

const database = await createPgDatabase({
  connectionString: process.env.DATABASE_URL!,
  typePolicy,
});

try {
  console.log(await database.maybeOne(accountById(1n)));
} finally {
  await database.close();
}

pnpm exec tsx src/run.ts

The adapter is optional. You can instead render into an existing pg pool.

9. Confirm a type error

Temporarily interpolate a string directly in the bigint comparison:

const wrongId = "1";

sql`SELECT id FROM users WHERE id = ${wrongId}`;

Run typed-sql check again. The transformed check reports that string is not assignable to the bigint parameter position. Restore the valid query before continuing.

10. Choose the next step

• Adopt an existing pool without transferring ownership.
• Add the experimental editor tooling only after the CLI path works.
• Explore the complete PostgreSQL application.
• Add production controls independently when the application needs them.

What just happened?

• The PostgreSQL grammar—not the neutral compiler—owned SQL syntax, enum inference, placeholders, and diagnostics.
• The generated snapshot supplied schema evidence without becoming an application API.
• typed-sql check proved the row and ordered parameter tuple and remains the authoritative result.
• The interpolated id stayed a driver parameter instead of becoming SQL text.
• The shared type policy kept inferred types and runtime decoding aligned; no runtime validation was implied.
• The application installed and closed pg; typed-sql did not own the driver or connection lifecycle.