How to Configure and Initialize the SQLite Database in the Castrozan TCC Project

The TCC repository configures its SQLite database through a singleton client that creates a local file at ./data/database.sqlite, runs DDL migrations on startup, and registers graceful shutdown hooks to close connections when the process exits.

The castrozan/tcc monorepo includes two dummy applications—professionals-dummy-app and equipments-dummy-app—that share a lightweight SQLite persistence layer built on better-sqlite3. To configure and initialize the SQLite database, the codebase implements a three-stage bootstrap process that handles connection management, schema migration, and lifecycle cleanup. All database-related code resides under src/infrastructure/database within each application workspace.

Step 1: Configure the SQLite Client Singleton

The SQLite client (sqlite-client.ts) creates a singleton Database instance using better-sqlite3, ensuring only one connection handle exists throughout the application lifecycle. It automatically creates the data directory if missing, stores the file as database.sqlite, and enables foreign-key support via PRAGMA.

// professionals-dummy-app/src/infrastructure/database/sqlite/sqlite-client.ts
import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

let db: Database.Database | null = null;

export function getDatabase(): Database.Database {
  if (!db) {
    const dataDir = path.resolve(process.cwd(), 'data');
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }

    const dbPath = path.resolve(dataDir, 'database.sqlite');
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  return db;
}

export function closeDatabase(): void {
  if (db) {
    db.close();
    db = null;
  }
}

The getDatabase() function lazily initializes the connection on first call, while closeDatabase() provides a clean way to release the file handle during shutdown.

Step 2: Initialize Database Schema with Migrations

The migrations module (migrations.ts) contains the DDL scripts required to build the initial schema. It uses CREATE TABLE IF NOT EXISTS statements to ensure idempotent execution, allowing the application to restart without errors.

// professionals-dummy-app/src/infrastructure/database/sqlite/migrations.ts
import { getDatabase } from './sqlite-client';

export function initializeDatabase(): void {
  const db = getDatabase();
  db.exec(`
    CREATE TABLE IF NOT EXISTS Professional (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      role TEXT NOT NULL,
      bio TEXT,
      imageUrl TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      hierarchy INTEGER
    );
  `);
}

This pattern is duplicated in the equipments dummy app, where the migration defines an Equipment table instead of Professional.

Step 3: Bootstrap the Application Database

The database initializer (init.ts) orchestrates the startup sequence by invoking migrations and verifying connectivity with a health-check query. It also exports cleanupDatabase() for graceful termination.

// professionals-dummy-app/src/infrastructure/database/init.ts
import { initializeDatabase as initializeSQLite } from './sqlite/migrations';
import { closeDatabase, getDatabase } from './sqlite/sqlite-client';

export async function initializeDatabase(): Promise<void> {
  try {
    initializeSQLite();

    const db = getDatabase();
    const result = db.prepare('SELECT 1 AS test').get() as { test: number };
    if (result && result.test === 1) {
      console.log('Database connection successful');
    }
  } catch (error) {
    console.error('SQLite initialization failed:', error);
    throw error;
  }
}

export function cleanupDatabase(): void {
  closeDatabase();
  console.log('Database connection closed');
}

The application entry point (index.ts) calls initializeDatabase() before starting the HTTP server and attaches signal handlers for SIGINT and SIGTERM to invoke cleanupDatabase():

// professionals-dummy-app/src/index.ts
import { cleanupDatabase, initializeDatabase } from './infrastructure/database/init';

initializeDatabase().catch((error) => {
  console.error('Failed to initialize database:', error);
  process.exit(1);
});

process.on('SIGINT', () => {
  console.log('Application shutting down...');
  cleanupDatabase();
  process.exit(0);
});
process.on('SIGTERM', () => {
  console.log('Application shutting down...');
  cleanupDatabase();
  process.exit(0);
});

Working with the Database in Practice

Adding New Tables via Migration

To extend the schema, modify migrations.ts to include additional CREATE TABLE statements:

// src/infrastructure/database/sqlite/migrations.ts
export function initializeDatabase(): void {
  const db = getDatabase();
  db.exec(`
    CREATE TABLE IF NOT EXISTS Equipment (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      type TEXT,
      location TEXT,
      createdAt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    );
  `);
}

Querying Data in Repositories

Repositories access the same singleton instance via getDatabase() to execute prepared statements:

// src/infrastructure/database/repositories/SQLiteEquipmentRepository.ts
import { getDatabase } from '../sqlite/sqlite-client';

export class SQLiteEquipmentRepository {
  async findAll() {
    const db = getDatabase();
    return db.prepare('SELECT * FROM Equipment').all();
  }

  async create(equipment: { name: string; type?: string; location?: string }) {
    const db = getDatabase();
    const stmt = db.prepare(`
      INSERT INTO Equipment (name, type, location) VALUES (?, ?, ?)
    `);
    const info = stmt.run(equipment.name, equipment.type, equipment.location);
    return info.lastInsertRowid;
  }
}

Running the Application Locally

Execute the following commands to install dependencies and start the application, which automatically creates ./data/database.sqlite on first run:

npm install
npm run dev --workspace=professionals-dummy-app

Summary

• The SQLite singleton client in sqlite-client.ts manages the better-sqlite3 connection, stores data in ./data/database.sqlite, and enables foreign-key constraints.
• Migrations in migrations.ts execute idempotent DDL to create tables such as Professional or Equipment during startup.
• The bootstrap module (init.ts) verifies connectivity and exports cleanupDatabase() for graceful shutdown.
• The application entry point registers SIGINT and SIGTERM handlers to ensure the database connection closes cleanly when the process terminates.

Frequently Asked Questions

Where is the SQLite database file stored?

The database file is stored at ./data/database.sqlite relative to the process working directory. The sqlite-client.ts module automatically creates the data directory if it does not exist, ensuring the file path is valid before initializing the connection.

How does the application handle database connections on shutdown?

The init.ts module exports cleanupDatabase(), which invokes closeDatabase() from the client module to release the file handle. This function is registered as a handler for both SIGINT and SIGTERM signals in index.ts, ensuring connections close gracefully during server restarts or container shutdowns.

Can I use the same configuration for the equipments dummy app?

Yes. The equipments-dummy-app contains identical files under src/infrastructure/database with the same logic. You can apply the same configuration steps to either workspace, adjusting only the table names in migrations.ts to match the domain entities.

How do I enable foreign key constraints?

Foreign key support is enabled automatically when the database connection is initialized. The getDatabase() function executes db.pragma('foreign_keys = ON') immediately after creating the better-sqlite3 instance, ensuring referential integrity checks are active for all subsequent queries.

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