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

> Learn how to set up a PostgreSQL backend with Drizzle ORM in Thunderbolt. Explore driver-agnostic clients, schema definitions, and automatic migrations for efficient database management.

- Repository: [Thunderbird/thunderbolt](https://github.com/thunderbird/thunderbolt)
- Tags: how-to-guide
- Published: 2026-04-19

---

**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`](https://github.com/thunderbird/thunderbolt/blob/main/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:

```typescript
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`](https://github.com/thunderbird/thunderbolt/blob/main/auth-schema.ts)** – Defines `user`, `session`, `account`, and `verification` tables with Drizzle relations.
- **[`powersync-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/powersync-schema.ts)** – Contains PowerSync‑synced tables (settings, chat, models, devices) with composite primary keys and cascade rules.
- **[`waitlist-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/waitlist-schema.ts)**, **[`rate-limit-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/rate-limit-schema.ts)**, **[`encryption-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/encryption-schema.ts)**, **[`otp-challenge-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/backend/src/db/schema.ts) file re‑exports all schemas, creating a single source of truth for the Drizzle client:

```typescript
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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
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`](https://github.com/thunderbird/thunderbolt/blob/main/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.

```typescript
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:

```typescript
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:

```typescript
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`](https://github.com/thunderbird/thunderbolt/blob/main/backend/src/db/api-keys-schema.ts):

```typescript
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**:

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

```

3. **Generate and run migrations**:

```bash
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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/backend/src/db/schema.ts) re‑exports them as a unified schema object.
- **Type Safety**: The `db` object exported from [`client.ts`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/auth-schema.ts), [`powersync-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/powersync-schema.ts), and [`rate-limit-schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/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`](https://github.com/thunderbird/thunderbolt/blob/main/backend/src/db/client.ts) and table definitions from [`backend/src/db/schema.ts`](https://github.com/thunderbird/thunderbolt/blob/main/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.