### Install @voxpelli/pg-utils

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Installs the @voxpelli/pg-utils package using npm. This is the first step to using the library in your Node.js project.

```bash
npm install @voxpelli/pg-utils
```

--------------------------------

### Mocha Test Integration with pg-utils

Source: https://context7.com/voxpelli/pg-utils/llms.txt

This example demonstrates integrating PgTestHelpers with Mocha for robust database integration tests. It includes setup and teardown logic for managing test databases, such as resetting tables and inserting fixtures before each test, and cleaning up afterwards.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';
import chai from 'chai';

const should = chai.should();
const connectionString = process.env.DATABASE_URL;

describe('User Repository', function () {
  /** @type {PgTestHelpers} */
  let testHelpers;

  before(async function () {
    // Setup before all tests in suite
    testHelpers = new PgTestHelpers({
      connectionString,
      fixtureFolder: new URL('./fixtures', import.meta.url),
      schema: new URL('./schema.sql', import.meta.url),
      tablesWithDependencies: ['posts', 'comments'],
    });
  });

  beforeEach(async function () {
    // Reset database before each test
    await testHelpers.removeTables();
    await testHelpers.initTables();
    await testHelpers.insertFixtures();
  });

  after(async function () {
    // Cleanup after all tests
    await testHelpers.removeTables();
    await testHelpers.end();
  });

  it('should find users by email', async function () {
    const { rows } = await testHelpers.queryPromise(
      'SELECT * FROM users WHERE email = $1',
      ['bob@example.com']
    );

    rows.should.have.lengthOf(1);
    rows[0].name.should.equal('Bob Smith');
  });

  it('should create new users', async function () {
    await testHelpers.queryPromise(
      'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
      ['550e8400-e29b-41d4-a716-446655440000', 'New User', 'new@example.com']
    );

    const { rows } = await testHelpers.queryPromise('SELECT COUNT(*) as count FROM users');
    parseInt(rows[0].count, 10).should.equal(3); // 2 fixtures + 1 new
  });
});

```

--------------------------------

### Initialize and Use PgTestHelpers for Database Testing

Source: https://github.com/voxpelli/pg-utils/blob/main/README.md

Demonstrates how to instantiate and use the `PgTestHelpers` class for managing PostgreSQL database states during testing. It covers initializing tables, inserting fixtures, and ensuring proper cleanup by releasing database locks and closing connections.

```javascript
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();
}
```

--------------------------------

### Configure PgTestHelpers Constructor Options (JavaScript)

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Illustrates various ways to configure the PgTestHelpers constructor, including specifying the schema from an SQL file, an inline SQL string, or using an Umzug migration instance. It also shows a complete configuration with all available options.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

// Option 1: Schema from SQL file
const helpers1 = new PgTestHelpers({
  connectionString: 'postgres://localhost/mydb',
  schema: new URL('./create-tables.sql', import.meta.url),
});

// Option 2: Schema as inline string
const helpers2 = new PgTestHelpers({
  connectionString: 'postgres://localhost/mydb',
  schema: `
    CREATE TABLE users (
      id UUID PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) UNIQUE
    );
  `,
});

// Option 3: Schema using Umzug migration function
const helpers3 = new PgTestHelpers({
  connectionString: 'postgres://localhost/mydb',
  schema: (pool) => {
    // Return an Umzug instance for migration-based schema management
    return createUmzugInstance(pool);
  },
});

// Full configuration with all options
const helpersComplete = new PgTestHelpers({
  connectionString: 'postgres://user:pass@localhost:5432/test_db',
  fixtureFolder: new URL('./test/fixtures', import.meta.url),
  ignoreTables: ['schema_migrations', 'spatial_ref_sys'],
  schema: new URL('./db/schema.sql', import.meta.url),
  tablesWithDependencies: [
    'order_items',      // Depends on orders and products
    ['orders', 'reviews'], // Both depend on users and products
    'products',         // Depends on categories
  ],
});

await helpersComplete.end();
```

--------------------------------

### Initialize Database Tables with PgTestHelpers (JavaScript)

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Shows how to use the initTables() method of PgTestHelpers to create database tables based on a configured schema file. This method automatically acquires an advisory lock, ensuring that schema modifications are safe during concurrent test runs.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
});

