@kodai-ch/postgres

A type-safe PostgreSQL library for TypeScript with support for SvelteKit, TanStack Start, and comprehensive testing utilities.

Features

• Type-safe SQL: Tagged template literals with automatic parameter binding
• Framework Adapters: First-class support for SvelteKit and TanStack Start
• SQL Migrations: Pure SQL migration files with CLI tooling
• Testing Utilities: PgLite integration for fast, isolated tests
• Schema Enforcement: CI-ready schema validation

Packages

• @kodai-ch/postgres-core - Query builder and database connection
• @kodai-ch/postgres-migrations - Migration CLI and runner
• @kodai-ch/postgres-testing - PgLite testing utilities with Vitest integration
• @kodai-ch/postgres-schema - Schema extraction and validation
• @kodai-ch/postgres-sveltekit - SvelteKit adapter
• @kodai-ch/postgres-tanstack-start - TanStack Start adapter

Setup

This project uses Nix flakes with direnv for development environment management.

Using direnv

direnv allow

Using nix directly

nix develop

Installation

# Install dependencies
pnpm install

# Build all packages
pnpm build

# Run tests
pnpm test

# Type check
pnpm typecheck

Quick Start

Basic Usage

import { createDatabase, sql } from '@kodai-ch/postgres-core';

const db = createDatabase(process.env.DATABASE_URL);

// Type-safe queries
const users = await db.execute<{ id: number; name: string }>(
  sql`SELECT * FROM users WHERE active = ${true}`
);

// Transactions
await db.transaction(async (tx) => {
  const user = await tx.queryOne(
    sql`INSERT INTO users (name) VALUES (${'John'}) RETURNING *`
  );
  await tx.execute(
    sql`INSERT INTO logs (user_id) VALUES (${user.id})`
  );
});

Migrations

# Generate a new migration
npx postgres-migrate generate --name add_users_table

# Run pending migrations
npx postgres-migrate run

# Check status
npx postgres-migrate status

# Rollback
npx postgres-migrate rollback --steps 1

Migration files are pure SQL:

-- migrations/20240331120000_add_users_table.sql
-- Up
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL
);

-- Down
DROP TABLE users;

Testing with Vitest

import { test } from '@kodai-ch/postgres-testing/vitest';
import { sql } from '@kodai-ch/postgres-core';

test('creates a user', async ({ db }) => {
  // db is a fresh PgLite instance with migrations applied
  const result = await db.execute(
    sql`INSERT INTO users (name) VALUES ('John') RETURNING *`
  );
  expect(result[0].name).toBe('John');
});

Configure in vitest.config.ts:

export default defineConfig({
  test: {
    environment: 'node',
  },
});

And set MIGRATIONS_PATH environment variable.

Schema Enforcement

Generate schema.sql:

npx postgres-schema

Check in CI that schema.sql is up to date:

# .github/workflows/ci.yml
- name: Check Schema
  run: npx postgres-schema --check

SvelteKit Integration

// hooks.server.ts
import { createDatabaseHandle } from '@kodai-ch/postgres-sveltekit';
import { createDatabase } from '@kodai-ch/postgres-core';

const db = createDatabase(process.env.DATABASE_URL);
export const handle = createDatabaseHandle(db);

// +page.server.ts
import type { PageServerLoad } from './$types';

export const load: PageServerLoad = async ({ locals }) => {
  const users = await locals.db.execute(sql`SELECT * FROM users`);
  return { users };
};

Environment Variables

• DATABASE_URL - PostgreSQL connection string (required for CLI tools)
• MIGRATIONS_PATH - Path to migrations directory (default: ./migrations)

Edge Runtime Support

The core package uses pg (node-postgres) by default. For edge runtimes:

import { sql } from '@kodai-ch/postgres-core';

// Provide your own driver implementing the Database interface
const db = createDatabase({
  driver: edgeCompatibleDriver,
  connectionString: process.env.DATABASE_URL,
});

Development

# Run in development mode
pnpm dev

# Build the project
pnpm build

# Start the built application
pnpm start

Build with Nix

nix build

License

MIT