9598324938

Committed 20 Jun 2024 01:24PM UTC coverage: 49.226% (-0.01%) from 49.237%

Build # 9598324938

Build Type

push

github

Committed by

web-flow

Commit Message

Add automatic row timestamps for Accounts and Groups tables (#1674)

Add automatic row timestamps for Accounts and Groups tables

Run Details

413 of 2443 branches covered (16.91%)

Branch coverage included in aggregate %.

0 of 4 new or added lines in 1 file covered. (0.0%)

1 existing line in 1 file now uncovered.

4072 of 6668 relevant lines covered (61.07%)

12.8 hits per line

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

8.42

/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;
};

type TestResult<BeforeType, AfterType> = {
  before: BeforeType | undefined;
  after: AfterType;
};

/**
 * 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 });
      }
    });
  }

  /**
   * Migrates up to/allows for querying before/after migration to test it.
   * Uses generics to allow the caller to specify the return type of the before/after functions.
   *
   * 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<BeforeType, AfterType>(args: {
    migration: string;
    before?: (sql: Sql) => Promise<BeforeType>;
    after: (sql: Sql) => Promise<AfterType>;
    folder?: string;
  }): Promise<TestResult<BeforeType, AfterType>> {
    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: BeforeType | undefined;

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

      if (isMigrationBeingTested && args.before) {
        before = await args.before(this.sql).catch((err) => {
          throw Error(`Error running before function: ${err}`);
        });
      }

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

    const after = await args.after(this.sql).catch((err) => {
      throw Error(`Error running after function: ${err}`);
    });

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

safe-global / safe-client-gateway / 9598324938

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