database

Database Skill

Purpose

This skill covers database integration using Neon PostgreSQL and Drizzle ORM in the create-legal-app starter kit.

Key Concepts

• Neon: Serverless PostgreSQL database
• Drizzle ORM: TypeScript-first ORM
• Migrations: Version-controlled schema changes
• Type Safety: Fully typed database queries

Setup

Installation

bun add drizzle-orm @neondatabase/serverless
bun add -D drizzle-kit

Environment Variables

# .env.local
DATABASE_URL=postgresql://user:password@host/database?sslmode=require

Project Structure

/
├── lib/
│   ├── db/
│   │   ├── index.ts          # Database client
│   │   ├── schema.ts         # Database schema
│   │   └── migrations/       # Migration files
│   └── ...
├── drizzle.config.ts         # Drizzle configuration
└── package.json

Configuration

Drizzle Config

// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './lib/db/schema.ts',
  out: './lib/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Database Client

// lib/db/index.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);

export const db = drizzle(sql, { schema });

Schema Definition

Naming convention: use camelCase property names mapped to snake_case DB columns. Example: createdAt: timestamp("created_at"). For Better Auth tables specifically, always copy the templates from lib/auth/templates/ and keep the property names camelCase.

Basic Table

// lib/db/schema.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core';

export const cases = pgTable('cases', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: text('title').notNull(),
  description: text('description'),
  status: text('status').notNull().default('pending'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});