Database

Overview

Add a type-safe SQLite database to your project with Drizzle ORM for structured data storage.

Databases are perfect for:

Complex relational data & queries
User accounts & progress tracking
Leaderboards & achievements
Transaction history & analytics

Getting Started

$ playcademy init  # Select "Yes" for Database integration

$ playcademy db init

This scaffolds a complete database setup:

example.ts

index.ts

types.ts

seed.ts

database.ts

drizzle.config.ts

playcademy.config.js

Sample Route

A sample route is scaffolded in server/api/sample/database.ts to help you get started.

Working with Schemas

The CLI creates an example schema to get you started:

server/db/schema/example.ts

export const items = sqliteTable('items', {
    id: integer('id').primaryKey({ autoIncrement: true }),
    name: text('name').notNull(),
    data: text('data', { mode: 'json' }).$type<Record<string, unknown>>(),
    createdAt: text('created_at').notNull(),
})

Create your own schema files in the schema/ directory:

server/db/schema/players.ts

export const players = sqliteTable('players', {
    id: integer('id').primaryKey({ autoIncrement: true }),
    name: text('name').notNull(),
    score: integer('score').default(0),
})

Both approaches of db:push and db:generate / db:migrate can be used locally. Your choice also determines the deployment strategy — db:push maps to push mode, while db:generate/db:migrate maps to migrate mode.

Command	Description	Local
`db:push`	Push schema changes to local DB	✓
`db:generate`	Generate migration files	✓
`db:migrate`	Apply migrations locally	✓
`db:studio`	Open Drizzle Studio	✓

Drizzle Kit

These commands use drizzle-kit. They're for local development only.

Remote schema operations are handled by playcademy deploy.

Playcademy CLI Commands

Database operations using the Playcademy CLI:

Command	Description	Local	Remote
`db schema`	Print full schema SQL	✓
`db diff`	Preview migration SQL	✓
`db reset`	Reset database	✓	✓
`db seed`	Seed database with test data	✓	✓

View Full Command Documentation

See Database CLI Reference for all options and flags.

Development Workflow

The push approach applies schema changes directly without migration files:

Command

$ playcademy dev  # Start dev server first$ bun db:push  # Push schema to local DB

After modifying your schema, run bun db:push again to apply changes instantly.

Run the development server at least once before using db:push: the database file must exist first.

The migration approach generates SQL files for version control:

Command

$ playcademy dev  # Start dev server first$ bun db:generate  # Generate migration from schema$ bun db:migrate  # Apply migration to local DB

After modifying your schema, run db:generate to create a migration file, then db:migrate to apply it.

Migration files are stored in db/migrations/ and can be committed to version control.

Deploy schema changes to production:

Command

$ bun db:push  # or db:migrate locally$ playcademy db diff  # Preview migration SQL$ playcademy deploy  # Deploy with auto-migration

playcademy deploy detects schema changes and applies migrations automatically to your remote database.

Reset database and populate with test data:

Command

$ playcademy db seed  # Reset + seed (recommended)$ playcademy db reset  # Reset only$ playcademy db seed --no-reset  # Seed only

Add --remote flag to operate on your deployed database instead of local.

Using the Database

Access your database via c.env.DB in your API routes:

server/api/scores.ts

import { desc } from 'drizzle-orm'

import { getDb, schema } from '../../db'

export async function GET(c: Context) {
    const db = getDb(c.env.DB)

    const scores = await db.query.scores.findMany({
        limit: 10,
        orderBy: desc(schema.scores.score),
        with: { user: true }, // Include relations
    })

    return c.json({ scores })
}

server/api/scores.ts

import { getDb, schema } from '../../db'

export async function POST(c: Context) {
    const { score, level } = await c.req.json()
    const db = getDb(c.env.DB)

    // Get or create user
    let user = await db.select().from(schema.users).limit(1).get()

    if (!user) {
        ;[user] = await db
            .insert(schema.users)
            .values({ name: 'Demo Player', createdAt: new Date().toISOString() })
            .returning()
    }

    // Insert score
    const [newScore] = await db
        .insert(schema.scores)
        .values({ userId: user.id, score, level, createdAt: new Date().toISOString() })
        .returning()

    return c.json({ success: true, data: newScore })
}

server/api/users.ts

import { eq } from 'drizzle-orm'

import { getDb, schema } from '../../db'

