Skip to main content

Database Migrations

Migrations provide version-controlled schema changes for your database. Every schema operation in Ductape automatically creates a migration, ensuring all changes are tracked, reproducible, and reversible across all environments.

Runtime defaults

When using ductape.databases from a Ductape instance, set product and env on the constructor and pass only database to connect(). Examples below use DatabaseService directly; pass product and env on connect() only if you are not using a Ductape client with constructor defaults.

Prefer the CLI for schema management

For most projects, ductape db schema generate and ductape db migrate are the recommended way to manage your database schema. Declare tables in ductape/database/schema.json, generate version-controlled migration files, and apply them to each environment with a single command. The programmatic API below is available for advanced use cases where you need migration logic embedded in application code.

Quick Example​

import { DatabaseService } from '@ductape/sdk';

const db = new DatabaseService();
await db.connect({ database: 'main-db' });

// Create table - automatically generates and applies a migration
const result = await db.schema.create('users', {
  id: { type: 'uuid', primaryKey: true },
  email: { type: 'string', length: 255, required: true, unique: true },
  name: { type: 'string', length: 100 },
}, { timestamps: true });

// The migration is tracked and can be replayed
console.log('Migration:', result.migration.tag);

How It Works​

Unlike traditional ORMs where you write migration files manually, Ductape uses a migration-first approach:

  1. Every schema change creates a migration - When you call db.schema.create(), db.schema.addField(), etc., a migration is automatically generated
  2. Migrations are applied immediately - By default, the migration runs on the connected environment
  3. Migrations are tracked - All applied migrations are stored in a migrations table
  4. Platform-independent - The same migration works across PostgreSQL, MySQL, MongoDB, DynamoDB, Cassandra, and MariaDB
Your Code                  Generated Migration           Database
    ↓                            ↓                           ↓
db.schema.create()  →  { type: 'createCollection' }  →  CREATE TABLE
db.schema.addField() →  { type: 'addField' }         →  ALTER TABLE ADD
db.schema.dropIndex() → { type: 'dropIndex' }        →  DROP INDEX

Why This Approach?​

  • No migration files to manage - Migrations are generated from your intent
  • Type-safe - Schema definitions are validated at compile time
  • Cross-database - Same API works for SQL and NoSQL databases
  • Automatic rollback - Every migration includes down operations
  • Environment-aware - Apply to dev, staging, or production

Schema Operations (Automatic Migrations)​

Create Table​

await db.schema.create('products', {
  id: { type: 'uuid', primaryKey: true },
  name: { type: 'string', length: 255, required: true },
  price: { type: 'decimal', precision: 10, scale: 2 },
  stock: 'integer',
  is_active: { type: 'boolean', default: true },
  metadata: 'json',
}, { timestamps: true });

// Generated migration:
// {
//   type: 'createCollection',
//   name: 'products',
//   fields: [...],
//   up: [...],
//   down: [{ type: 'dropCollection', name: 'products' }]
// }

Add Field​

await db.schema.addField('users', 'phone', {
  type: 'string',
  length: 20,
});

// Generated migration:
// {
//   type: 'addField',
//   collection: 'users',
//   field: { name: 'phone', type: 'string', length: 20 },
//   down: [{ type: 'dropField', collection: 'users', field: 'phone' }]
// }

Drop Field​

await db.schema.dropField('users', 'old_column');

Rename Field​

await db.schema.renameField('users', 'name', 'full_name');

Modify Field​

await db.schema.modifyField('users', 'email', {
  length: 500,
  required: true,
});

Index management​

Create Index​

await db.schema.createIndex('users', ['email'], {
  unique: true,
  name: 'idx_users_email',
});

Drop Index​

await db.schema.dropIndex('users', 'idx_users_old');

Add Constraint (SQL)​

await db.schema.addConstraint('posts', {
  name: 'fk_posts_author',
  type: 'foreignKey',
  columns: ['author_id'],
  references: {
    table: 'users',
    columns: ['id'],
    onDelete: 'CASCADE',
  },
});

