Migrations and Entities - the Roman legion archive

Young administrator! Consul Caesar.js has a new challenge for you. Now that you can create basic tribute maps (entities), it's time to learn how to safely update the structure of your treasury without risking the loss of valuable data!

What are migrations in the Roman world?

Imagine that your legion has been collecting tributes in an old chest for years. One day it turns out you need a bigger chest with additional compartments. You can't just move everything haphazardly - you need to do it carefully, step by step.

Migrations are exactly such a reorganization plan for the treasury:

• They document every change in the database structure
• They can be reverted if something goes wrong
• They are executed automatically across all forts and legion garrisons
• They ensure consistency between different environments

Generating your first migration

TypeORM CLI configuration

1# CLI installation
2npm install -g @nestjs/cli
3npm install --save-dev ts-node

1// package.json - migration scripts
2{
3 "scripts": {
4 "migration:generate": "typeorm-ts-node-commonjs migration:generate",
5 "migration:run": "typeorm-ts-node-commonjs migration:run",
6 "migration:revert": "typeorm-ts-node-commonjs migration:revert"
7 }
8}

Data source configuration

1// data-source.ts
2import { DataSource } from 'typeorm';
3import { Tribute } from './src/tribute/tribute.entity';
4import { Legionary } from './src/legionary/legionary.entity';
5
6export const AppDataSource = new DataSource({
7 type: 'postgres',
8 host: 'localhost',
9 port: 5432,
10 username: 'consul',
11 password: 'imperium123',
12 database: 'roman_empire',
13 entities: [Tribute, Legionary],
14 migrations: ['src/migrations/*.ts'],
15 synchronize: false, // IMPORTANT: false in production!
16});

Creating an advanced Legionary entity

1// legionary.entity.ts
2import {
3 Entity,
4 PrimaryGeneratedColumn,
5 Column,
6 CreateDateColumn,
7 UpdateDateColumn,
8 Index,
9} from 'typeorm';
10
11@Entity('legionaries')
12@Index(['name', 'cohort']) // Index for faster searching
13export class Legionary {
14 @PrimaryGeneratedColumn()
15 id: number;
16
17 @Column({ length: 100, unique: true })
18 @Index() // Index on name
19 name: string;
20
21 @Column({ length: 100, nullable: true })
22 cohort: string;
23
24 @Column({ type: 'int', default: 0 })
25 tributeCount: number;
26
27 @Column({ type: 'decimal', precision: 10, scale: 2, default: 0 })
28 totalTributeValue: number;
29
30 @Column({ length: 50 })
31 rank: string;
32
33 @Column({ type: 'text', nullable: true })
34 biography: string;
35
36 @Column({ type: 'json', nullable: true })
37 skills: {
38 navigation: number;
39 combat: number;
40 negotiation: number;
41 leadership: number;
42 };
43
44 @Column({ default: true })
45 isActive: boolean;
46
47 @CreateDateColumn()
48 joinedAt: Date;
49
50 @UpdateDateColumn()
51 lastUpdated: Date;
52
53 @Column({ type: 'date', nullable: true })
54 lastSeenAt: Date;
55}

Generating a migration after entity changes

1# Generate migration based on entity changes
2npm run migration:generate -- src/migrations/AddLegionaryEntity

TypeORM will automatically generate a migration file:

