Database

Overview

Codapult uses Turso (LibSQL) as its database — an edge-hosted SQLite database with a generous free tier — and Drizzle ORM for type-safe queries. No raw SQL is needed.

Schema Conventions

The schema file src/lib/db/schema.ts is the single source of truth for all tables. 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.

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, all Drizzle queries use the PostgreSQL dialect automatically. The schema file uses a compatibility layer that maps SQLite types to Postgres equivalents.

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