How to Set Up PostgreSQL with Drizzle ORM in Thunderbolt: Complete Backend Database Guide

Thunderbolt configures its PostgreSQL backend using Drizzle ORM through a driver‑agnostic client that switches between real PostgreSQL and in‑memory PGlite, with centralized schema definitions and automatic migration handling.

The Thunderbolt project (thunderbird/thunderbolt) implements a robust, type‑safe database layer using Drizzle ORM. This setup separates driver initialization, schema declaration, and migration logic into distinct modules, enabling seamless switching between a production PostgreSQL instance and an in‑memory PGlite database for local development and testing.

Database Driver Selection

The dual‑driver architecture lives in backend/src/db/client.ts. This module detects the runtime environment and initializes either a real PostgreSQL connection or an in‑memory PGlite instance.

Environment Configuration

Two environment variables control the driver selection:

• DATABASE_DRIVER – Set to 'postgres' to use a real PostgreSQL server; any other value (or omission) defaults to PGlite.
• DATABASE_URL – Required when DATABASE_DRIVER=postgres; for PGlite, it typically points to an in‑memory file path.

Driver Initialization Logic

The client file imports both drizzle-orm/pglite and drizzle-orm/postgres-js, then conditionally instantiates the correct driver:

import { drizzle as drizzlePglite } from 'drizzle-orm/pglite';
import { drizzle as drizzlePostgres } from 'drizzle-orm/postgres-js';
import { PGlite } from '@electric-sql/pglite';
import postgres from 'postgres';

const isPglite = process.env.DATABASE_DRIVER !== 'postgres';

const pgliteDb = isPglite
  ? drizzlePglite({ client: new PGlite(process.env.DATABASE_URL), schema })
  : null;

const postgresDb = isPglite
  ? null
  : drizzlePostgres({ client: postgres(process.env.DATABASE_URL!), schema });

export const db = pgliteDb ?? postgresDb!;

Both instances receive the same schema object, ensuring identical type safety regardless of the underlying driver.

Schema Definition and Aggregation

Thunderbolt organizes database tables into modular schema files, then aggregates them through a central export module.

Modular Schema Files

Individual domains define their own tables in dedicated files under backend/src/db/:

• auth-schema.ts – Defines user, session, account, and verification tables with Drizzle relations.
• powersync-schema.ts – Contains PowerSync‑synced tables (settings, chat, models, devices) with composite primary keys and cascade rules.
• waitlist-schema.ts, rate-limit-schema.ts, encryption-schema.ts, otp-challenge-schema.ts – Additional domain‑specific tables.

Each file uses pgTable from drizzle-orm/pg-core to declare columns, indexes, and foreign‑key relationships.

Central Schema Export

The backend/src/db/schema.ts file re‑exports all schemas, creating a single source of truth for the Drizzle client:

export * from './auth-schema';
export * from './waitlist-schema';
export * from './powersync-schema';
export * from './rate-limit-schema';
export * from './encryption-schema';
export * from './otp-challenge-schema';

This aggregation is imported by client.ts and passed to the Drizzle constructor, ensuring the runtime db object has typed access to every table.

Migration Workflow

Thunderbolt automates database migrations through Drizzle‑Kit and runtime migration functions.

Migration Configuration

The backend/src/db/client.ts file exposes two key functions:

• getMigrationsFolder() – Resolves the migrations directory from the MIGRATIONS_DIR environment variable or defaults to ./drizzle relative to the working directory.
• runMigrations() – Conditionally executes migrations based on the SKIP_MIGRATIONS environment variable.

export const getMigrationsFolder = () =>
  process.env.MIGRATIONS_DIR ?? resolve(process.cwd(), 'drizzle');

export const runMigrations = async () => {
  if (process.env.SKIP_MIGRATIONS === 'true') return;
  const migrationsFolder = getMigrationsFolder();
  if (pgliteDb) {
    await migratePglite(pgliteDb, { migrationsFolder });
  } else if (postgresDb) {
    await migratePostgres(postgresDb, { migrationsFolder });
  }
};

Drizzle-Kit Configuration

