Database Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• drizzle.config.ts
• drizzle/0000_elite_eternals.sql
• package.json
• drizzle/schema.ts
• server/db.ts
• drizzle/meta/0000_snapshot.json

The database migration system in this project is managed by Drizzle ORM and its command-line tool, Drizzle Kit. This system is responsible for keeping the MySQL database schema in sync with the application's data models defined in TypeScript. It provides a structured and automated way to evolve the database schema over time, starting with the initial users table required for authentication.

The entire process is streamlined through a single npm script, db:push, which handles both the generation of migration files and their application to the database. This ensures that schema changes are version-controlled and consistently applied across different environments.

Sources: package.json:13, drizzle.config.ts, drizzle/schema.ts

Tooling and Configuration

The project utilizes a specific set of tools for database management and migrations.

Sources: package.json:20, 52, 69, drizzle.config.ts

Core Dependencies

The following packages from package.json are essential for the database layer:

Package	Version	Description
drizzle-orm	^0.44.7	The core ORM library for database interaction.
drizzle-kit	^0.31.8	The CLI tool for generating and managing migrations.
mysql2	^3.16.0	The MySQL database driver used by Drizzle to connect to the database.

Sources: package.json:29, 52, 69

Drizzle Configuration

Configuration for Drizzle Kit is located in drizzle.config.ts. This file specifies the location of the schema definition, the output directory for migration files, the database dialect, and connection credentials.

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
  throw new Error("DATABASE_URL is required to run drizzle commands");
}

export default defineConfig({
  schema: "./drizzle/schema.ts",
  out: "./drizzle",
  dialect: "mysql",
  dbCredentials: {
    url: connectionString,
  },
});

Sources: drizzle.config.ts:1-14

The configuration relies on a DATABASE_URL environment variable for the database connection string.

Option	Value	Description
schema	./drizzle/schema.ts	Path to the file defining the database schema.
out	./drizzle	Directory where generated migration files are stored.
dialect	mysql	Specifies that the target database is MySQL.
dbCredentials.url	connectionString	The database connection URL from process.env.DATABASE_URL.

Sources: drizzle.config.ts:9-13

Database Schema

The application's database schema is defined programmatically using Drizzle ORM's syntax in drizzle/schema.ts.

Sources: drizzle/schema.ts

The users Table

The initial schema contains a single table, users, which is fundamental for the authentication and user management system.

This diagram shows the structure of the users table.

The table's columns are defined as follows:

Column	Type	Constraints	Description
id	int	AUTO_INCREMENT, PRIMARY KEY	Surrogate primary key for relations.
openId	varchar(64)	NOT NULL, UNIQUE	Manus OAuth identifier.
name	text	NULL	User's full name.
email	varchar(320)	NULL	User's email address.
loginMethod	varchar(64)	NULL	The method used for login (e.g., 'google').
role	enum('user', 'admin')	NOT NULL, DEFAULT 'user'	User's role within the application.
createdAt	timestamp	NOT NULL, DEFAULT NOW()	Timestamp of when the user record was created.
updatedAt	timestamp	NOT NULL, DEFAULT NOW(), ON UPDATE NOW()	Timestamp of the last update to the record.
lastSignedIn	timestamp	NOT NULL, DEFAULT NOW()	Timestamp of the user's last sign-in.

Sources: drizzle/schema.ts:7-24, drizzle/meta/0000_snapshot.json:10-80

Migration Workflow

The process of generating and applying database migrations is automated via an npm script.

db:push Script

The package.json file defines a db:push script which is the primary command for managing schema changes.

// package.json (scripts section)
"scripts": {
    "db:push": "drizzle-kit generate && drizzle-kit migrate",
    ...
}

Sources: package.json:13

This command executes two drizzle-kit operations in sequence:

1. drizzle-kit generate: Compares the current schema in drizzle/schema.ts with the last known state and generates a new SQL migration file in the drizzle/ directory if differences are found. It also updates the snapshot in drizzle/meta/.
2. drizzle-kit migrate: Applies any pending SQL migration files from the drizzle/ directory to the database specified by DATABASE_URL.

The following diagram illustrates the developer workflow for updating the database schema.

Initial Migration File

The first migration, 0000_elite_eternals.sql, was generated by this process. It contains the raw SQL to create the users table.

-- drizzle/0000_elite_eternals.sql
CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`openId` varchar(64) NOT NULL,
	`name` text,
	`email` varchar(320),
	`loginMethod` varchar(64),
	`role` enum('user','admin') NOT NULL DEFAULT 'user',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`lastSignedIn` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_openId_unique` UNIQUE(`openId`)
);

Sources: drizzle/0000_elite_eternals.sql

Database Interaction

The defined schema is used throughout the server-side application to interact with the database. The server/db.ts file provides functions that leverage the Drizzle ORM and the users schema.

Key functions include:

• getDb(): Lazily initializes and returns the Drizzle database instance.
• upsertUser(user: InsertUser): Inserts a new user or updates an existing one based on their openId. This function directly uses the InsertUser type inferred from the users schema.
• getUserByOpenId(openId: string): Retrieves a user from the database using their unique openId.

This demonstrates the connection between the schema definition (drizzle/schema.ts) and its practical application in the codebase (server/db.ts).

Sources: server/db.ts:7-105