database-schema-manager

Database Schema Manager

Quick Start

This skill manages LibSQL/Turso database schemas:

Schema design: Create tables with proper types and indexes
Zod validation: Schema-first approach with type inference
Migrations: Version-controlled schema changes
Type safety: Ensure TypeScript types match database schema

When to Use

Creating new database tables
Writing database migrations
Implementing schema validation
Need type-safe database operations

Database Connection

// src/lib/db.ts import { createClient } from '@libsql/client';

export const db = createClient({ url: import.meta.env.VITE_TURSO_DATABASE_URL, authToken: import.meta.env.VITE_TURSO_AUTH_TOKEN, });

// Test connection export async function testConnection(): Promise<boolean> { try { await db.execute('SELECT 1'); return true; } catch (error) { console.error('Database connection failed:', error); return false; } }

Table Creation Pattern

-- migrations/001_create_projects_table.sql CREATE TABLE IF NOT EXISTS projects ( id TEXT PRIMARY KEY, title TEXT NOT NULL, description TEXT, genre TEXT NOT NULL, target_word_count INTEGER NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL ) STRICT;

CREATE INDEX idx_projects_created_at ON projects(created_at DESC); CREATE INDEX idx_projects_genre ON projects(genre);

Key Guidelines:

TEXT PRIMARY KEY for UUID identifiers
INTEGER for timestamps (Unix milliseconds)
TEXT for enums (validated by Zod, not database constraints)
STRICT mode for type safety
Indexes for frequently queried columns

Zod Validation (Schema-First)

// src/features/projects/types/project.schema.ts import { z } from 'zod';

// Zod schema (source of truth) export const projectSchema = z.object({ id: z.string().uuid(), title: z.string().min(1).max(200), description: z.string().max(1000).optional(), genre: z.enum(['fantasy', 'scifi', 'mystery', 'romance', 'thriller']), targetWordCount: z.number().int().positive().max(1000000), createdAt: z.number().int().positive(), updatedAt: z.number().int().positive(), });

// Infer TypeScript type from Zod schema export type Project = z.infer<typeof projectSchema>;

// Partial schema for updates (all fields optional except id) export const projectUpdateSchema = projectSchema .partial() .required({ id: true }); export type ProjectUpdate = z.infer<typeof projectUpdateSchema>;

// Schema for creation (omit id, timestamps) export const projectCreateSchema = projectSchema.omit({ id: true, createdAt: true, updatedAt: true, }); export type ProjectCreate = z.infer<typeof projectCreateSchema>;

Type-Safe Database Operations

Service Pattern with Validation

// src/features/projects/services/projectService.ts import { db } from '@/lib/db'; import { projectSchema, projectCreateSchema, type Project, } from '../types/project.schema';