1// src/migrations/1234567890123-AddLegionaryEntity.ts
2import { MigrationInterface, QueryRunner } from 'typeorm';
3
4export class AddLegionaryEntity1234567890123 implements MigrationInterface {
5 name = 'AddLegionaryEntity1234567890123';
6
7 public async up(queryRunner: QueryRunner): Promise<void> {
8 await queryRunner.query(`
9 CREATE TABLE "legionaries" (
10 "id" SERIAL NOT NULL,
11 "name" character varying(100) NOT NULL,
12 "cohort" character varying(100),
13 "tributeCount" integer NOT NULL DEFAULT '0',
14 "totalTributeValue" numeric(10,2) NOT NULL DEFAULT '0',
15 "rank" character varying(50) NOT NULL,
16 "biography" text,
17 "skills" json,
18 "isActive" boolean NOT NULL DEFAULT true,
19 "joinedAt" TIMESTAMP NOT NULL DEFAULT now(),
20 "lastUpdated" TIMESTAMP NOT NULL DEFAULT now(),
21 "lastSeenAt" date,
22 CONSTRAINT "UQ_legionaries_name" UNIQUE ("name"),
23 CONSTRAINT "PK_legionaries_id" PRIMARY KEY ("id")
24 )
25 `);
26
27 await queryRunner.query(`
28 CREATE INDEX "IDX_legionaries_name" ON "legionaries" ("name")
29 `);
30
31 await queryRunner.query(`
32 CREATE INDEX "IDX_legionaries_name_cohort" ON "legionaries" ("name", "cohort")
33 `);
34 }
35
36 public async down(queryRunner: QueryRunner): Promise<void> {
37 await queryRunner.query(`DROP INDEX "IDX_legionaries_name_cohort"`);
38 await queryRunner.query(`DROP INDEX "IDX_legionaries_name"`);
39 await queryRunner.query(`DROP TABLE "legionaries"`);
40 }
41}

Running migrations

1# Execute all pending migrations
2npm run migration:run
3
4# Revert the last migration (when something goes wrong)
5npm run migration:revert

Manually creating migrations

Sometimes you need to create migrations manually, e.g., for data seeding:

1// src/migrations/1234567890124-SeedInitialLegionaries.ts
2import { MigrationInterface, QueryRunner } from 'typeorm';
3
4export class SeedInitialLegionaries1234567890124 implements MigrationInterface {
5 public async up(queryRunner: QueryRunner): Promise<void> {
6 await queryRunner.query(`
7 INSERT INTO "legionaries" ("name", "cohort", "rank", "tributeCount", "totalTributeValue", "skills") VALUES
8 ('Marcus Aurelius', 'Legio X Equestris', 'Centurion', 150, 50000.00, '{"navigation": 95, "combat": 90, "negotiation": 70, "leadership": 95}'),
9 ('Julia Domna', 'Legio XII Fulminata', 'Optio', 45, 12000.00, '{"navigation": 70, "combat": 95, "negotiation": 80, "leadership": 75}'),
10 ('Lucius Verus', 'Legio III Gallica', 'Centurion', 60, 18000.00, '{"navigation": 80, "combat": 75, "negotiation": 85, "leadership": 70}'),
11 ('Hadrian', 'Legio XII Fulminata', 'Speculator', 35, 8500.00, '{"navigation": 90, "combat": 85, "negotiation": 60, "leadership": 65}')
12 `);
13 }
14
15 public async down(queryRunner: QueryRunner): Promise<void> {
16 await queryRunner.query(`
17 DELETE FROM "legionaries" WHERE "name" IN ('Marcus Aurelius', 'Julia Domna', 'Lucius Verus', 'Hadrian')
18 `);
19 }
20}

Advanced column types

1@Entity('detailed_legionaries')
2export class DetailedLegionary {
3 @PrimaryGeneratedColumn('uuid') // UUID instead of auto-increment
4 id: string;
5
6 @Column({ type: 'enum', enum: ['Centurion', 'Optio', 'Speculator', 'Tesserarius', 'Coquus'] })
7 rank: string;
8
9 @Column({ type: 'simple-array' }) // String array
10 languages: string[];
11
12 @Column({ type: 'simple-json' }) // Simple JSON
13 equipment: { weapon: string; armor: string; accessories: string[] };
14
15 @Column({ type: 'point' }) // PostgreSQL geometric type
16 lastKnownLocation: string;
17
18 @Column({ type: 'interval' }) // Time interval
19 timeSinceLastVoyage: string;
20
21 @Column({ type: 'money' }) // PostgreSQL money type
22 bounty: string;
23}

Database-level validation

