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.

| Component | Details | | ---------- | ----------------------------------------------- | | Database | Turso (LibSQL) — edge SQLite, free tier | | ORM | Drizzle ORM — type-safe, zero-overhead | | Schema | src/lib/db/schema.ts (single source of truth) | | Client | src/lib/db/index.ts | | Seed | src/lib/db/seed.ts | | Migrations | src/lib/db/migrations/ |

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:

| Convention | Rule | | --------------- | ---------------------------------------------------------------------------- | | Table names | Singular snake_case (user, organization_member) | | Column names | snake_case (created_at, user_id) | | Primary keys | text('id').primaryKey() — UUIDs or nanoid, never auto-increment | | Timestamps | integer('col', { mode: 'timestamp' }) with .$defaultFn(() => new Date()) | | Booleans | integer('col', { mode: 'boolean' }) (SQLite has no native bool) | | Foreign keys | Always specify onDelete (cascade, set null) | | Type-safe enums | .$type<TypeName>() with an exported TypeScript union |

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: '[email protected]',
});

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

| Command | Description | | ------------------ | ----------------------------------- | | pnpm db:push | Apply schema directly to database | | pnpm db:generate | Generate migration from schema diff | | pnpm db:migrate | Run pending migrations | | pnpm db:seed | Seed sample data |

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 CLI, then set the replica URLs in your environment. The database client handles automatic routing — reads go to the nearest replica, writes go to the primary.

Next Steps

• Authentication — user accounts and sessions stored in the database
• Teams & Organizations — multi-tenant data model
• Modules — each module's tables can be removed independently