export async function PATCH(c: Context) {
    const { userId, name } = await c.req.json()
    const db = getDb(c.env.DB)

    const [updated] = await db
        .update(schema.users)
        .set({ name })
        .where(eq(schema.users.id, userId))
        .returning()

    return c.json({ user: updated })
}

server/api/users.ts

import { eq } from 'drizzle-orm'

import { getDb, schema } from '../../db'

export async function DELETE(c: Context) {
    const { userId } = await c.req.json()
    const db = getDb(c.env.DB)

    await db.delete(schema.users).where(eq(schema.users.id, userId))

    return c.json({ success: true })
}

Learn More About Drizzle Queries

See the Drizzle ORM Query documentation for advanced queries, joins, and more.

Deployment

Schema changes are automatically applied when you deploy:

Command

$ playcademy deploy

The CLI supports two schema strategies for deployment, matching how you work with Drizzle locally.

The push strategy uses schema diffing, the same approach as drizzle-kit push.

The CLI snapshots your schema after each deployment and compares it to your current schema to generate migration SQL on the fly.

Detect schema changes since last deployment
Generate migration SQL by diffing snapshots
Apply generated SQL to your remote database

Auto-Detection

This is the default strategy. Only changed tables/columns trigger migrations — no migration files needed.

The migrate strategy uses your committed migration files, the same approach as drizzle-kit generate + drizzle-kit migrate.

Read migration journal
Determine which migrations haven't been applied yet
Concatenate and apply unapplied migration SQL to your remote database

When to Use Migrate

Use this strategy when you want full control over migration SQL, need to review migrations in code review, or want versioned migration history in your repository.

Strategy Detection

The CLI auto-detects which strategy to use based on your project structure.

You can also set the strategy explicitly in your config:

playcademy.config.js

export default {
    name: 'My Project',
    integrations: {
        database: {
            strategy: 'migrate', // or 'push'
        },
    },
}

Preview Changes

Use playcademy db diff to preview what SQL will be applied on next deployment:

Command

$ playcademy db diff

This works with both strategies, displaying the generated diff SQL (push mode) or the unapplied migration SQL (migrate mode).

Switching from Push to Migrate

If you started with push-mode and want to switch to versioned migration files, make sure out is set in your drizzle.config.ts, then:

Command

$ bun db:generate                      # Create baseline migration$ playcademy db migrate-from-push      # Mark as applied & switch modes

From this point on, run bun db:generate after schema changes, then playcademy deploy to apply.

Seeding

Populate your database with initial or test data using seed files.

Seed File

After running playcademy init (or playcademy db init), a seed file is automatically generated at server/db/seed.ts.

server/db/seed.ts

import { getDb } from '.'
import * as schema from './schema'

export async function seed(c: Context) {
    const db = getDb(c.env.DB)

    // Seed users
    const [user] = await db
        .insert(schema.users)
        .values({
            name: 'Demo User',
            createdAt: new Date().toISOString(),
        })
        .returning()

    // Seed related data
    await db.insert(schema.scores).values({
        userId: user.id,
        score: 100,
        level: 1,
    })
}

Key Points

This file must export async function seed(c: Context)
Use getDb(c.env.DB) to get Drizzle instance
Full Drizzle power: transactions, upserts, relations

Local Seeding

Command

$ playcademy db seed              # Resets database first (recommended)$ playcademy db seed --no-reset   # Seed without reset

Remote Seeding

Command

$ playcademy db seed --remote              # Staging (resets first)$ playcademy db seed --remote --no-reset   # Staging (no reset)$ playcademy db seed --remote --env production  # Production$ playcademy db seed --remote -f           # Skip secrets sync check

Secrets Sync Check

Before remote seeding, the CLI checks if your local .env secrets match remote.

If out of sync, you'll be prompted to push secrets first. Use -f or --force to skip this check.

See Secrets Guide for details.

Remote seeding with reset

When seeding your remote database, you'll be asked to:

Confirm with yes/no
Type the project slug to confirm

All existing data will be deleted!

Database Reset

Reset your database without seeding:

Command

$ playcademy db reset            # Local$ playcademy db reset -f         # Local, skip confirmation$ playcademy db reset --remote   # Remote

Overview

Getting Started

Working with Schemas

Database Management

Drizzle Kit Commands

Playcademy CLI Commands

Development Workflow

Using the Database

Deployment

Strategy Detection

Preview Changes

Switching from Push to Migrate

Seeding

Seed File

Local Seeding

Remote Seeding

Database Reset

What's Next?

Custom Routes Guide

Drizzle ORM Documentation

Deployment Guide

CLI Commands

On this page