# How to Add New Database Migrations in the TCC Repository

> Learn how to add new database migrations in the castrozan/tcc repository by extending the migrations.ts file with SQL statements and IF NOT EXISTS clauses for idempotency.

- Repository: [Lucas Zanoni⠀⠀⠀⠀⠀ ⠀╱|、 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ (˚ˎ 。7 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ |、˜〵 ⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀⠀ じしˍ,)ノ/tcc](https://github.com/castrozan/tcc)
- Tags: how-to-guide
- Published: 2026-02-27

---

**To add new database migrations in the castrozan/tcc repository, extend the existing [`migrations.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/init.ts) wraps the migration call and verifies the connection. When the application starts via [`src/index.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts)
- **equipments-dummy-app**: [`equipments-dummy-app/src/infrastructure/database/sqlite/migrations.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/migrations.ts):

```typescript
// 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:

```typescript
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:

```typescript
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`](https://github.com/castrozan/tcc/blob/main/migrations.ts) | Contains `initializeDatabase()` function that executes schema SQL | [`src/infrastructure/database/sqlite/migrations.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/sqlite/migrations.ts) |
| [`sqlite-client.ts`](https://github.com/castrozan/tcc/blob/main/sqlite-client.ts) | Provides `getDatabase()` for connection management | [`src/infrastructure/database/sqlite/sqlite-client.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/sqlite/sqlite-client.ts) |
| [`init.ts`](https://github.com/castrozan/tcc/blob/main/init.ts) | Wraps migration execution and connection verification | [`src/infrastructure/database/init.ts`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/init.ts) |
| [`index.ts`](https://github.com/castrozan/tcc/blob/main/index.ts) | Application entry point that triggers `initializeDatabase()` | [`src/index.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/migrations.ts) every time the application starts.
- **Add new migrations** by extending the SQL in [`src/infrastructure/database/sqlite/migrations.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/src/infrastructure/database/init.ts). This typically happens in the main entry point ([`src/index.ts`](https://github.com/castrozan/tcc/blob/main/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.