@voxpelli/pg-utils

My personal database utils / helpers for Postgres

Usage

import {
  csvFromFolderToDb,
  PgTestHelpers,
} from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: 'postgres://user:pass@localhost/example',
  fixtureFolder: new URL('./fixtures', import.meta.url),
  ignoreTables: ['xyz'],
  schema: new URL('./create-tables.sql', import.meta.url),
  tablesWithDependencies: [
    'abc',
    ['foo', 'bar'],
  ]
});

try {
  // The helper automatically acquires an exclusive database lock
  // on the first call to initTables(), insertFixtures(), or removeTables()
  // to prevent concurrent access between tests during test operations.
  await pgHelpers.initTables();
  await pgHelpers.insertFixtures();
} finally {
  // Always release the lock and close connections,
  // even if a test or setup step throws an error
  await pgHelpers.end();
}

PgTestHelpers

Class that creates a helpers instance

Syntax

new PgTestHelpers({
  connectionString: 'postgres://user:pass@localhost/example',
  fixtureFolder: new URL('./fixtures', import.meta.url),
  ignoreTables: [
    // ...
  ],
  schema: new URL('./create-tables.sql', import.meta.url),
  tablesWithDependencies: [
    // ...
  ]
});

Arguments

• options – PgTestHelpersOptions

PgTestHelpersOptions

• connectionString – string – a connection string for the postgres database
• fixtureFolder – [string | URL] – optional – the path to a folder of .csv-file fixtures named by their respective table
• ignoreTables – [string[]] – optional – names of tables to ignore when dropping
• schema – string | URL | Umzug – an umzug instance that can be used to initialize tables or the schema itself or a URL to a text file containing the schema
• tablesWithDependencies – [Array<string[] | string>] – optional – names of tables that depend on other tables. If some of these tables depend on each other, then use nested arrays to ensure that within the same array no two tables depend on each other

Methods

• initTables() => Promise<void> – sets up all of the tables. Automatically acquires an exclusive database lock on first call.
• insertFixtures() => Promise<void> – inserts all the fixtures data into the tables (only usable if fixtureFolder has been set). Automatically acquires an exclusive database lock on first call.
• removeTables() => Promise<void> – removes all of the tables (starting with tablesWithDependencies). Automatically acquires an exclusive database lock on first call.
• end() => Promise<void> – releases the database lock (if acquired) and closes all database connections. Always call this when done to properly clean up resources.

Database Locking

The PgTestHelpers class uses PostgreSQL advisory locks to ensure exclusive database access during test operations. The lock is automatically acquired on the first call to initTables(), insertFixtures(), or removeTables(), and is held until end() is called. This prevents multiple test suites from interfering with each other when using the same database.

csvFromFolderToDb()

Imports data into tables from a folder of CSV files. All files will be imported and they should named by their table names + .csv.

Syntax

csvFromFolderToDb(pool, path, [tablesWithDependencies]) => Promise<void>

Arguments

• pool – string | pg.Pool – a postgres pool to use for the queries or a connection string that will be used to create one
• path – string | URL – the path to the folder that contains the CSV:s named by their table names
• tablesWithDependencies – [string[]] – optional – names of tables that depend on other tables. The first name in this list will have its fixtures inserted last

Returns

Promise that resolves on completion