test-database-limitations

Test Database Limitations

Overview

Test databases created via withDevDatabase() from @repo/test-utils use pglite (an in-memory PostgreSQL implementation via @prisma/dev). Pglite does not support PostgreSQL extensions like pgvector, pg_trgm, etc.

The Problem

When writing tests that use features requiring PostgreSQL extensions:

❌ WRONG: Trying to use extensions in pglite

await withClient(connectionString, async (client) => {
  await client.query('create extension if not exists vector');  // ❌ Fails: extension not available
  await client.query('create table post (embedding vector(1536))');  // ❌ Fails: vector type doesn't exist
});

Error: extension "vector" is not available or type "vector" does not exist

Solutions

Option 1: Avoid Extension Features in Tests (Recommended for Non-Extension Tests)

If your test is not specifically testing extension functionality, avoid selecting or using extension-dependent columns:

✅ CORRECT: Don't select extension columns in non-extension tests

// Test is about ORM includes, not vector operations
const plan = orm
  .user()
  .include.posts((child) =>
    child
      .select((m) => ({
        id: m.id,
        title: m.title,
        createdAt: m.createdAt,
        // ✅ Don't select embedding - test is about includes, not vectors
      }))
  )
  .select((u) => ({ id: u.id, posts: true }))
  .findMany();

Why? The test focuses on the feature being tested (includes), not the extension functionality. Extension functionality should be tested separately.

Option 2: Use Real PostgreSQL Database

If you must test extension functionality, use a real PostgreSQL database instead of withDevDatabase():

✅ CORRECT: Use real PostgreSQL for extension tests

// Connect to real PostgreSQL database (not pglite)
const connectionString = process.env['DATABASE_URL']; // Real PostgreSQL
const client = new Client({ connectionString });
await client.connect();

try {
  await client.query('create extension if not exists vector');
  await client.query('create table post (embedding vector(1536))');
  // ... test extension functionality
} finally {
  await client.end();
}