Skip to content

PostgreSQL

Install

1
npm install @testcontainers/postgresql --save-dev

Examples

These examples use the following libraries:

  • pg
    1
2
    npm install pg
npm install @types/pg --save-dev

Choose an image from the container registry and substitute IMAGE.

Execute a query

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
await using container = await new PostgreSqlContainer(IMAGE).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();

Connect via URI

1
2
3
4
5
await using container = await new PostgreSqlContainer(IMAGE).start();

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

With database

1
await using container = await new PostgreSqlContainer(IMAGE).withDatabase("customDatabase").start();

With username

1
await using container = await new PostgreSqlContainer(IMAGE).withUsername("customUsername").start();

Snapshots

Warning

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".

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
await using container = await new PostgreSqlContainer(IMAGE).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();