Drop Table​

await db.schema.drop('old_table', { cascade: true });

Advanced: Migration Builder​

For complex scenarios where you need to batch multiple operations or have more control, use the MigrationBuilder:

import { MigrationBuilder, migration } from '@ductape/sdk';

// Fluent API for building migrations
const usersMigration = migration('create_users_with_indexes')
  .description('Create users table with all indexes')
  .createCollection('users', [
    { name: 'id', type: 'uuid', primaryKey: true },
    { name: 'email', type: 'string', length: 255, unique: true },
    { name: 'name', type: 'string', length: 100 },
    { name: 'status', type: 'enum', enumValues: ['active', 'inactive'] },
  ])
  .createIndex('users', 'idx_users_email', [{ name: 'email' }], { unique: true })
  .createIndex('users', 'idx_users_status', [{ name: 'status' }])
  .build();

// Apply the migration
const engine = new MigrationEngine(db.getAdapter());
await engine.up(usersMigration);

Batch Multiple Operations​

const orderSystemMigration = migration('create_order_system')
  .description('Create orders and order_items tables')
  .createCollection('orders', [
    { name: 'id', type: 'uuid', primaryKey: true },
    { name: 'customer_id', type: 'uuid', nullable: false },
    { name: 'status', type: 'string', length: 50 },
    { name: 'total', type: 'decimal', precision: 10, scale: 2 },
  ])
  .createCollection('order_items', [
    { name: 'id', type: 'uuid', primaryKey: true },
    { name: 'order_id', type: 'uuid', nullable: false },
    { name: 'product_id', type: 'uuid', nullable: false },
    { name: 'quantity', type: 'integer' },
    { name: 'unit_price', type: 'decimal', precision: 10, scale: 2 },
  ])
  .createIndex('orders', 'idx_orders_customer', [{ name: 'customer_id' }])
  .createIndex('order_items', 'idx_order_items_order', [{ name: 'order_id' }])
  .build();

Migration Engine​

The MigrationEngine handles executing migrations directly:

import { MigrationEngine } from '@ductape/sdk';

const engine = new MigrationEngine(db.getAdapter());

// Run migration up
await engine.up(migration);

// Run migration down (rollback)
await engine.down(migration);

// Get migration history
const history = await engine.getHistory();

// Check migration status
const status = await engine.getStatus({
  definedMigrations: [migration1, migration2, migration3],
});

console.log('Pending migrations:', status.pending);
console.log('Completed migrations:', status.completed);

Field Types​

Platform-independent field types that work across all databases:

TypeDescriptionSQLMongoDBDynamoDBCassandra
integerWhole numbersINTNumberNint
bigintLarge integersBIGINTLongNbigint
smallintSmall integersSMALLINTNumberNsmallint
floatFloating pointFLOATDoubleNfloat
doubleDouble precisionDOUBLEDoubleNdouble
decimalExact decimalDECIMALDecimal128Ndecimal
stringVariable textVARCHARStringStext
textLong textTEXTStringStext
booleanTrue/falseBOOLEANBooleanBOOLboolean
dateDate onlyDATEDateSdate
timeTime onlyTIMEStringStime
datetimeDate and timeDATETIMEDateStimestamp
timestampTimestampTIMESTAMPDateNtimestamp
jsonJSON dataJSON/JSONBObjectMtext
uuidUUIDUUIDStringSuuid
enumEnumerationENUMStringStext
arrayArray typeARRAYArrayLlist
binaryBinary dataBYTEABinDataBblob
blobLarge binaryBLOBBinDataBblob

Database-Specific Migrations​

SQL Databases (PostgreSQL, MySQL, MariaDB)​

await db.schema.create('users', {
  id: { type: 'uuid', primaryKey: true },
  email: { type: 'string', required: true },
}, {
  sqlOptions: {
    ifNotExists: true,
    temporary: false,
    unlogged: false,  // PostgreSQL only
  },
});

