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

> Configure and initialize the SQLite database in the Castrozan TCC project. Learn how to set up a singleton client, run DDL migrations, and implement shutdown hooks for seamless database management.

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

---

**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`](https://github.com/castrozan/tcc/blob/main/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`.

```typescript
// 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`](https://github.com/castrozan/tcc/blob/main/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.

```typescript
// 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`](https://github.com/castrozan/tcc/blob/main/init.ts)) orchestrates the startup sequence by invoking migrations and verifying connectivity with a health-check query. It also exports `cleanupDatabase()` for graceful termination.

```typescript
// 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`](https://github.com/castrozan/tcc/blob/main/index.ts)) calls `initializeDatabase()` before starting the HTTP server and attaches signal handlers for `SIGINT` and `SIGTERM` to invoke `cleanupDatabase()`:

```typescript
// 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`](https://github.com/castrozan/tcc/blob/main/migrations.ts) to include additional `CREATE TABLE` statements:

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

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

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

```

## Summary

- The **SQLite singleton client** in [`sqlite-client.ts`](https://github.com/castrozan/tcc/blob/main/sqlite-client.ts) manages the `better-sqlite3` connection, stores data in `./data/database.sqlite`, and enables foreign-key constraints.
- **Migrations** in [`migrations.ts`](https://github.com/castrozan/tcc/blob/main/migrations.ts) execute idempotent DDL to create tables such as `Professional` or `Equipment` during startup.
- The **bootstrap module** ([`init.ts`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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`](https://github.com/castrozan/tcc/blob/main/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.