Step 10: Database Setup with Proper Migrations

Database migrations let you version control your database schema. Instead of manually running SQL commands, you create migration files that can be applied and rolled back consistently across environments. This means:

• Team collaboration: Everyone has the same database structure
• Deployment safety: Changes are applied in order, every time
• Rollback capability: Undo changes if something goes wrong
• History tracking: See when and what changed in your schema

Create a proper.json configuration file in your project root. For production, use MySQL or MariaDB:

{
  "database": "sql",
  "sql": {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "your_password",
    "database": "myapp"
  },
  "migration_folder": "migrations",
  "migration_table": "proper_migrations"
}

Let's break down each property:

• database: The database type (sql for MySQL/MariaDB, or sqlite)
• sql: MySQL/MariaDB connection settings
• migration_folder: Where migration files are stored
• migration_table: Table name used to track applied migrations

Create your database before running migrations:

mysql -u root -p -e 'CREATE DATABASE myapp;'

SQLite is perfect for testing because it requires no server setup and supports in-memory databases that reset between test runs. Use a separate config for tests:

{
  "database": "sqlite",
  "sqlite": { "database": ":memory:" },
  "migration_folder": "migrations",
  "migration_table": "proper_migrations"
}

Key benefits of SQLite for testing:

• In-memory databases: Use :memory: for databases that exist only during the test
• No server required: Tests run anywhere without MySQL installation
• Fast isolation: Each test can get a fresh database instantly
• Same migrations: Your migration files work on both MySQL and SQLite

For file-based SQLite (persists between runs):

{
  "database": "sqlite",
  "sqlite": { "database": "database/test.sqlite" },
  "migration_folder": "migrations",
  "migration_table": "proper_migrations"
}

Use the Proper CLI to create a new migration for our todos table:

npx proper create create_todos_table

This creates two files in the migrations/ directory:

• 1700000000000_create_todos_table.up.sql - Applied when migrating up
• 1700000000000_create_todos_table.down.sql - Applied when rolling back

The "up" migration creates our table. Edit the file to define the todos table:

CREATE TABLE todos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    completed TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Notes about this schema:

• id: Auto-incrementing primary key
• title: Required VARCHAR field for the todo title
• description: Optional TEXT field for details
• completed: TINYINT for boolean (0 = false, 1 = true)
• created_at: Timestamp with automatic default
• updated_at: Auto-updates on row modification

SQLite Variant (for testing)

If you need SQLite-specific syntax, create a variant file:

CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    completed INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

Proper automatically uses .sqlite.sql files when running against SQLite.

The "down" migration reverses what "up" did. It should cleanly remove everything:

DROP TABLE IF EXISTS todos;

Always use IF EXISTS to avoid errors if the table was already dropped.

Apply all pending migrations with:

npx proper up

You should see output like:

Applying: 20240101120000-create_todos_table
✓ Migration complete

Check which migrations have been applied:

npx proper status

This shows all migrations and their status (applied or pending).

If you need to undo the last migration:

npx proper down

This runs the "down" file for the most recently applied migration. You can run it multiple times to roll back multiple migrations.

• Always write both up and down: This ensures you can rollback safely
• Keep migrations atomic: Each migration should do one logical thing
• Never edit applied migrations: Create a new migration instead
• Test rollbacks: Run up then down to verify both work
• Use descriptive names: create_todos_table is better than migration_1

As your application grows, create additional migrations:

npx proper create add_users_table
npx proper create add_user_id_to_todos

Each migration builds on the previous state, creating a clear history of your database evolution.

Seeds populate your database with initial data like reference tables, test users, or default settings. Seeds are TypeScript modules that can load data from JSON files, giving you full control over the seeding process.

Configuring Seeds

Add a seeds section to your proper.json:

{
  "database": "sql",
  "sql": { ... },
  "migration_folder": "migrations",
  "seeds": {
    "migrationsDir": "database/seed/migrations",
    "dataDir": "database/seed/data",
    "list": ["categories", "default_settings", "test_users"]
  }
}

• migrationsDir: Where seed TypeScript files live (.ts or .js)
• dataDir: Where JSON data files are stored
• list: Default seeds to run when no names are provided

Creating a Seed

Each seed has a TypeScript file and an optional JSON data file with matching names:

1. Create the data file:

[
  { "name": "Work", "slug": "work" },
  { "name": "Personal", "slug": "personal" },
  { "name": "Shopping", "slug": "shopping" }
]

2. Create the seed module with up and down functions:

import type { IMigrationRunner, SeedContext } from "@noego/proper";

export async function up(runner: IMigrationRunner, ctx: SeedContext) {
  const categories = ctx.data as any[];

  for (const cat of categories) {
    await runner.query(
      "INSERT INTO categories (name, slug) VALUES (?, ?)",
      [cat.name, cat.slug]
    );
    ctx.log?.(`Created category: ${cat.name}`);
  }
}

export async function down(runner: IMigrationRunner, ctx: SeedContext) {
  const categories = ctx.data as any[];
  for (const cat of categories) {
    await runner.query("DELETE FROM categories WHERE slug = ?", [cat.slug]);
  }
}

Seed Context

The ctx parameter provides:

• ctx.data — The loaded JSON data (from dataDir/seedName.json)
• ctx.dialect — The database dialect ("sql", "sqlite")
• ctx.log — A logging function for progress messages

Running Seeds

# Run all default seeds from config
npx proper seed

# Run specific seeds
npx proper seed categories test_users

# Rollback seeds
npx proper seed --action down test_users

# Transactional modes
npx proper seed --transactional seed    # Each seed in its own transaction
npx proper seed --transactional runner  # All seeds in one transaction

Programmatic Seeding (for tests)

Run seeds from your test setup:

import { MigrationRunnerFactory, runSeedsWithRunner, loadMigrationConfig } from "@noego/proper";

const config = loadMigrationConfig("proper.json");
const runner = await MigrationRunnerFactory.create("proper.json");

// Seed the database
await runSeedsWithRunner(runner, config, 'up', {
  names: ['categories', 'test_users']
});

// After tests, clean up
await runSeedsWithRunner(runner, config, 'down', {
  names: ['test_users', 'categories']  // Reverse order
});

Now that we have a database, let's create a repository layer to interact with it. We'll use SQLStack to build type-safe database operations with co-located SQL queries.