try {
  // Creates all tables defined in schema.sql
  // Lock acquired automatically, held until end() is called
  await pgHelpers.initTables();

  // Verify tables were created
  const { rows } = await pgHelpers.queryPromise(
    "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"
  );
  console.log('Created tables:', rows.map(r => r.tablename));
  // Output: Created tables: ['users', 'posts', 'comments']
} catch (error) {
  console.error('Failed to create tables:', error.message);
  // Error includes cause chain for debugging
} finally {
  await pgHelpers.end();
}
```

--------------------------------

### Initialize PgTestHelpers and Manage Database Lifecycle (JavaScript)

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Demonstrates the basic usage of the PgTestHelpers class to initialize a test database schema, insert fixture data from CSV files, execute queries, and clean up resources. It automatically acquires and releases advisory locks for concurrency safety.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: 'postgres://user:pass@localhost/test_db',
  fixtureFolder: new URL('./fixtures', import.meta.url),
  ignoreTables: ['migrations'],
  schema: new URL('./schema.sql', import.meta.url),
  tablesWithDependencies: [
    'foobar',           // Dropped first (depends on user_foo and user_bar)
    ['user_foo', 'user_bar'], // Dropped second (these can be dropped in parallel)
    // 'users' table dropped last automatically
  ]
});

try {
  // Initialize database schema (acquires advisory lock automatically)
  await pgHelpers.initTables();

  // Load fixture data from CSV files
  await pgHelpers.insertFixtures();

  // Run your tests using queryPromise for direct queries
  const { rows } = await pgHelpers.queryPromise('SELECT * FROM users WHERE role = $1', ['admin']);
  console.log('Admin users:', rows);

  // Clean up tables when done
  await pgHelpers.removeTables();
} finally {
  // Always release lock and close connections
  await pgHelpers.end();
}
```

--------------------------------

### PgTestHelpers Options Configuration

Source: https://github.com/voxpelli/pg-utils/blob/main/README.md

Defines the configuration options for the `PgTestHelpers` class, specifying how to connect to the database, manage fixture data, handle table dependencies, and initialize the schema.

```typescript
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: [
    // ...
  ]
});
```

--------------------------------

### PgTestHelpers Class

Source: https://github.com/voxpelli/pg-utils/blob/main/README.md

A class that provides helper methods for setting up, populating, and cleaning up PostgreSQL databases for testing purposes. It manages database connections and uses advisory locks for exclusive access.

```APIDOC
## PgTestHelpers

Class that creates a helpers instance

### Syntax

```ts
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)_

### 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](https://www.postgresql.org/docs/current/explicit-locking.html#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.
```

--------------------------------

### Execute Raw SQL with queryPromise Property in JavaScript

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Provides direct access to the underlying `pool.query` method for executing raw SQL queries. This is useful for performing complex operations, inserting data directly, or verifying table states during tests.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
  fixtureFolder: new URL('./fixtures', import.meta.url),
});

try {
  await pgHelpers.initTables();
  await pgHelpers.insertFixtures();

  // Execute parameterized queries
  const { rows: users } = await pgHelpers.queryPromise(
    'SELECT * FROM users WHERE role = $1',
    ['admin']
  );

  // Insert test data directly
  await pgHelpers.queryPromise(
    'INSERT INTO users (id, name, email) VALUES ($1, $2, $3)',
    ['550e8400-e29b-41d4-a716-446655440000', 'Test User', 'test@example.com']
  );

  // Verify table state
  const { rows: allTables } = await pgHelpers.queryPromise(
    "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"
  );
  console.log('Tables:', allTables.map(t => t.tablename));
} finally {
  await pgHelpers.end();
}
```

--------------------------------

### Load Test Data with insertFixtures() in JavaScript

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Loads test data from CSV files into PostgreSQL tables. Requires PostgreSQL 15+ for HEADER MATCH. Fixture files must be named after their target tables and placed in the configured fixture folder. The method handles inserting data respecting table dependencies.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

// Directory structure:
// ./fixtures/
//   users.csv
//   posts.csv
//   comments.csv

// users.csv content:
// "id","name","email","role","created_at"
// "c7f1e901-28b0-41d7-9313-80fd10a07e74","Bob Smith","bob@example.com","admin","2024-12-03T13:53:56.587Z"
// "2d8d41ba-fcc4-4296-a212-61365e0efb75","Alice Jones","alice@example.com","user","2024-12-03T13:53:56.587Z"

const pgHelpers = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  fixtureFolder: new URL('./fixtures', import.meta.url),
  schema: new URL('./schema.sql', import.meta.url),
  tablesWithDependencies: ['comments', 'posts'], // Insert order: users -> posts -> comments
});

try {
  await pgHelpers.initTables();
  await pgHelpers.insertFixtures();

  // Verify fixtures loaded
  const { rows } = await pgHelpers.queryPromise('SELECT * FROM users');
  console.log('Loaded users:', rows);
  // Output: [
  //   { id: 'c7f1e901-...', name: 'Bob Smith', email: 'bob@example.com', ... },
  //   { id: '2d8d41ba-...', name: 'Alice Jones', email: 'alice@example.com', ... }
  // ]
} finally {
  await pgHelpers.end();
}
```

--------------------------------

### csvFromFolderToDb Function

Source: https://github.com/voxpelli/pg-utils/blob/main/README.md

Imports data into PostgreSQL tables from a folder containing CSV files. Each CSV file should be named after the table it corresponds to.

```APIDOC
## 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

