DT
Drizzle Team•2w ago
uber

🚨 Issue: Testing with PostgreSQL and Drizzle ORM using Testcontainers

I’m writing tests for a function (listProjects) that interacts with a PostgreSQL database using Drizzle ORM. The problem is that listProjects is using a global db instance, which is initialized in the main application. In my tests, I want to use a PostgreSQL container from Testcontainers to spin up a test database, but my test code ends up using a different db instance, not the one I need. 
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schemas";

// Global db instance
const connection = postgres(process.env.DATABASE_URL);
const db = drizzle(connection, { logger: false, schema });

export default db;
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schemas";

// Global db instance
const connection = postgres(process.env.DATABASE_URL);
const db = drizzle(connection, { logger: false, schema });

export default db;
test.ts 
import { PostgreSqlContainer } from "@testcontainers/postgresql";
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { beforeAll, afterAll, describe, it, expect } from "vitest";
import { Project } from "../project";

let db: PostgresJsDatabase<typeof schema> & { $client: postgres.Sql<{}> };

beforeAll(async () => {
  const container = await new PostgreSqlContainer().start();

  const connectionString = `postgres://postgres:${process.env.POSTGRES_PASSWORD}@${container.getHost()}:${container.getFirstMappedPort()}/test-db`;

  const client = postgres(connectionString);
  db = drizzle(client, { logger: false, schema });
});

afterAll(async () => {
  await container.stop();
});

describe("listProjects", () => {
  it("should return a list of projects", async () => {
    const result = await Project.listProjects();
    expect(result).toBeInstanceOf(Array);
    expect(result.length).toBeGreaterThan(0);
  });
});
import { PostgreSqlContainer } from "@testcontainers/postgresql";
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { beforeAll, afterAll, describe, it, expect } from "vitest";
import { Project } from "../project";

let db: PostgresJsDatabase<typeof schema> & { $client: postgres.Sql<{}> };

beforeAll(async () => {
  const container = await new PostgreSqlContainer().start();

  const connectionString = `postgres://postgres:${process.env.POSTGRES_PASSWORD}@${container.getHost()}:${container.getFirstMappedPort()}/test-db`;

  const client = postgres(connectionString);
  db = drizzle(client, { logger: false, schema });
});

afterAll(async () => {
  await container.stop();
});

describe("listProjects", () => {
  it("should return a list of projects", async () => {
    const result = await Project.listProjects();
    expect(result).toBeInstanceOf(Array);
    expect(result.length).toBeGreaterThan(0);
  });
});
method to be tested. 
export async function listProjects() {
    const projects = await db.query.projects.findMany({
      with: {
        service: true,
        client: true,
      },
    });
    return projects;
  }
export async function listProjects() {
    const projects = await db.query.projects.findMany({
      with: {
        service: true,
        client: true,
      },
    });
    return projects;
  }
3 Replies
uber
uberOP•2w ago
I dont want to have to pass a db instance to my methods.. that leads me to handle the global instance to return the proper db instance based on the use case: In my global db instance i would either return my dev/prod db instance but if test return the testcontainers postgres db instance ? Is this the right approach? If no, what is a good simple approach. If yes how would i differenciate between dev/prod vs test? envs .. NODE_ENV? const container = await new PostgreSqlContainer().start(); would have to move from my test files and would go in my global db handler file to be used as db instance if NODE_ENV is test ? Is that a clean approach EDIT EDIT EDIT: i now have this but i dont rly like it: 
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { Resource } from "sst";
import * as schema from "./schemas";

export type DrizzlePgDB = PostgresJsDatabase<typeof schema> & {
  $client: postgres.Sql<{}>;
};

// Maintain a mutable connection that can be swapped in tests
let sqlConnection = postgres(Resource.Database.url);

export function setTestConnection(connectionString: string) {
  if (process.env.NODE_ENV !== "test") {
    throw new Error(
      "setTestConnection should only be called in test environment"
    );
  }
  sqlConnection = postgres(connectionString);
}

// Create a proxy to always use the current connection
const db = new Proxy({} as DrizzlePgDB, {
  get: (target, prop) => {
    const drizzleDB = drizzle(sqlConnection, { schema, logger: false });
    return drizzleDB[prop as keyof typeof drizzleDB];
  },
});

export default db;
import { drizzle, PostgresJsDatabase } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { Resource } from "sst";
import * as schema from "./schemas";

export type DrizzlePgDB = PostgresJsDatabase<typeof schema> & {
  $client: postgres.Sql<{}>;
};

// Maintain a mutable connection that can be swapped in tests
let sqlConnection = postgres(Resource.Database.url);

export function setTestConnection(connectionString: string) {
  if (process.env.NODE_ENV !== "test") {
    throw new Error(
      "setTestConnection should only be called in test environment"
    );
  }
  sqlConnection = postgres(connectionString);
}

// Create a proxy to always use the current connection
const db = new Proxy({} as DrizzlePgDB, {
  get: (target, prop) => {
    const drizzleDB = drizzle(sqlConnection, { schema, logger: false });
    return drizzleDB[prop as keyof typeof drizzleDB];
  },
});

export default db;
tests 
import {
  PostgreSqlContainer,
  StartedPostgreSqlContainer,
} from "@testcontainers/postgresql";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { Project } from "../project";
import { setTestConnection } from "../../db";

let container: StartedPostgreSqlContainer;

beforeAll(async () => {
  container = await new PostgreSqlContainer().start();
  const connectionString = container.getConnectionUri();
  console.log("Container started with connection >>", connectionString);

  // Set test connection
  setTestConnection(connectionString);
});

afterAll(async () => {
  await container.stop().catch(console.error);
  console.log("Container stopped");
});

describe("listProjects", () => {
  it("should return a list of projects", async () => {
    const result = await Project.listProjects();
    expect(result).toBeInstanceOf(Array);
    expect(result.length).toBeGreaterThan(0);
  });
});
import {
  PostgreSqlContainer,
  StartedPostgreSqlContainer,
} from "@testcontainers/postgresql";
import { afterAll, beforeAll, describe, expect, it } from "vitest";
import { Project } from "../project";
import { setTestConnection } from "../../db";

let container: StartedPostgreSqlContainer;

beforeAll(async () => {
  container = await new PostgreSqlContainer().start();
  const connectionString = container.getConnectionUri();
  console.log("Container started with connection >>", connectionString);

  // Set test connection
  setTestConnection(connectionString);
});

afterAll(async () => {
  await container.stop().catch(console.error);
  console.log("Container stopped");
});

describe("listProjects", () => {
  it("should return a list of projects", async () => {
    const result = await Project.listProjects();
    expect(result).toBeInstanceOf(Array);
    expect(result.length).toBeGreaterThan(0);
  });
});
rphlmr âš¡
rphlmr ⚡•2w ago
Maybe this can help you? https://github.com/rphlmr/drizzle-vitest-pg
GitHub
GitHub - rphlmr/drizzle-vitest-pg: How to use Drizzle & PGLite with...
How to use Drizzle & PGLite with Vitest. Contribute to rphlmr/drizzle-vitest-pg development by creating an account on GitHub.
uber
uberOP•2w ago
Yeah i was just lookin at this, i kinda like the idea of override the import, thats what i need (I think)

Did you find this page helpful?