export const projectService = { async getAll(): Promise<Project[]> { const result = await db.execute( 'SELECT * FROM projects ORDER BY created_at DESC', ); return z.array(projectSchema).parse(result.rows); },

async getById(id: string): Promise<Project | null> { const result = await db.execute({ sql: 'SELECT * FROM projects WHERE id = ?', args: [id], }); if (result.rows.length === 0) return null; return projectSchema.parse(result.rows[0]); },

async create(data: unknown): Promise<Project> { // Validate input const validated = projectCreateSchema.parse(data);

const project: Project = {
  id: crypto.randomUUID(),
  ...validated,
  createdAt: Date.now(),
  updatedAt: Date.now(),
};

await db.execute({
  sql: `INSERT INTO projects (id, title, description, genre, target_word_count, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)`,
  args: [
    project.id,
    project.title,
    project.description ?? null,
    project.genre,
    project.targetWordCount,
    project.createdAt,
    project.updatedAt,
  ],
});

return project;

async update(id: string, data: unknown): Promise<Project> { // Validate input const validated = projectCreateSchema.partial().parse(data);

const updatedAt = Date.now();

await db.execute({
  sql: `UPDATE projects
        SET title = COALESCE(?, title),
            description = COALESCE(?, description),
            genre = COALESCE(?, genre),
            target_word_count = COALESCE(?, target_word_count),
            updated_at = ?
        WHERE id = ?`,
  args: [
    validated.title,
    validated.description,
    validated.genre,
    validated.targetWordCount,
    updatedAt,
    id,
  ],
});

const updated = await projectService.getById(id);
if (!updated) throw new Error('Project not found after update');
return updated;

async delete(id: string): Promise<void> { await db.execute({ sql: 'DELETE FROM projects WHERE id = ?', args: [id], }); }, };

Migration Pattern

Migration File Structure

migrations/ ├── 001_create_projects_table.sql ├── 002_create_chapters_table.sql ├── 003_add_version_column.sql └── README.md

Migration Template

-- migrations/003_add_version_column.sql -- Description: Add version tracking column to projects table -- Date: 2024-12-04

-- Add new column ALTER TABLE projects ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Create index for version queries CREATE INDEX idx_projects_version ON projects(version);

-- Update existing rows (if needed) UPDATE projects SET version = 1 WHERE version IS NULL;

Migration Runner

// scripts/run-migration.ts import { db } from '../src/lib/db'; import { readFileSync } from 'fs';

async function runMigration(filename: string): Promise<void> { const sql = readFileSync(migrations/${filename}, 'utf-8');

// Split by semicolon and execute each statement const statements = sql .split(';') .map(s => s.trim()) .filter(s => s.length > 0 && !s.startsWith('--'));

for (const statement of statements) { await db.execute(statement); }

console.log(✅ Migration ${filename} completed); }

// Usage: pnpm tsx scripts/run-migration.ts 003_add_version_column.sql runMigration(process.argv[2]).catch(console.error);

Transaction Pattern

// src/features/projects/services/projectService.ts export async function createProjectWithChapters( projectData: ProjectCreate, chapterTitles: string[], ): Promise<Project> { // LibSQL supports transactions const transaction = await db.batch([ { sql: INSERT INTO projects (id, title, genre, created_at, updated_at) VALUES (?, ?, ?, ?, ?), args: [ crypto.randomUUID(), projectData.title, projectData.genre, Date.now(), Date.now(), ], }, ...chapterTitles.map((title, index) => ({ sql: INSERT INTO chapters (id, project_id, title, position, created_at) VALUES (?, ?, ?, ?, ?), args: [crypto.randomUUID(), projectId, title, index, Date.now()], })), ]);

return await projectService.getById(projectId); }

Common Patterns

Enum Validation

// Zod enum matches database TEXT column export const genreSchema = z.enum(['fantasy', 'scifi', 'mystery', 'romance']);

// Usage in table genre: genreSchema.parse(row.genre);

Timestamp Handling

// Store as Unix milliseconds (INTEGER) createdAt: Date.now();

// Convert to Date when needed const createdDate = new Date(project.createdAt);

Null vs Undefined

// Database uses NULL description TEXT -- Can be NULL

// TypeScript uses optional (undefined) description?: string

// Convert between them description: row.description ?? undefined // NULL → undefined args: [project.description ?? null] // undefined → NULL

Camel Case Conversion

// Database uses snake_case target_word_count INTEGER

// TypeScript uses camelCase targetWordCount: number

// Convert in queries const row = { target_word_count: 50000 }; const project = { targetWordCount: row.target_word_count };

Schema Consistency Checklist

Zod schema matches database columns
TypeScript types inferred from Zod schema
Enum values match across all layers
Timestamps stored as Unix milliseconds (INTEGER)
NULL/undefined handled correctly
snake_case database ↔ camelCase TypeScript mapping
Indexes created for frequently queried columns
STRICT mode enabled on tables
Validation errors handled gracefully

Common Issues

Validation fails on database read

Ensure Zod schema matches database column types exactly
Check for NULL vs undefined handling

Type mismatch between database and TypeScript

Use z.infer<typeof schema> to generate TypeScript types from Zod
Don't manually create TypeScript interfaces

Migration fails with syntax error

LibSQL may not support all SQLite features
Test migrations locally before deploying

Enum validation error

Verify enum values match between Zod schema and database inserts
Remember: database stores TEXT, Zod validates values

Success Criteria

All database operations type-checked at compile time
Zod schemas validate all inputs before database operations
No runtime type errors from database reads
Migrations version-controlled and reproducible
Schema changes documented

References

LibSQL Documentation: https://docs.turso.tech/libsql
Zod Documentation: https://zod.dev/
SQLite Strict Tables: https://www.sqlite.org/stricttables.html

database-schema-manager

Safety Notice

Copy this and send it to your AI assistant to learn

Source Transparency

Related Skills

iterative-refinement

gemini-websearch

task-decomposition

skill-creator