PostgreSQL adapter implementation for the Drizzle Adapter ecosystem using the postgres.js driver.
npm install @drizzle-adapter/postgres-jsPostgreSQL adapter implementation for the Drizzle Adapter ecosystem using the postgres.js driver.
The @drizzle-adapter/postgres-js package provides the PostgreSQL implementation for the Drizzle Adapter interface using the modern and lightweight postgres.js driver. While you don't interact with this package directly (you use @drizzle-adapter/core instead), it enables PostgreSQL support in the Drizzle Adapter ecosystem.
The postgres.js driver offers unique advantages:
- Zero Dependencies: Minimal footprint with no external dependencies
- Modern Design: Clean, promise-based API without legacy baggage
- Native Types: First-class TypeScript support
- Built-in Pooling: Efficient connection management
- Prepared Statements: Automatic statement preparation
- Performance: Optimized for high throughput
- Lightweight: Small bundle size, perfect for serverless
- Simple API: Intuitive interface without complexity
``bash`Install both the core package and the PostgreSQL adapter
pnpm install @drizzle-adapter/core @drizzle-adapter/postgres-js
For the adapter to work correctly with the DrizzleAdapterFactory, you must import it for its self-registration side effects:
`typescript
// Import for side effects - adapter will self-register
import '@drizzle-adapter/postgres-js';
// Now you can use the factory
import { DrizzleAdapterFactory } from '@drizzle-adapter/core';
`
`typescript
import { DrizzleAdapterFactory, TypeDrizzleDatabaseConfig } from '@drizzle-adapter/core';
const config: TypeDrizzleDatabaseConfig = {
DATABASE_DRIVER: 'postgres-js',
DATABASE_URL: 'postgresql://user:password@localhost:5432/database'
};
const factory = new DrizzleAdapterFactory();
const adapter = factory.create(config);
`
`typescript
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'),
searchVector: dataTypes.dbTsVector('search_vector'),
createdAt: dataTypes.dbTimestampTz('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.dbTimestampTz('created_at').defaultNow()
});
`
`typescript
import { eq, and, or, desc, sql } from 'drizzle-orm';
const connection = await adapter.getConnection();
const client = connection.getClient();
try {
// INSERT
// Single insert with returning
const [newUser] = await client
.insert(users)
.values({
name: 'John Doe',
email: 'john@example.com',
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, 'john@example.com'));
// 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));
// Full-text search
const searchResults = await client
.select()
.from(posts)
.where(sqlto_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ to_tsquery('english', 'hello'))
.orderBy(desc(posts.createdAt));
// UPDATE
// Update with JSONB operations
await client
.update(users)
.set({
profile: sql${users.profile} || '{"title": "New Title"}'::jsonb
})
.where(eq(users.id, newUser.id));
// DELETE
await client
.delete(posts)
.where(
and(
eq(posts.userId, newUser.id),
eq(posts.published, false)
)
);
} finally {
await connection.disconnect();
}
`
#### Transaction Support
`typescript
const connection = await adapter.getConnection();
const client = connection.getClient();
try {
await client.transaction(async (tx) => {
const [user] = await tx
.insert(users)
.values({
name: 'John Doe',
email: 'john@example.com'
})
.returning();
await tx
.insert(posts)
.values({
userId: user.id,
title: 'My First Post',
content: 'Hello, world!'
});
});
} finally {
await connection.disconnect();
}
`
#### Streaming Results
postgres.js supports efficient streaming of large result sets:
`typescript
const stream = client
.select()
.from(posts)
.where(eq(posts.published, true))
.stream();
for await (const post of stream) {
// Process each post
console.log(post.title);
}
``
1. Connection Management: Always use try/finally to ensure connections are closed
2. Transactions: Use transactions for related operations
3. Prepared Statements: Statements are automatically prepared
4. Error Handling: Implement proper error handling
5. Query Optimization: Use indexes and proper query planning
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.
- @drizzle-adapter/core - Core interfaces and types
- @drizzle-adapter/pg-core - Shared PostgreSQL functionality
- @drizzle-adapter/node-postgres - Alternative PostgreSQL adapter