// Add constraint
await db.schema.addConstraint('orders', {
  name: 'fk_orders_user',
  type: 'foreignKey',
  columns: ['user_id'],
  references: {
    table: 'users',
    columns: ['id'],
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE',
  },
});

MongoDB​

await db.schema.create('users', {
  _id: { type: 'uuid', primaryKey: true },
  email: { type: 'string', required: true },
}, {
  mongoOptions: {
    capped: false,
    validator: {
      $jsonSchema: {
        bsonType: 'object',
        required: ['email'],
        properties: {
          email: { bsonType: 'string' }
        }
      }
    },
    validationLevel: 'strict',
    validationAction: 'error',
  },
});

// Shard a collection
// (handled via raw migration operations)

DynamoDB​

await db.schema.create('users', {
  id: { type: 'string', primaryKey: true },
  email: 'string',
  created_at: 'timestamp',
}, {
  dynamoOptions: {
    partitionKey: { name: 'id', type: 'S' },
    sortKey: { name: 'created_at', type: 'N' },
    billingMode: 'PAY_PER_REQUEST',
    globalSecondaryIndexes: [{
      name: 'email-index',
      partitionKey: { name: 'email', type: 'S' },
      projection: 'ALL',
    }],
    localSecondaryIndexes: [{
      name: 'created-index',
      sortKey: { name: 'created_at', type: 'N' },
      projection: 'KEYS_ONLY',
    }],
    streamEnabled: true,
    streamViewType: 'NEW_AND_OLD_IMAGES',
    ttlAttribute: 'expires_at',
  },
});

Cassandra​

await db.schema.create('events', {
  id: { type: 'uuid', primaryKey: true },
  user_id: 'uuid',
  event_type: 'string',
  data: 'json',
  created_at: 'timestamp',
}, {
  cassandraOptions: {
    partitionKey: ['user_id'],
    clusteringColumns: ['created_at', 'id'],
    clusteringOrder: [
      { column: 'created_at', order: 'DESC' },
      { column: 'id', order: 'ASC' },
    ],
    defaultTTL: 86400,
    compaction: {
      class: 'LeveledCompactionStrategy',
    },
  },
});

Migration History​

View Migration History​

const engine = new MigrationEngine(db.getAdapter());
const history = await engine.getHistory();

history.forEach((entry) => {
  console.log('Tag:', entry.tag);
  console.log('Name:', entry.name);
  console.log('Applied at:', entry.appliedAt);
  console.log('Checksum:', entry.checksum);
  console.log('---');
});

Check Migration Status​

const status = await engine.getStatus({
  definedMigrations: allMigrations,
});

console.log('Total:', status.total);
console.log('Completed:', status.completed);
console.log('Pending:', status.pending);
console.log('Last applied:', status.lastApplied?.name);

// List pending migrations
status.pendingMigrations.forEach((m) => {
  console.log('Pending:', m.name);
});

Rolling Back Migrations​

Rollback Last Migration​

const engine = new MigrationEngine(db.getAdapter());
await engine.down(lastMigration);

Rollback to Specific Point​

// Get history and find the target
const history = await engine.getHistory();
const targetIndex = history.findIndex(h => h.tag === 'target_migration_tag');

// Rollback each migration after the target
for (let i = history.length - 1; i > targetIndex; i--) {
  await engine.down(history[i].migration);
}

Raw Operations​

For database-specific operations not covered by the standard API:

const rawMigration = migration('custom_operation')
  .raw('postgresql', 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
  .raw('postgresql', 'CREATE INDEX CONCURRENTLY idx_users_email ON users(email)')
  .build();

Best Practices​

1. Keep Migrations Small​

Create separate migrations for each logical change:

// Good: Separate migrations
await db.schema.create('users', { ... });
await db.schema.create('posts', { ... });
await db.schema.addField('users', 'avatar_url', 'string');

// Avoid: One giant migration with everything

2. Test in Development First​

Always run migrations in development before production:

// Development
await db.connect({ database: 'main-db' });
await db.schema.create('new_table', { ... });

// After testing, apply to production
await db.connect({ database: 'main-db' });
await db.schema.create('new_table', { ... });

3. Use Descriptive Names​

migration('add_phone_and_address_to_users')
migration('create_order_fulfillment_tables')
migration('add_composite_index_on_orders')

4. Include Both Up and Down​

The simplified API automatically generates rollback operations, but verify they're correct for complex changes:

const result = await db.schema.addField('users', 'phone', 'string');

// Verify the down migration
console.log(result.migration.down);
// Should include: { type: 'dropField', collection: 'users', field: 'phone' }

5. Handle Data Migrations Separately​

For data migrations, use the query API:

// Schema migration
await db.schema.addField('users', 'full_name', 'string');

// Data migration (separate operation)
await db.update({
  table: 'users',
  data: {
    full_name: { $CONCAT: ['first_name', ' ', 'last_name'] }
  },
  where: { full_name: null },
});

// Clean up old columns
await db.schema.dropField('users', 'first_name');
await db.schema.dropField('users', 'last_name');

Migration Helpers​

The MigrationHelpers class provides utilities for common operations:

import { MigrationHelpers } from '@ductape/sdk';

// Generate a unique migration tag
const tag = MigrationHelpers.generateTag('create_users');
// "20240115_143052_create_users"

// Validate a migration
const isValid = MigrationHelpers.validate(migration);

// Calculate migration checksum
const checksum = MigrationHelpers.checksum(migration);

Troubleshooting​

Migration Already Applied​

try {
  await db.schema.create('users', { ... });
} catch (error) {
  if (error.message.includes('already exists')) {
    console.log('Migration already applied, skipping');
  }
}

Failed Migration​

If a migration fails partway through:

  1. Check the migration history to see what was applied
  2. Manually fix the database state if needed
  3. Mark the migration as failed or remove it from history
  4. Re-run the migration

Different Environments Out of Sync​

// Get status for each environment
for (const env of ['dev', 'staging', 'prd']) {
  await db.connect({ env, database: 'main-db' });
  const status = await engine.getStatus({ definedMigrations: allMigrations });
  console.log(`${env}: ${status.completed}/${status.total} migrations applied`);
}

Managing migrations with the CLI​

The CLI provides a file-based migration workflow that is version-control friendly and environment-safe. It is the recommended approach for team projects.

1. Declare your schema​

Add your tables to ductape/database/schema.json in your project:

[
  {
    "db": "main-db",
    "tables": {
      "users": {
        "id":        { "type": "String", "primaryKey": true, "autoGenerate": true },
        "email":     { "type": "String", "required": true, "unique": true },
        "name":      { "type": "String", "required": true },
        "createdAt": { "type": "Date", "default": "now" }
      }
    }
  }
]

2. Generate migration files​

ductape db schema generate

This diffs schema.json against any existing migration files and writes new ones to ductape/database/migrations/<db-tag>/. Nothing is applied to the database at this step.

3. Apply migrations​

# Apply to your linked environment (from ductape/config.json)
ductape db migrate

# Apply to a specific environment
ductape db migrate --env staging
ductape db migrate --env prd

The MigrationEngine tracks applied migrations in _ductape_migrations. Re-running is safe — already-applied migrations are skipped.

4. Check status and roll back​

ductape db migrate status

ductape db migrate rollback       # roll back the last migration
ductape db migrate rollback -n 3  # roll back the last 3

When to use the programmatic API instead​

The SDK's db.schema.* and MigrationBuilder API (documented above) is appropriate when:

  • You need to generate a migration at runtime based on dynamic application logic
  • You are building tooling or admin scripts that manage schema as part of a larger workflow
  • You need database-specific options (e.g., DynamoDB GSI configuration, Cassandra partition keys) that are not yet expressible in schema.json

For everything else — including all production deployments — prefer the CLI workflow so that migrations are committed to version control and applied explicitly per environment.

See CLI: Database runtime & migrations for the full command reference.

Next Steps​

See Also​