The drizzle.config.ts file configures the Drizzle‑Kit CLI for generating migration files. While the backend uses PostgreSQL, the configuration may reference alternative dialects for specific generation contexts, though the generated SQL remains compatible with the Postgres driver when executed through the backend migration runner.

export default defineConfig({
  out: './src/drizzle',
  schema: './src/db/schema.ts',
  dialect: 'sqlite',           // Generation dialect; runtime uses pg-core
  casing: 'snake_case',
  dbCredentials: { url: process.env.DB_FILE_NAME! },
});

Runtime Usage Examples

Once initialized, the db object provides a type‑safe query interface across the entire backend.

Querying Data

Import the db instance and specific table schemas to execute fully typed queries:

import { db } from '@/backend/src/db/client';
import { eq } from 'drizzle-orm';
import { user } from '@/backend/src/db/auth-schema';

const userRow = await db.select().from(user).where(eq(user.id, '123')).get();

All columns are autocompleted, and query results inherit the TypeScript types defined in the schema files.

Running Migrations on Startup

Integrate runMigrations() into the server initialization sequence to ensure the schema is current before handling requests:

import { runMigrations } from '@/backend/src/db/client';
import express from 'express';

const app = express();

await runMigrations();  // Applies pending migrations
app.listen(3000, () => console.log('Server ready'));

Adding a New Table

To extend the database schema, create a new schema file and re‑export it:

1. Define the table in backend/src/db/api-keys-schema.ts:

import { pgTable, text, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { user } from './auth-schema';

export const apiKey = pgTable('api_key', {
  id: text('id').primaryKey(),
  userId: text('user_id')
    .notNull()
    .references(() => user.id, { onDelete: 'cascade' }),
  key: text('key').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const apiKeyRelations = relations(apiKey, ({ one }) => ({
  user: one(user, { fields: [apiKey.userId], references: [user.id] }),
}));

2. Export from schema.ts:

export * from './api-keys-schema';

3. Generate and run migrations:

bunx drizzle-kit generate

# Migrations run automatically on server start, or manually via:

bunx drizzle-kit migrate

Summary

• Driver Selection: backend/src/db/client.ts switches between PostgreSQL and PGlite based on DATABASE_DRIVER and DATABASE_URL environment variables.
• Schema Aggregation: Modular schema files define tables using pgTable, then backend/src/db/schema.ts re‑exports them as a unified schema object.
• Type Safety: The db object exported from client.ts provides fully typed query methods across all tables.
• Migration Workflow: runMigrations() applies pending migrations on startup, reading from drizzle/ or a custom MIGRATIONS_DIR, with SKIP_MIGRATIONS available for external deployment tools.
• Extensibility: New tables are added by creating schema files, re‑exporting them, and regenerating migrations via Drizzle‑Kit.

Frequently Asked Questions

How does Thunderbolt switch between PostgreSQL and PGlite?

Thunderbolt checks the DATABASE_DRIVER environment variable in backend/src/db/client.ts. When set to 'postgres', it initializes a postgres-js client; otherwise, it defaults to an in‑memory PGlite instance. Both drivers receive the same schema object, ensuring identical query behavior across environments.

Where are the database tables defined in Thunderbolt?

Tables are defined in modular schema files under backend/src/db/, such as auth-schema.ts, powersync-schema.ts, and rate-limit-schema.ts. Each file uses pgTable from drizzle-orm/pg-core to declare columns, types, and relations. These modules are aggregated in backend/src/db/schema.ts, which re‑exports all tables as a single schema object.

How do migrations work in Thunderbolt?

Migrations are handled by the runMigrations() function in backend/src/db/client.ts. On server startup, this function checks SKIP_MIGRATIONS; if not disabled, it applies pending migrations from the drizzle/ folder (or MIGRATIONS_DIR) using the driver‑specific migrator (migratePglite or migratePostgres). Migration files are generated via Drizzle‑Kit based on the schema definitions.

Can I use the Thunderbolt database client in my own project?

Yes, the architecture is modular. You can import the db instance from backend/src/db/client.ts and table definitions from backend/src/db/schema.ts or individual schema files. The driver‑agnostic design allows you to run against PostgreSQL in production and PGlite for unit tests without changing query code, provided you maintain the same schema structure and required environment variables.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}