1@Entity('validated_legionaries')
2@Check('"age" >= 16 AND "age" <= 100') // Age validation
3@Check('"tributeCount" >= 0') // Cannot be negative
4export class ValidatedLegionary {
5 @PrimaryGeneratedColumn()
6 id: number;
7
8 @Column({ length: 100 })
9 @Index({ unique: true }) // Unique index
10 name: string;
11
12 @Column({ type: 'int' })
13 age: number;
14
15 @Column({ type: 'int', default: 0 })
16 tributeCount: number;
17
18 @Column({ type: 'varchar', length: 100 })
19 @Index() // Regular index for faster searching
20 homePort: string;
21}

Structure change migration

Example of adding a new column to an existing table:

1// Adding the 'reputation' field to the legionaries table
2@Column({ type: 'int', default: 50 })
3reputation: number;

1npm run migration:generate -- src/migrations/AddReputationToLegionarys

Generated migration:

1export class AddReputationToLegionarys1234567890125 implements MigrationInterface {
2 public async up(queryRunner: QueryRunner): Promise<void> {
3 await queryRunner.query(`
4 ALTER TABLE "legionaries"
5 ADD "reputation" integer NOT NULL DEFAULT '50'
6 `);
7 }
8
9 public async down(queryRunner: QueryRunner): Promise<void> {
10 await queryRunner.query(`
11 ALTER TABLE "legionaries"
12 DROP COLUMN "reputation"
13 `);
14 }
15}

Migration performance in production

For large tables, migrations can be slow. Here are some tips:

1export class OptimizedMigration implements MigrationInterface {
2 public async up(queryRunner: QueryRunner): Promise<void> {
3 // 1. Add column without index
4 await queryRunner.query(`
5 ALTER TABLE "legionaries"
6 ADD "email" varchar(255)
7 `);
8
9 // 2. Fill data in smaller batches
10 await queryRunner.query(`
11 UPDATE "legionaries"
12 SET "email" = "name" || '@legionary.legion'
13 WHERE "id" BETWEEN 1 AND 1000
14 `);
15
16 // 3. Add constraints after filling data
17 await queryRunner.query(`
18 ALTER TABLE "legionaries"
19 ALTER COLUMN "email" SET NOT NULL
20 `);
21
22 // 4. Add index at the end
23 await queryRunner.query(`
24 CREATE UNIQUE INDEX CONCURRENTLY "IDX_legionaries_email"
25 ON "legionaries" ("email")
26 `);
27 }
28}

Migration best practices

1. Always test migrations

1# Test on a database copy
2npm run migration:run
3npm run migration:revert
4npm run migration:run

2. Avoid removing columns immediately

1// First release: mark as deprecated
2@Column({ nullable: true, comment: 'DEPRECATED: will be removed in v2.0' })
3oldField: string;
4
5// Second release: remove
6// @Column() oldField: string; // commented out

3. Use transactions

1public async up(queryRunner: QueryRunner): Promise<void> {
2 await queryRunner.startTransaction();
3 try {
4 await queryRunner.query('...');
5 await queryRunner.query('...');
6 await queryRunner.commitTransaction();
7 } catch (error) {
8 await queryRunner.rollbackTransaction();
9 throw error;
10 }
11}

Useful CLI commands

1# Creating an empty migration
2npm run migration:create -- src/migrations/CustomMigration
3
4# Showing migration status
5npm run migration:show
6
7# Reverting a specific number of migrations
8npm run migration:revert -- --fake

Practical exercise

Try creating your own entity and migrations:

1. Create a

   Legion

   entity with fields: name, type, capacity, consul
2. Generate migrations
3. Add a new

   foundedYear

   field to Legion
4. Generate and run the update migration

1// Your code here
2@Entity('legions')
3export class Legion {
4 // Implement the entity according to the guidelines
5}

Summary

Now you're a true Roman archivist! You can:

✓ Generate migrations automatically from entities ✓ Create manual migrations for special tasks ✓ Use advanced column types in PostgreSQL ✓ Add indexes and validations at the database level ✓ Safely update the structure in production

Consul Caesar.js can now sleep soundly - his treasury is in good hands!