PostgreSQL Module

PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Install

npm install @testcontainers/postgresql --save-dev

Examples

Connect and execute query:

it("should connect and return a query result", async () => {
  const container = await new PostgreSqlContainer().start();

  const client = new Client({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });
  await client.connect();

  const result = await client.query("SELECT 1");
  expect(result.rows[0]).toEqual({ "?column?": 1 });

  await client.end();
  await container.stop();
});

Connect and execute query using URI:

it("should work with database URI", async () => {
  const container = await new PostgreSqlContainer().start();

  const client = new Client({
    connectionString: container.getConnectionUri(),
  });
  await client.connect();

  const result = await client.query("SELECT 1");
  expect(result.rows[0]).toEqual({ "?column?": 1 });

  await client.end();
  await container.stop();
});

Set database:

it("should set database", async () => {
  const container = await new PostgreSqlContainer().withDatabase("customDatabase").start();

  const client = new Client({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });
  await client.connect();

  const result = await client.query("SELECT current_database()");
  expect(result.rows[0]).toEqual({ current_database: "customDatabase" });

  await client.end();
  await container.stop();
});

Set username:

it("should set username", async () => {
  const container = await new PostgreSqlContainer().withUsername("customUsername").start();

  const client = new Client({
    host: container.getHost(),
    port: container.getPort(),
    database: container.getDatabase(),
    user: container.getUsername(),
    password: container.getPassword(),
  });
  await client.connect();

  const result = await client.query("SELECT current_user");
  expect(result.rows[0]).toEqual({ current_user: "customUsername" });

  await client.end();
  await container.stop();
});

Using Snapshots

This example shows the usage of the postgres module's Snapshot feature to give each test a clean database without having to recreate the database container on every test or run heavy scripts to clean your database. This makes the individual tests very modular, since they always run on a brand-new database.

Tip

You should never pass the "postgres" system database as the container database name if you want to use snapshots. The Snapshot logic requires dropping the connected database and using the system database to run commands, which will not work if the database for the container is set to "postgres".

Test with a reusable Postgres container

it("should create and restore from snapshot", async () => {
  const container = await new PostgreSqlContainer().start();

  // Connect to the database
  let client = new Client({
    connectionString: container.getConnectionUri(),
  });
  await client.connect();

  // Create some test data
  await client.query("CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)");
  await client.query("INSERT INTO test_table (name) VALUES ('initial data')");

  // Close connection before snapshot (otherwise we'll get an error because user is already connected)
  await client.end();

  // Take a snapshot
  await container.snapshot();

  // Reconnect to database
  client = new Client({
    connectionString: container.getConnectionUri(),
  });
  await client.connect();

  // Modify the database
  await client.query("INSERT INTO test_table (name) VALUES ('data after snapshot')");

  // Verify both records exist
  let result = await client.query("SELECT * FROM test_table ORDER BY id");
  expect(result.rows).toHaveLength(2);
  expect(result.rows[0].name).toEqual("initial data");
  expect(result.rows[1].name).toEqual("data after snapshot");

  // Close connection before restore (same reason as above)
  await client.end();

  // Restore to the snapshot
  await container.restoreSnapshot();

  // Reconnect to database
  client = new Client({
    connectionString: container.getConnectionUri(),
  });
  await client.connect();

  // Verify only the initial data exists after restore
  result = await client.query("SELECT * FROM test_table ORDER BY id");
  expect(result.rows).toHaveLength(1);
  expect(result.rows[0].name).toEqual("initial data");

  await client.end();
  await container.stop();
});