```ts
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
```

--------------------------------

### Import Data from CSV to Database

Source: https://github.com/voxpelli/pg-utils/blob/main/README.md

Illustrates the usage of the `csvFromFolderToDb` function to import data from CSV files into PostgreSQL tables. It requires a database connection (pool or connection string) and the path to the folder containing the CSV files.

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

--------------------------------

### Import CSV Files to Database with pg-utils

Source: https://context7.com/voxpelli/pg-utils/llms.txt

The csvFromFolderToDb function imports CSV files into PostgreSQL tables. It infers table names from filenames and handles insertion order based on specified dependencies. It can accept either a connection string or an existing pg.Pool instance.

```javascript
import { csvFromFolderToDb } from '@voxpelli/pg-utils';
import { Pool } from 'pg';

// Using connection string directly
await csvFromFolderToDb(
  'postgres://user:pass@localhost/mydb',
  new URL('./fixtures', import.meta.url),
  ['comments', 'posts'] // Tables with dependencies (inserted last)
);

// Using existing Pool instance
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});

try {
  // Import fixtures with dependency ordering
  // Files: users.csv, posts.csv, comments.csv
  // Order: users -> posts -> comments (because comments and posts have dependencies)
  await csvFromFolderToDb(
    pool,
    './test/fixtures',
    ['comments', 'posts']
  );

  // Verify import
  const { rows } = await pool.query('SELECT COUNT(*) as count FROM users');
  console.log('Imported users:', rows[0].count);
} catch (error) {
  // Error includes table name that failed
  console.error(error.message);
  // "Failed inserting data into \"comments\""
  console.error(error.cause); // Original PostgreSQL error
} finally {
  await pool.end();
}
```

--------------------------------

### Database Locking for Concurrent Tests with pg-utils

Source: https://context7.com/voxpelli/pg-utils/llms.txt

PgTestHelpers utilizes PostgreSQL advisory locks to serialize concurrent test runs against the same database. This prevents race conditions and deadlocks by ensuring only one test suite modifies the database at a time. The lock is acquired during table removal and released upon completion.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

// Two test suites running concurrently against the same database
const suite1 = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
});

const suite2 = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
});

// Run concurrently - advisory lock ensures serialization
const [result1, result2] = await Promise.all([
  (async () => {
    try {
      await suite1.removeTables(); // Acquires lock, blocks suite2
      await suite1.initTables();
      // ... run tests ...
      return 'suite1 complete';
    } finally {
      await suite1.end(); // Releases lock, suite2 can proceed
    }
  })(),
  (async () => {
    try {
      await suite2.removeTables(); // Waits for suite1's lock
      await suite2.initTables();
      // ... run tests ...
      return 'suite2 complete';
    } finally {
      await suite2.end();
    }
  })(),
]);

// Both suites complete successfully without interference
console.log(result1, result2);
```

--------------------------------

### Drop Tables with removeTables() in JavaScript

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Drops all tables from the public schema, respecting dependency order. It uses `CASCADE` to handle foreign key constraints and allows specifying tables to ignore and the order of dropping.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
  ignoreTables: ['spatial_ref_sys'], // Never drop these tables
  tablesWithDependencies: [
    'foobar',                    // Dropped first
    ['user_foo', 'user_bar'],    // Dropped second (in parallel)
    // Remaining tables dropped last
  ],
});

try {
  // Clean slate before test
  await pgHelpers.removeTables();

  // Run test setup
  await pgHelpers.initTables();
  await pgHelpers.insertFixtures();

  // ... run tests ...

  // Clean up after test
  await pgHelpers.removeTables();

  // Verify cleanup
  const { rows } = await pgHelpers.queryPromise(
    "SELECT tablename FROM pg_tables WHERE schemaname = 'public'"
  );
  console.log('Remaining tables:', rows); // Only ignored tables remain
} finally {
  await pgHelpers.end();
}
```

--------------------------------

### Release Connections with end() in JavaScript

Source: https://context7.com/voxpelli/pg-utils/llms.txt

Releases the advisory lock and closes all database connections managed by the PgTestHelpers instance. This method must be called when the helper is no longer needed, typically within a `finally` block to ensure cleanup.

```javascript
import { PgTestHelpers } from '@voxpelli/pg-utils';

const pgHelpers = new PgTestHelpers({
  connectionString: process.env.DATABASE_URL,
  schema: new URL('./schema.sql', import.meta.url),
});

try {
  await pgHelpers.initTables();
  // ... perform operations ...
} finally {
  // Releases advisory lock and closes pool
  await pgHelpers.end();

  // Calling end() again is safe (no-op)
  await pgHelpers.end();

  // Using helper after end() throws an error
  try {
    await pgHelpers.initTables();
  } catch (error) {
    console.log(error.message);
    // "This PgTestHelpers instance has been ended through .end() and can not be used any more."
  }
}
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.