9546514333

Committed 17 Jun 2024 11:01AM UTC coverage: 49.473% (-42.8%) from 92.296%

Build # 9546514333

Build Type

push

github

Committed by

web-flow

Commit Message

Add `PostgresDatabaseMigrator` for (testing) migration (#1655)

Adds a new `PostgresDatabaseMigrator` that has the core logic of `postgres-shift`, as well as a testing method. The `migrate` method mirrors `postgres-shift` and the `test` method reuses part of it, executing each migration in a separate transaction. It allows us to interact with the database _before_ and _after_ a migration has executed, stopping after the desired migration:

- Create `PostgresDatabaseMigrator` and inject it
- Remove `postgres-shift` and associated definition/patch, replacing usage with the above
- Add appropriate test coverage

Run Details

394 of 2367 branches covered (16.65%)

Branch coverage included in aggregate %.

10 of 62 new or added lines in 3 files covered. (16.13%)

2191 existing lines in 221 files now uncovered.

3969 of 6452 relevant lines covered (61.52%)

12.49 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

8.6

/src/datasources/db/postgres-database.migrator.ts

import { Inject, Injectable } from '@nestjs/common';
import fs from 'node:fs';
import { join } from 'node:path';
import type { Sql, TransactionSql } from 'postgres';

type Migration = {
  path: string;
  id: number;
  name: string;
};

/**
 * Migrates a Postgres database using SQL and JavaScript files.
 *
 * Migrations should be in a directory, prefixed with a 5-digit number,
 * and contain either an `index.sql` or `index.js` file.
 *
 * This is heavily inspired by `postgres-shift`
 * @see https://github.com/porsager/postgres-shift/blob/master/index.js
 */
@Injectable()
export class PostgresDatabaseMigrator {
  private static readonly MIGRATIONS_FOLDER = join(process.cwd(), 'migrations');
  private static readonly SQL_MIGRATION_FILE = 'index.sql';
  private static readonly JS_MIGRATION_FILE = 'index.js';
  private static readonly MIGRATIONS_TABLE = 'migrations';

  constructor(@Inject('DB_INSTANCE') private readonly sql: Sql) {}

  /**
   * Runs/records migrations not present in the {@link PostgresMigrator.MIGRATIONS_TABLE} table.
   *
   * Note: all migrations are run in a single transaction for optimal performance.
   */
  async migrate(
    path = PostgresDatabaseMigrator.MIGRATIONS_FOLDER,
  ): Promise<void> {
    const migrations = this.getMigrations(path);

    await this.assertMigrationsTable();

    const last = await this.getLastRunMigration();
    const remaining = migrations.slice(last?.id ?? 0);

    await this.sql.begin(async (transaction: TransactionSql) => {
      for (const current of remaining) {
        await this.run({ transaction, migration: current });
        await this.setLastRunMigration({ transaction, migration: current });
      }
    });
  }

  /**
   * @private migrates up to/allows for querying before/after migration to test it.
   *
   * Note: each migration is ran in separate transaction to allow queries in between.
   *
   * @param args.migration - migration to test
   * @param args.folder - folder to search for migrations
   * @param args.before - function to run before each migration
   * @param args.after - function to run after each migration
   *
   * @example
   * ```typescript
   * const result = await migrator.test({
   *   migration: '00001_initial',
   *   before: (sql) => sql`SELECT * FROM <table_name>`,
   *   after: (sql) => sql`SELECT * FROM <table_name>`,
   * });
   *
   * expect(result.before).toBeUndefined();
   * expect(result.after).toStrictEqual(expected);
   * ```
   */
  async test(args: {
    migration: string;
    before?: (sql: Sql) => Promise<unknown>;
    after: (sql: Sql) => Promise<unknown>;
    folder?: string;
  }): Promise<{
    before: unknown;
    after: unknown;
  }> {
    const migrations = this.getMigrations(
      args.folder ?? PostgresDatabaseMigrator.MIGRATIONS_FOLDER,
    );

    // Find index of migration to test
    const migrationIndex = migrations.findIndex((migration) => {
      return migration.path.includes(args.migration);
    });

    if (migrationIndex === -1) {
      throw new Error(`Migration ${args.migration} not found`);
    }

    // Get migrations up to the specified migration
    const migrationsToTest = migrations.slice(0, migrationIndex + 1);

    let before: unknown;

    for await (const migration of migrationsToTest) {
      const isMigrationBeingTested = migration.path.includes(args.migration);

      if (isMigrationBeingTested && args.before) {
        before = await args.before(this.sql).catch(() => undefined);
      }

      await this.sql.begin((transaction) => {
        return this.run({ transaction, migration });
      });
    }

    const after = await args.after(this.sql).catch(() => undefined);

    return { before, after };
  }

  /**
   * Retrieves all migrations found at the specified path.
   *
   * @param path - path to search for migrations
   *
   * @returns array of {@link Migration}
   */
  private getMigrations(path: string): Array<Migration> {
    const migrations = fs
      .readdirSync(path)
      .filter((file) => {
        const isDirectory = fs.statSync(join(path, file)).isDirectory();
        const isMigration = file.match(/^[0-9]{5}_/);
        return isDirectory && isMigration;
      })
      .sort()
      .map((file) => {
        return {
          path: join(path, file),
          id: parseInt(file.slice(0, 5)),
          name: file.slice(6),
        };
      });

    if (migrations.length === 0) {
      throw new Error('No migrations found');
    }

    const latest = migrations.at(-1);
    if (latest?.id !== migrations.length) {
      throw new Error('Migrations numbered inconsistency');
    }

    return migrations;
  }

  /**
   * Adds specified migration to the transaction if supported.
   *
   * @param args.transaction - {@link TransactionSql} to migration within
   * @param args.migration - {@link Migration} to add
   */
  private async run(args: {
    transaction: TransactionSql;
    migration: Migration;
  }): Promise<void> {
    const isSql = fs.existsSync(
      join(args.migration.path, PostgresDatabaseMigrator.SQL_MIGRATION_FILE),
    );
    const isJs = fs.existsSync(
      join(args.migration.path, PostgresDatabaseMigrator.JS_MIGRATION_FILE),
    );

    if (isSql) {
      await args.transaction.file(
        join(args.migration.path, PostgresDatabaseMigrator.SQL_MIGRATION_FILE),
      );
    } else if (isJs) {
      const file = (await import(
        join(args.migration.path, PostgresDatabaseMigrator.JS_MIGRATION_FILE)
      )) as {
        default: (transaction: TransactionSql) => Promise<void>;
      };
      await file.default(args.transaction);
    } else {
      throw new Error(`No migration file found for ${args.migration.path}`);
    }
  }
  /**
   * Creates the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table if it does not exist.
   */
  private async assertMigrationsTable(): Promise<void> {
    try {
      await this.sql`SELECT
                        '${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)}'::regclass`;
    } catch {
      await this.sql`CREATE TABLE
                        ${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)} (
                            id SERIAL PRIMARY KEY,
                            created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
                            name TEXT
                        )`;
    }
  }

  /**
   * Retrieves the last run migration from the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table.
   *
   * @returns last run {@link Migration}
   */
  private async getLastRunMigration(): Promise<Migration> {
    const [last] = await this.sql<Array<Migration>>`SELECT
                                                        id
                                                    FROM
                                                        ${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)}
                                                    ORDER BY
                                                        id DESC
                                                    LIMIT
                                                        1`;

    return last;
  }

  /**
   * Adds the last run migration to the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table.
   *
   * @param args.transaction - {@link TransactionSql} to set within
   * @param args.migration - {@link Migration} to set
   */
  private async setLastRunMigration(args: {
    transaction: TransactionSql;
    migration: Migration;
  }): Promise<void> {
    await args.transaction`INSERT INTO ${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)} (
                               id,
                               name
                           ) VALUES (
                               ${args.migration.id},
                               ${args.migration.name}
                           )`;
  }
}

1	import { Inject, Injectable } from '@nestjs/common';	16✔
2	import fs from 'node:fs';	16✔
3	import { join } from 'node:path';	16✔
4	import type { Sql, TransactionSql } from 'postgres';
5
6	type Migration = {
7	path: string;
8	id: number;
9	name: string;
10	};
11
12	/**
13	* Migrates a Postgres database using SQL and JavaScript files.
14	*
15	* Migrations should be in a directory, prefixed with a 5-digit number,
16	* and contain either an `index.sql` or `index.js` file.
17	*
18	* This is heavily inspired by `postgres-shift`
19	* @see https://github.com/porsager/postgres-shift/blob/master/index.js
20	*/
21	@Injectable()
22	export class PostgresDatabaseMigrator {	16✔
23	private static readonly MIGRATIONS_FOLDER = join(process.cwd(), 'migrations');	16✔
24	private static readonly SQL_MIGRATION_FILE = 'index.sql';	16✔
25	private static readonly JS_MIGRATION_FILE = 'index.js';	16✔
26	private static readonly MIGRATIONS_TABLE = 'migrations';	16✔
27
NEW 28	constructor(@Inject('DB_INSTANCE') private readonly sql: Sql) {}	×
29
30	/**
31	* Runs/records migrations not present in the {@link PostgresMigrator.MIGRATIONS_TABLE} table.
32	*
33	* Note: all migrations are run in a single transaction for optimal performance.
34	*/
35	async migrate(
36	path = PostgresDatabaseMigrator.MIGRATIONS_FOLDER,	×
37	): Promise<void> {
NEW 38	const migrations = this.getMigrations(path);	×
39
NEW 40	await this.assertMigrationsTable();	×
41
NEW 42	const last = await this.getLastRunMigration();	×
NEW 43	const remaining = migrations.slice(last?.id ?? 0);	×
44
NEW 45	await this.sql.begin(async (transaction: TransactionSql) => {	×
NEW 46	for (const current of remaining) {	×
NEW 47	await this.run({ transaction, migration: current });	×
NEW 48	await this.setLastRunMigration({ transaction, migration: current });	×
49	}
50	});
51	}
52
53	/**
54	* @private migrates up to/allows for querying before/after migration to test it.
55	*
56	* Note: each migration is ran in separate transaction to allow queries in between.
57	*
58	* @param args.migration - migration to test
59	* @param args.folder - folder to search for migrations
60	* @param args.before - function to run before each migration
61	* @param args.after - function to run after each migration
62	*
63	* @example
64	* ```typescript
65	* const result = await migrator.test({
66	* migration: '00001_initial',
67	* before: (sql) => sql`SELECT * FROM <table_name>`,
68	* after: (sql) => sql`SELECT * FROM <table_name>`,
69	* });
70	*
71	* expect(result.before).toBeUndefined();
72	* expect(result.after).toStrictEqual(expected);
73	* ```
74	*/
75	async test(args: {
76	migration: string;
77	before?: (sql: Sql) => Promise<unknown>;
78	after: (sql: Sql) => Promise<unknown>;
79	folder?: string;
80	}): Promise<{
81	before: unknown;
82	after: unknown;
83	}> {
NEW 84	const migrations = this.getMigrations(	×
85	args.folder ?? PostgresDatabaseMigrator.MIGRATIONS_FOLDER,	×
86	);
87
88	// Find index of migration to test
NEW 89	const migrationIndex = migrations.findIndex((migration) => {	×
NEW 90	return migration.path.includes(args.migration);	×
91	});
92
NEW 93	if (migrationIndex === -1) {	×
NEW 94	throw new Error(`Migration ${args.migration} not found`);	×
95	}
96
97	// Get migrations up to the specified migration
NEW 98	const migrationsToTest = migrations.slice(0, migrationIndex + 1);	×
99
100	let before: unknown;
101
NEW 102	for await (const migration of migrationsToTest) {	×
NEW 103	const isMigrationBeingTested = migration.path.includes(args.migration);	×
104
NEW 105	if (isMigrationBeingTested && args.before) {	×
NEW 106	before = await args.before(this.sql).catch(() => undefined);	×
107	}
108
NEW 109	await this.sql.begin((transaction) => {	×
NEW 110	return this.run({ transaction, migration });	×
111	});
112	}
113
NEW 114	const after = await args.after(this.sql).catch(() => undefined);	×
115
NEW 116	return { before, after };	×
117	}
118
119	/**
120	* Retrieves all migrations found at the specified path.
121	*
122	* @param path - path to search for migrations
123	*
124	* @returns array of {@link Migration}
125	*/
126	private getMigrations(path: string): Array<Migration> {
NEW 127	const migrations = fs	×
128	.readdirSync(path)
129	.filter((file) => {
NEW 130	const isDirectory = fs.statSync(join(path, file)).isDirectory();	×
NEW 131	const isMigration = file.match(/^[0-9]{5}_/);	×
NEW 132	return isDirectory && isMigration;	×
133	})
134	.sort()
135	.map((file) => {
NEW 136	return {	×
137	path: join(path, file),
138	id: parseInt(file.slice(0, 5)),
139	name: file.slice(6),
140	};
141	});
142
NEW 143	if (migrations.length === 0) {	×
NEW 144	throw new Error('No migrations found');	×
145	}
146
NEW 147	const latest = migrations.at(-1);	×
NEW 148	if (latest?.id !== migrations.length) {	×
NEW 149	throw new Error('Migrations numbered inconsistency');	×
150	}
151
NEW 152	return migrations;	×
153	}
154
155	/**
156	* Adds specified migration to the transaction if supported.
157	*
158	* @param args.transaction - {@link TransactionSql} to migration within
159	* @param args.migration - {@link Migration} to add
160	*/
161	private async run(args: {
162	transaction: TransactionSql;
163	migration: Migration;
164	}): Promise<void> {
NEW 165	const isSql = fs.existsSync(	×
166	join(args.migration.path, PostgresDatabaseMigrator.SQL_MIGRATION_FILE),
167	);
NEW 168	const isJs = fs.existsSync(	×
169	join(args.migration.path, PostgresDatabaseMigrator.JS_MIGRATION_FILE),
170	);
171
NEW 172	if (isSql) {	×
NEW 173	await args.transaction.file(	×
174	join(args.migration.path, PostgresDatabaseMigrator.SQL_MIGRATION_FILE),
175	);
NEW 176	} else if (isJs) {	×
NEW 177	const file = (await import(	×
NEW 178	join(args.migration.path, PostgresDatabaseMigrator.JS_MIGRATION_FILE)	×
179	)) as {
180	default: (transaction: TransactionSql) => Promise<void>;
181	};
NEW 182	await file.default(args.transaction);	×
183	} else {
NEW 184	throw new Error(`No migration file found for ${args.migration.path}`);	×
185	}
186	}
187	/**
188	* Creates the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table if it does not exist.
189	*/
190	private async assertMigrationsTable(): Promise<void> {
NEW 191	try {	×
NEW 192	await this.sql`SELECT	×
193	'${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)}'::regclass`;
194	} catch {
NEW 195	await this.sql`CREATE TABLE	×
196	${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)} (
197	id SERIAL PRIMARY KEY,
198	created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
199	name TEXT
200	)`;
201	}
202	}
203
204	/**
205	* Retrieves the last run migration from the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table.
206	*
207	* @returns last run {@link Migration}
208	*/
209	private async getLastRunMigration(): Promise<Migration> {
NEW 210	const [last] = await this.sql<Array<Migration>>`SELECT	×
211	id
212	FROM
213	${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)}
214	ORDER BY
215	id DESC
216	LIMIT
217	1`;
218
NEW 219	return last;	×
220	}
221
222	/**
223	* Adds the last run migration to the {@link PostgresDatabaseMigrator.MIGRATIONS_TABLE} table.
224	*
225	* @param args.transaction - {@link TransactionSql} to set within
226	* @param args.migration - {@link Migration} to set
227	*/
228	private async setLastRunMigration(args: {
229	transaction: TransactionSql;
230	migration: Migration;
231	}): Promise<void> {
NEW 232	await args.transaction`INSERT INTO ${this.sql(PostgresDatabaseMigrator.MIGRATIONS_TABLE)} (	×
233	id,
234	name
235	) VALUES (
236	${args.migration.id},
237	${args.migration.name}
238	)`;
239	}
240	}

safe-global / safe-client-gateway / 9546514333

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous