Test Database Interactions with Jest and Real Databases (2026)

The most surprising truth about testing database interactions with Jest is that you don’t have to spin up a full-blown, persistent database for every test.

Let’s see it in action. Imagine you have a simple userService that fetches users from a database:

// userService.js
import db from './db'; // Assume this is your ORM or DB connection

async function getUserById(id) {
  return db.query('SELECT * FROM users WHERE id = $1', [id]);
}

async function createUser(userData) {
  return db.query('INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *', [userData.name, userData.email]);
}

export { getUserById, createUser };

And your Jest test might look like this:

// userService.test.js
import { getUserById, createUser } from './userService';
import db from './db'; // We'll mock this

// Mock the database connection
jest.mock('./db');

describe('userService', () => {
  afterEach(() => {
    jest.clearAllMocks();
  });

  it('should create a user', async () => {
    const mockUser = { id: 1, name: 'Alice', email: 'alice@example.com' };
    db.query.mockResolvedValue({ rows: [mockUser] }); // Mocking the INSERT and RETURNING *

    const newUser = await createUser({ name: 'Alice', email: 'alice@example.com' });

    expect(newUser.rows[0]).toEqual(mockUser);
    expect(db.query).toHaveBeenCalledTimes(1);
    expect(db.query).toHaveBeenCalledWith(
      'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *',
      ['Alice', 'alice@example.com']
    );
  });

  it('should get a user by ID', async () => {
    const mockUser = { id: 1, name: 'Alice', email: 'alice@example.com' };
    db.query.mockResolvedValue({ rows: [mockUser] }); // Mocking the SELECT

    const user = await getUserById(1);

    expect(user.rows[0]).toEqual(mockUser);
    expect(db.query).toHaveBeenCalledTimes(1);
    expect(db.query).toHaveBeenCalledWith('SELECT * FROM users WHERE id = $1', [1]);
  });
});

This example uses jest.mock('./db') to replace the actual database module with a mock. The mockResolvedValue on db.query allows us to control what the database "returns" for specific queries. This is fantastic for unit tests where you want to isolate your code and ensure your business logic (how userService uses db.query) is correct, without the overhead and flakiness of a real database.

However, this approach doesn’t test the database interaction itself – it tests that your code calls the database module correctly. If you need to verify that your SQL is valid, that your schema constraints are respected, or that your ORM is mapping data correctly, you’ll need a real database.

The problem is, running a full PostgreSQL or MySQL instance for every test suite can be slow, complex to set up, and prone to race conditions. This is where testing with a temporary, in-memory or ephemeral database comes in. Libraries like knex (an SQL query builder) or sequelize (an ORM) can often spin up temporary databases for you, or you can use Docker to manage a dedicated test database container.

Consider this setup using knex and pg for PostgreSQL, and running tests against a temporary database:

First, set up knex for your tests:

// knexfile.js (for testing)
module.exports = {
  test: {
    client: 'pg',
    connection: {
      host: 'localhost',
      port: 5432,
      user: 'test_user',
      password: 'test_password',
      database: 'my_app_test', // A dedicated DB for tests
    },
    migrations: {
      directory: './db/migrations', // Point to your migration files
    },
    seeds: {
      directory: './db/seeds/test', // Point to test seed files
    },
  },
};

Then, in your Jest setup file (jest.setup.js):

// jest.setup.js
const knex = require('knex');
const config = require('../knexfile');
const path = require('path');

const knexConfig = config.test;
const knexInstance = knex(knexConfig);

// Create database, run migrations, and seed before all tests
beforeAll(async () => {
  // Ensure the test database exists (you might need to create it manually or via script)
  // For PostgreSQL, you might connect to 'postgres' and then CREATE DATABASE my_app_test
  await knexInstance.migrate.latest();
  await knexInstance.seed.run();
});

// Clean up the database after all tests
afterAll(async () => {
  await knexInstance.migrate.rollback(null, true); // Rollback all migrations
  await knexInstance.destroy();
});

// Clean up tables between tests to ensure isolation
afterEach(async () => {
  const tables = await knexInstance.raw("SELECT tablename FROM pg_tables WHERE schemaname = 'public'");
  for (const table of tables.rows) {
    if (table.tablename !== 'knex_migrations' && table.tablename !== 'knex_migrations_lock') {
      await knexInstance.raw(`TRUNCATE TABLE "${table.tablename}" CASCADE`);
    }
  }
});

module.exports = knexInstance; // Export the knex instance for tests to use

And your userService.test.js would change to use the real knex instance:

// userService.test.js
import { getUserById, createUser } from './userService';
import knexInstance from './jest.setup'; // Import the shared knex instance

describe('userService', () => {
  it('should create and retrieve a user', async () => {
    const userData = { name: 'Bob', email: 'bob@example.com' };
    const createdUser = await createUser(userData); // This now hits the real DB

    expect(createdUser.rows[0].name).toBe(userData.name);
    expect(createdUser.rows[0].email).toBe(userData.email);

    const fetchedUser = await getUserById(createdUser.rows[0].id); // This also hits the real DB

    expect(fetchedUser.rows[0].id).toBe(createdUser.rows[0].id);
    expect(fetchedUser.rows[0].name).toBe(userData.name);
  });

  // ... other tests using knexInstance directly or indirectly via userService
});

The key here is that beforeAll runs migrations and seeds once for the entire test suite, and afterEach truncates tables between tests. This provides a clean slate for each test while avoiding the overhead of database creation/destruction per test. knexInstance.raw("SELECT tablename FROM pg_tables WHERE schemaname = 'public'") gets all table names in the public schema, and TRUNCATE TABLE ... CASCADE efficiently removes all data from those tables, including foreign-key related data.

The one thing most people don’t realize is that TRUNCATE is often significantly faster than DELETE FROM table because it’s a DDL command that resets table state, rather than a DML command that logs each row deletion. Using CASCADE ensures that related data in other tables (e.g., orders for a user) is also removed, maintaining data integrity for your test isolation.

The next challenge you’ll likely face is managing complex data dependencies and ensuring your test data accurately reflects production scenarios without becoming overly cumbersome.