Drizzle Game Schema

Production-ready database patterns for game persistence using Drizzle ORM with SQLite/Turso.

Schema Organization

packages/db/src/schema/ ├── index.ts # Export all schemas ├── auth.ts # Better-Auth tables ├── player.ts # Player profiles, stats ├── inventory.ts # Items, currencies ├── game-session.ts # Active game state ├── progression.ts # Achievements, unlocks └── social.ts # Leaderboards, friends

Core Schemas

Player Profile

// schema/player.ts import { sqliteTable, text, integer, real } from 'drizzle-orm/sqlite-core'; import { user } from './auth';

export const playerProfile = sqliteTable('player_profile', { id: text('id').primaryKey(), userId: text('user_id').notNull().references(() => user.id, { onDelete: 'cascade' }),

// Display displayName: text('display_name').notNull(), avatarUrl: text('avatar_url'),

// Stats totalScore: integer('total_score').notNull().default(0), highScore: integer('high_score').notNull().default(0), gamesPlayed: integer('games_played').notNull().default(0), totalPlayTime: integer('total_play_time').notNull().default(0), // seconds

// Progression level: integer('level').notNull().default(1), experience: integer('experience').notNull().default(0),

// Timestamps createdAt: integer('created_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()), updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()), lastActiveAt: integer('last_active_at', { mode: 'timestamp' }), });

// Indexes for common queries export const playerProfileIndexes = { byUserId: sqliteIndex('player_by_user').on(playerProfile.userId), byScore: sqliteIndex('player_by_score').on(playerProfile.totalScore), byLevel: sqliteIndex('player_by_level').on(playerProfile.level), };

Inventory & Currencies

// schema/inventory.ts import { sqliteTable, text, integer, real, primaryKey } from 'drizzle-orm/sqlite-core';

export const playerCurrency = sqliteTable('player_currency', { playerId: text('player_id').notNull().references(() => playerProfile.id), currencyType: text('currency_type').notNull(), // 'coins', 'tulipBulbs', 'seeds' amount: integer('amount').notNull().default(0), lifetimeEarned: integer('lifetime_earned').notNull().default(0), updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(), }, (table) => ({ pk: primaryKey({ columns: [table.playerId, table.currencyType] }), }));

export const inventoryItem = sqliteTable('inventory_item', { id: text('id').primaryKey(), playerId: text('player_id').notNull().references(() => playerProfile.id),

itemType: text('item_type').notNull(), // 'simulin', 'seed', 'decoration' itemId: text('item_id').notNull(), // Reference to item definition quantity: integer('quantity').notNull().default(1),

// Item state (for unique items like Simulins) metadata: text('metadata', { mode: 'json' }).$type<Record<string, unknown>>(),

acquiredAt: integer('acquired_at', { mode: 'timestamp' }).notNull(), equippedSlot: text('equipped_slot'), // null if not equipped });

export const inventoryIndexes = { byPlayer: sqliteIndex('inv_by_player').on(inventoryItem.playerId), byType: sqliteIndex('inv_by_type').on(inventoryItem.playerId, inventoryItem.itemType), };

Game Session (Save State)

// schema/game-session.ts import { sqliteTable, text, integer, blob } from 'drizzle-orm/sqlite-core';

export const gameSession = sqliteTable('game_session', { id: text('id').primaryKey(), playerId: text('player_id').notNull().references(() => playerProfile.id),

// Session metadata sessionType: text('session_type').notNull(), // 'campaign', 'daily', 'event' status: text('status').notNull().default('active'), // 'active', 'completed', 'abandoned'

// Game state (serialized) gameState: text('game_state', { mode: 'json' }).$type<{ time: { season: number; day: number; phase: string }; resources: { tulipBulbs: number; coins: number; stamina: number }; hexes: Record<string, unknown>; troubles: Record<string, unknown>; score: number; }>(),

// Metrics startedAt: integer('started_at', { mode: 'timestamp' }).notNull(), lastSavedAt: integer('last_saved_at', { mode: 'timestamp' }).notNull(), completedAt: integer('completed_at', { mode: 'timestamp' }), playDuration: integer('play_duration').notNull().default(0), // seconds

// Results (when completed) finalScore: integer('final_score'), rewards: text('rewards', { mode: 'json' }).$type<{ coins: number; experience: number; items: string[]; }>(), });

Achievements & Unlocks

// schema/progression.ts import { sqliteTable, text, integer, primaryKey } from 'drizzle-orm/sqlite-core';

export const achievement = sqliteTable('achievement', { id: text('id').primaryKey(), name: text('name').notNull(), description: text('description').notNull(), category: text('category').notNull(), // 'farming', 'gambling', 'social'

// Requirements requirement: text('requirement', { mode: 'json' }).$type<{ type: string; target: number; conditions?: Record<string, unknown>; }>(),

// Rewards rewardCoins: integer('reward_coins').default(0), rewardExp: integer('reward_exp').default(0), rewardItem: text('reward_item'),

// Display iconUrl: text('icon_url'), rarity: text('rarity').default('common'), // common, rare, epic, legendary hidden: integer('hidden', { mode: 'boolean' }).default(false), });

export const playerAchievement = sqliteTable('player_achievement', { playerId: text('player_id').notNull().references(() => playerProfile.id), achievementId: text('achievement_id').notNull().references(() => achievement.id),

progress: integer('progress').notNull().default(0), completed: integer('completed', { mode: 'boolean' }).notNull().default(false), completedAt: integer('completed_at', { mode: 'timestamp' }), claimed: integer('claimed', { mode: 'boolean' }).notNull().default(false), }, (table) => ({ pk: primaryKey({ columns: [table.playerId, table.achievementId] }), }));

export const unlock = sqliteTable('player_unlock', { playerId: text('player_id').notNull().references(() => playerProfile.id), unlockType: text('unlock_type').notNull(), // 'simulin', 'table', 'cosmetic' unlockId: text('unlock_id').notNull(), unlockedAt: integer('unlocked_at', { mode: 'timestamp' }).notNull(), }, (table) => ({ pk: primaryKey({ columns: [table.playerId, table.unlockType, table.unlockId] }), }));

Leaderboards

// schema/social.ts import { sqliteTable, text, integer, index } from 'drizzle-orm/sqlite-core';

export const leaderboardEntry = sqliteTable('leaderboard_entry', { id: text('id').primaryKey(), playerId: text('player_id').notNull().references(() => playerProfile.id),

boardType: text('board_type').notNull(), // 'daily', 'weekly', 'alltime', 'season_1' period: text('period').notNull(), // '2026-01-10', '2026-W02', 'alltime'

score: integer('score').notNull(), rank: integer('rank'), // Computed/cached

metadata: text('metadata', { mode: 'json' }).$type<{ gamesPlayed?: number; bestCombo?: number; favoriteTable?: string; }>(),

updatedAt: integer('updated_at', { mode: 'timestamp' }).notNull(), }, (table) => ({ scoreIdx: index('lb_score').on(table.boardType, table.period, table.score), playerIdx: index('lb_player').on(table.playerId, table.boardType), }));

Common Queries

Get Player with Stats

import { eq } from 'drizzle-orm';

async function getPlayerWithStats(userId: string) { return db.query.playerProfile.findFirst({ where: eq(playerProfile.userId, userId), with: { currencies: true, achievements: { where: eq(playerAchievement.completed, true), }, }, }); }

Update Currency (Transaction)

async function addCurrency( playerId: string, type: string, amount: number ) { return db.transaction(async (tx) => { const current = await tx.query.playerCurrency.findFirst({ where: and( eq(playerCurrency.playerId, playerId), eq(playerCurrency.currencyType, type) ), });

if (current) {
  await tx.update(playerCurrency)
    .set({ 
      amount: current.amount + amount,
      lifetimeEarned: amount > 0 
        ? current.lifetimeEarned + amount 
        : current.lifetimeEarned,
      updatedAt: new Date(),
    })
    .where(and(
      eq(playerCurrency.playerId, playerId),
      eq(playerCurrency.currencyType, type)
    ));
} else {
  await tx.insert(playerCurrency).values({
    playerId,
    currencyType: type,
    amount: Math.max(0, amount),
    lifetimeEarned: Math.max(0, amount),
    updatedAt: new Date(),
  });
}

}); }

Leaderboard Query

async function getLeaderboard( boardType: string, period: string, limit = 100 ) { return db.select({ rank: leaderboardEntry.rank, score: leaderboardEntry.score, player: { id: playerProfile.id, displayName: playerProfile.displayName, avatarUrl: playerProfile.avatarUrl, level: playerProfile.level, }, }) .from(leaderboardEntry) .innerJoin(playerProfile, eq(leaderboardEntry.playerId, playerProfile.id)) .where(and( eq(leaderboardEntry.boardType, boardType), eq(leaderboardEntry.period, period) )) .orderBy(desc(leaderboardEntry.score)) .limit(limit); }

Save Game State

async function saveGameState( sessionId: string, gameState: GameState ) { await db.update(gameSession) .set({ gameState: { time: gameState.time, resources: gameState.resources, hexes: Object.fromEntries(gameState.hexes), troubles: gameState.troubles, score: gameState.score, }, lastSavedAt: new Date(), playDuration: sqlplay_duration + ${AUTOSAVE_INTERVAL}, }) .where(eq(gameSession.id, sessionId)); }

Migration Workflow

Generate migration from schema changes

bun run db:generate

Apply migrations to database

bun run db:migrate

Push schema directly (dev only)

bun run db:push

Open Drizzle Studio

bun run db:studio

Performance Tips

• Index frequently filtered columns: playerId, timestamps, scores

• Use JSON columns sparingly: Good for flexible data, bad for querying

• Batch inserts: Use insert().values([...]) for bulk operations

• Denormalize carefully: Store computed ranks, not just scores

• Archive old data: Move completed sessions to archive tables