@drizzle-adapter/pglite

PgLite adapter implementation for the Drizzle Adapter ecosystem, providing a lightweight PostgreSQL-compatible database perfect for development and testing.

Overview

The @drizzle-adapter/pglite package provides the PgLite implementation for the Drizzle Adapter interface. While you don't interact with this package directly (you use @drizzle-adapter/core instead), it enables a lightweight PostgreSQL-compatible database in the Drizzle Adapter ecosystem.

Why PgLite?

PgLite offers unique advantages for development and testing:

• Embedded PostgreSQL: Full PostgreSQL compatibility without installation
• Zero Configuration: Works out of the box with no setup
• Memory or File Storage: Choose between in-memory or persistent storage
• Perfect for Testing: Fast, isolated database instances for each test
• Development Friendly: Quick startup, no external dependencies
• PostgreSQL Compatible: Use PostgreSQL features without PostgreSQL server
• Lightweight: Small footprint, perfect for CI/CD environments
• Fast Execution: Native implementation for better performance

Perfect For:

1. Unit Testing: Isolated database instances per test
2. Integration Testing: Full PostgreSQL compatibility for tests
3. Local Development: No need for PostgreSQL installation
4. CI/CD Pipelines: Fast, lightweight database for automated testing
5. Prototyping: Quick setup for new projects

Installation

# Install both the core package and the PgLite adapter
pnpm install @drizzle-adapter/core @drizzle-adapter/pglite

Important: Adapter Registration

For the adapter to work correctly with the DrizzleAdapterFactory, you must import it for its self-registration side effects:

// Import for side effects - adapter will self-register
import '@drizzle-adapter/pglite';

// Now you can use the factory
import { DrizzleAdapterFactory } from '@drizzle-adapter/core';

Usage

Configuration

import { DrizzleAdapterFactory, TypeDrizzleDatabaseConfig } from '@drizzle-adapter/core';

// In-memory database (perfect for testing)
const config: TypeDrizzleDatabaseConfig = {
  DATABASE_DRIVER: 'pglite'
};

const factory = new DrizzleAdapterFactory();
const adapter = factory.create(config);

Schema Definition

const dataTypes = adapter.getDataTypes();

const users = dataTypes.dbTable('users', {
  id: dataTypes.dbSerial('id').primaryKey(),
  name: dataTypes.dbText('name').notNull(),
  email: dataTypes.dbText('email').notNull().unique(),
  profile: dataTypes.dbJsonb('profile'),
  createdAt: dataTypes.dbTimestamp('created_at').defaultNow()
});

const posts = dataTypes.dbTable('posts', {
  id: dataTypes.dbSerial('id').primaryKey(),
  userId: dataTypes.dbInteger('user_id')
    .references(() => users.id),
  title: dataTypes.dbText('title').notNull(),
  content: dataTypes.dbText('content').notNull(),
  tags: dataTypes.dbArray('tags', { type: 'text' }),
  metadata: dataTypes.dbJsonb('metadata'),
  published: dataTypes.dbBoolean('published').default(false),
  createdAt: dataTypes.dbTimestamp('created_at').defaultNow()
});

Basic CRUD Operations

import { eq, and, or, desc, sql } from 'drizzle-orm';

const client = await adapter.getConnection().getClient();

// INSERT
// Single insert with returning
const [newUser] = await client
  .insert(users)
  .values({
    name: 'John Doe',
    email: '[email protected]',
    profile: { bio: 'Hello!' }
  })
  .returning();

// Bulk insert
await client
  .insert(posts)
  .values([
    {
      userId: newUser.id,
      title: 'First Post',
      content: 'Hello, world!',
      tags: ['hello', 'first']
    },
    {
      userId: newUser.id,
      title: 'Second Post',
      content: 'Another post',
      tags: ['second']
    }
  ]);

// SELECT
// Select all
const allUsers = await client
  .select()
  .from(users);

// Select with conditions
const user = await client
  .select()
  .from(users)
  .where(eq(users.email, '[email protected]'));

// Select with join and array operations
const postsWithTags = await client
  .select({
    userName: users.name,
    postTitle: posts.title,
    tags: posts.tags
  })
  .from(posts)
  .leftJoin(users, eq(posts.userId, users.id))
  .where(sql`${posts.tags} && ARRAY['hello']::text[]`)
  .orderBy(desc(posts.createdAt));

// UPDATE
await client
  .update(users)
  .set({
    name: 'John Smith',
    profile: { bio: 'Updated bio' }
  })
  .where(eq(users.id, newUser.id));

// DELETE
await client
  .delete(posts)
  .where(
    and(
      eq(posts.userId, newUser.id),
      eq(posts.published, false)
    )
  );

Testing Example

import { DrizzleAdapterFactory } from '@drizzle-adapter/core';

describe('User Repository Tests', () => {
  let adapter: DrizzleAdapterInterface;
  let repository: UserRepository;

  beforeEach(async () => {
    // New in-memory database for each test
    const factory = new DrizzleAdapterFactory();
    adapter = factory.create({
      DATABASE_DRIVER: 'pglite'
    });

    repository = new UserRepository(adapter);
    await repository.connect();

    // Create tables
    const client = await adapter.getConnection().getClient();
    await client.execute(sql`
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE
      )
    `);
  });

  afterEach(async () => {
    await repository.disconnect();
  });

  it('should create a new user', async () => {
    const user = await repository.create({
      name: 'Test User',
      email: '[email protected]'
    });

    expect(user.name).toBe('Test User');
    expect(user.email).toBe('[email protected]');
  });

  it('should find user by email', async () => {
    await repository.create({
      name: 'Test User',
      email: '[email protected]'
    });

    const user = await repository.findByEmail('[email protected]');
    expect(user).not.toBeNull();
    expect(user?.name).toBe('Test User');
  });
});

Best Practices

1. Testing: Use in-memory database for tests
2. Isolation: Create new instance for each test
3. Development: Use for local development without PostgreSQL
4. CI/CD: Perfect for automated testing pipelines
5. Schema Validation: Test PostgreSQL schemas without full server

Contributing

We welcome contributions! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

Related Packages

• @drizzle-adapter/core - Core interfaces and types
• @drizzle-adapter/pg-core - Shared PostgreSQL functionality