Database

Overview

Codapult uses Turso (LibSQL) as the default database and supports PostgreSQL via DB_PROVIDER=postgres. Drizzle ORM provides type-safe queries for both providers. No raw SQL is needed for normal application code.

Choosing Turso or PostgreSQL

Codapult is intentionally Turso-first, but not Turso-only. Use this table when deciding what to keep for your product:

If you are unsure, start with file:local.db locally and choose Turso or PostgreSQL before production. The application imports db from @/lib/db, so most feature code does not change when switching providers.

Schema Conventions

The SQLite schema file src/lib/db/schema.ts is the main schema used by the default Turso setup. If you support PostgreSQL in your product, keep the matching Postgres schema in src/lib/db/schema-pg.ts in sync as well. Follow these conventions when adding or modifying tables:

Example Table

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export type UserRole = 'user' | 'admin';

export const user = sqliteTable('user', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  role: text('role').$type<UserRole>().notNull().default('user'),
  createdAt: integer('created_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
  updatedAt: integer('updated_at', { mode: 'timestamp' })
    .notNull()
    .$defaultFn(() => new Date()),
});

Query Patterns

Import db from @/lib/db and table schemas from @/lib/db/schema. Use Drizzle's query builder with condition helpers from drizzle-orm.

Select

import { db } from '@/lib/db';
import { user } from '@/lib/db/schema';
import { eq } from 'drizzle-orm';

const users = await db.select().from(user).limit(50);

const singleUser = await db.select().from(user).where(eq(user.id, userId)).limit(1);

Insert

import { nanoid } from 'nanoid';

await db.insert(user).values({
  id: nanoid(),
  name: 'Alice',
  email: 'alice@example.com',
});

Update

await db.update(user).set({ name: 'Alice Smith' }).where(eq(user.id, userId));

Delete

await db.delete(user).where(eq(user.id, userId));

Conditions

import { eq, and, or, like } from 'drizzle-orm';

const admins = await db
  .select()
  .from(user)
  .where(and(eq(user.role, 'admin'), like(user.email, '%@example.com')))
  .limit(100);

Always use .limit() for list queries to prevent unbounded result sets.

Setup & Commands

Initial Setup

# Set your database URL in .env.local
TURSO_DATABASE_URL="file:local.db"  # local development
# TURSO_DATABASE_URL="libsql://your-db.turso.io"  # production

# Create all tables from schema (no migrations needed for fresh databases)
pnpm db:push

# Seed sample data for development
pnpm db:seed

Migration Workflow

For new projects, pnpm db:push applies the full schema directly. Once you have production data, use the migration workflow to make incremental changes safely:

# 1. Edit src/lib/db/schema.ts

# 2. Generate a migration
pnpm db:generate

# 3. Apply the migration
pnpm db:migrate

All db:* scripts auto-load .env.local via process.loadEnvFile() — no manual export needed. drizzle.config.ts selects the Turso or PostgreSQL schema and migration folder from DB_PROVIDER.

Command Reference

Local Development

For local development, use an SQLite file instead of a remote Turso database:

TURSO_DATABASE_URL="file:local.db"

This creates a local.db file in the project root. No Turso account required.

Multi-Region Replication

Turso supports read replicas in multiple regions for low-latency reads worldwide. Configure replicas through the Turso dashboard or the turso db replicate CLI command.

The Codapult database client supports automatic routing — reads go to the nearest replica, writes go to the primary. To enable replication management from the admin panel, set:

TURSO_API_TOKEN="your-platform-api-token"
TURSO_ORG_SLUG="your-org-slug"

PostgreSQL Support

Codapult also supports PostgreSQL via the DB_PROVIDER env var:

DB_PROVIDER="postgres"
DATABASE_URL="postgresql://user:password@host:5432/dbname?sslmode=require"

When using Postgres, src/lib/db/index.ts creates a postgres-js Drizzle client and loads src/lib/db/schema-pg.ts. Application code continues to import db from @/lib/db; table and column names are kept aligned across both schemas so existing query code does not change.

Supabase, Neon, RDS, Railway, and other managed PostgreSQL hosts work through the same DATABASE_URL path. Codapult does not ship Prisma as a second ORM because that would duplicate schema definitions, migrations, adapters, and plugin table contracts; if your team prefers Prisma, treat it as a deliberate project-level migration rather than a runtime provider switch.

Troubleshooting

Next Steps

• Migrations — production-safe schema changes and CI migration job
• Authentication — user accounts and sessions stored in the database
• Teams & Organizations — multi-tenant data model
• Modules — each module's tables can be removed independently