How to Add New Database Migrations in the TCC Repository

To add new database migrations in the castrozan/tcc repository, extend the existing migrations.ts file with additional SQL statements using db.exec(), ensuring idempotency with IF NOT EXISTS clauses.

The TCC project uses an embedded SQLite database to persist data across its dummy applications. Because migrations execute automatically on every application startup, adding new database migrations requires modifying the central migration script to include idempotent schema changes that safely run repeatedly.

How Database Migrations Work in TCC

The migration system follows a simple pattern where schema creation logic runs synchronously when the application boots. This ensures the database is ready before any business logic executes.

Migration Entry Point

The initializeDatabase() function in src/infrastructure/database/sqlite/migrations.ts serves as the primary migration entry point. This function retrieves a database connection and executes SQL statements to create tables and indexes.

According to the source code in castrozan/tcc, the function uses getDatabase() to obtain a better-sqlite3 connection, then runs db.exec() with schema definitions.

Database Client and Connection

The getDatabase() function in src/infrastructure/database/sqlite/sqlite-client.ts manages the SQLite connection lifecycle. It creates the database file if it does not exist and returns a singleton connection instance that migrations and application code share.

Initialization Flow

The initializeDatabase() function in src/infrastructure/database/init.ts wraps the migration call and verifies the connection. When the application starts via src/index.ts, it invokes this initialization routine, causing migrations to run once per process start.

How to Add New Database Migrations

Because the migration script executes on every startup, you must write idempotent SQL that safely handles existing schema objects. Follow these steps to extend the database schema.

Step 1: Locate the Migration File

Navigate to the migration script for the specific dummy application you are modifying:

• professionals-dummy-app: professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts
• equipments-dummy-app: equipments-dummy-app/src/infrastructure/database/sqlite/migrations.ts

Step 2: Append Idempotent SQL Statements

Add new db.exec() calls or extend existing ones with SQL statements that use IF NOT EXISTS clauses. This ensures the migration succeeds even if the schema objects already exist from a previous run.

For new tables, use CREATE TABLE IF NOT EXISTS. For indexes, use CREATE INDEX IF NOT EXISTS. For adding columns, check existence first or use ALTER TABLE ADD COLUMN (which is idempotent in SQLite only if the column does not exist).

Step 3: Verify on Application Startup

Start the application using npm start or your runtime command. The initializeDatabase() function will execute your new SQL. Check the console output for any SQL errors, and verify the schema using a SQLite browser or the application's data access layer.

Database Migration Examples

These practical examples demonstrate common schema changes in the TCC repository context.

Creating a New Table

To add a new Department table to the professionals dummy app, extend migrations.ts:

// professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts
export function initializeDatabase(): void {
  const db = getDatabase();

  db.exec(`
    -- Existing Professional table
    CREATE TABLE IF NOT EXISTS Professional (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      specialty TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- New Department table
    CREATE TABLE IF NOT EXISTS Department (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      description TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    -- Link Professional to Department
    ALTER TABLE Professional ADD COLUMN departmentId INTEGER;
    CREATE INDEX IF NOT EXISTS idx_professional_department 
      ON Professional(departmentId);
  `);
}

Adding Columns to Existing Tables

When adding columns to existing tables, guard against errors from duplicate column names:

function addEmailColumn(): void {
  const db = getDatabase();
  
  // Check if column exists before adding
  const columnExists = db.prepare(
    "SELECT name FROM pragma_table_info('Professional') WHERE name = 'email'"
  ).get();
  
  if (!columnExists) {
    db.exec(`ALTER TABLE Professional ADD COLUMN email TEXT;`);
  }
}

Adding Indexes for Performance

Create indexes to optimize query performance without failing if they already exist:

db.exec(`
  CREATE INDEX IF NOT EXISTS idx_professional_name 
    ON Professional(name);
  
  CREATE INDEX IF NOT EXISTS idx_professional_created 
    ON Professional(createdAt);
`);

Key Files in the Migration System

Understanding these files helps you navigate the codebase when adding new database migrations:

File	Purpose	Location
migrations.ts	Contains initializeDatabase() function that executes schema SQL	src/infrastructure/database/sqlite/migrations.ts
sqlite-client.ts	Provides getDatabase() for connection management	src/infrastructure/database/sqlite/sqlite-client.ts
init.ts	Wraps migration execution and connection verification	src/infrastructure/database/init.ts
index.ts	Application entry point that triggers initializeDatabase()	src/index.ts

Both professionals-dummy-app and equipments-dummy-app maintain identical structures under their respective root directories.

Summary

• Database migrations in the TCC repository run automatically via initializeDatabase() in migrations.ts every time the application starts.
• Add new migrations by extending the SQL in src/infrastructure/database/sqlite/migrations.ts with idempotent statements using IF NOT EXISTS clauses.
• Ensure idempotency because the migration script executes on every startup; use CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS, and existence checks for ALTER TABLE operations.
• Verify changes by starting the application and checking that the schema updates apply without SQL errors.

Frequently Asked Questions

Where are database migrations stored in the TCC repository?

Database migrations are stored in src/infrastructure/database/sqlite/migrations.ts within each dummy application directory (e.g., professionals-dummy-app/ and equipments-dummy-app/). This file contains the initializeDatabase() function that executes SQL statements to create tables and indexes when the application starts.

How do I make database migrations idempotent in SQLite?

Use IF NOT EXISTS clauses for all creation statements. For tables, use CREATE TABLE IF NOT EXISTS; for indexes, use CREATE INDEX IF NOT EXISTS. When adding columns with ALTER TABLE, first check if the column exists using PRAGMA table_info() or query the sqlite_master table, then conditionally execute the ALTER TABLE statement only if the column is missing.

Can I use a migration tool like Knex or db-migrate with TCC?

The current TCC implementation uses a simple custom migration pattern that runs raw SQL via better-sqlite3. While the existing code does not use Knex or db-migrate, you could refactor src/infrastructure/database/sqlite/migrations.ts to use a dedicated migration library if you need versioned migrations, rollback capabilities, or complex schema evolution tracking. For the current lightweight setup, extending the existing SQL script remains the recommended approach.

When do database migrations run in the application lifecycle?

Migrations run once per process start when the application invokes initializeDatabase() from src/infrastructure/database/init.ts. This typically happens in the main entry point (src/index.ts) before the application begins accepting requests or processing business logic. The migration executes synchronously, ensuring the database schema is ready before any data access occurs.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}