database

database

This document describes the database module, which serves as the central repository for managing database schema migrations within the project.

Overview

The database module, as currently defined, is a data-only module. It does not contain any executable code (functions, classes, or scripts) for database interaction or migration application. Instead, its sole purpose is to house the declarative definitions of database schema changes, known as migrations.

These migration definitions are stored in a structured JSON format, allowing an external migration runner or ORM tool to interpret and apply schema updates in a controlled and versioned manner.

Key Components

The database module primarily consists of the migrations subdirectory.

migrations Directory

This directory contains individual migration definition files. Each file describes a specific versioned change to the database schema, including both the "up" (application) and "down" (reversal) SQL statements.

Example File: database/migrations/0006_test_migration.json

{
  "version": 6,
  "name": "test_migration",
  "up": "-- Add your UP migration SQL here",
  "down": "-- Add your DOWN migration SQL here"
}

Migration File Structure

Each migration file is a JSON object with the following required fields:

• version (Integer): A unique, monotonically increasing integer representing the migration's version number. This is crucial for ordering migrations.
• name (String): A human-readable name for the migration, typically reflecting the change it introduces (e.g., "add_users_table", "alter_email_column").
• up (String): The SQL statements to execute when applying this migration (e.g., creating tables, adding columns, altering constraints).
• down (String): The SQL statements to execute when reverting this migration (e.g., dropping tables, removing columns, reversing alterations). This should ideally undo the changes made by the up script.

How Migrations Are Used

It is important to note that the database module itself does not contain the logic to apply these migrations. The migration JSON files are consumed by an external migration runner (not part of this module's codebase). This external tool is responsible for:

1. Scanning the database/migrations directory.
2. Reading and parsing the JSON migration files.
3. Determining which migrations need to be applied based on the current database schema version.
4. Executing the up or down SQL statements against the target database.

This separation of concerns means the database module acts purely as a data source for schema evolution instructions.

Contributing to Migrations

To add a new database migration:

1. Create a new JSON file in the database/migrations directory.

• The filename should typically follow a convention that includes the version number (e.g., 0007_add_products_table.json).

1. Increment the version number: Ensure the version field in your new JSON file is the next sequential integer after the highest existing migration version.
2. Provide a descriptive name: Choose a name that clearly indicates the purpose of the migration.
3. Write the up SQL: Add the SQL statements required to apply your schema change.
4. Write the down SQL: Add the SQL statements required to revert your schema change. This is crucial for rollback capabilities.

Example of adding a new migration:

If the latest migration is 0006_test_migration.json with version: 6, your new migration would be 0007_add_users_table.json:

// database/migrations/0007_add_users_table.json
{
  "version": 7,
  "name": "add_users_table",
  "up": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL);",
  "down": "DROP TABLE users;"
}

Connection to the Rest of the Codebase

As a data-only module, the database module does not have direct outgoing or incoming calls to other parts of the codebase. Its connection is indirect: it provides the necessary schema evolution definitions that are consumed by an external migration management system, which then interacts with the actual database. Developers working on features requiring schema changes will interact with this module by adding new migration files.