Drizzle has native support for Effect PostgreSQL connections with the @effect/sql-pg driver.
This is the basic file structure of the project. In the src/db directory, we have table definition in schema.ts. In drizzle folder there are sql migration file and snapshots.
📦 <project root>
├ 📂 drizzle
├ 📂 src
│ ├ 📂 db
│ │ └ 📜 schema.ts
│ └ 📜 index.ts
├ 📜 .env
├ 📜 drizzle.config.ts
├ 📜 package.json
└ 📜 tsconfig.jsonnpm i drizzle-orm@rc effect @effect/sql-pg pg dotenv
npm i -D drizzle-kit@rc tsx @types/pgCreate a .env file in the root of your project and add your database connection variable:
DATABASE_URL=If you don’t have a PostgreSQL database yet and want to create one for testing, you can use our guide on how to set up PostgreSQL in Docker.
The PostgreSQL in Docker guide is available here. Go set it up, generate a database URL (explained in the guide), and come back for the next steps
Create a index.ts file in the src directory and initialize the connection:
import 'dotenv/config';
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { PgClient } from '@effect/sql-pg';
import * as Effect from 'effect/Effect';
import * as Redacted from 'effect/Redacted';
import { types } from 'pg';
// Configure the PgClient layer with type parsers
const PgClientLive = PgClient.layer({
url: Redacted.make(process.env.DATABASE_URL!),
types: {
getTypeParser: (typeId, format) => {
// Return raw values for date/time types to let Drizzle handle parsing
if ([1184, 1114, 1082, 1186, 1231, 1115, 1185, 1187, 1182].includes(typeId)) {
return (val: any) => val;
}
return types.getTypeParser(typeId, format);
},
},
});
const program = Effect.gen(function*() {
// Create the database with default services
const db = yield* PgDrizzle.makeWithDefaults();
// Your queries here...
});
// Run the program with the PgClient layer
Effect.runPromise(program.pipe(Effect.provide(PgClientLive)));Create a schema.ts file in the src/db directory and declare your table:
import { integer, pgTable, varchar } from "drizzle-orm/pg-core";
export const usersTable = pgTable("users", {
id: integer().primaryKey().generatedAlwaysAsIdentity(),
name: varchar({ length: 255 }).notNull(),
age: integer().notNull(),
email: varchar({ length: 255 }).notNull().unique(),
});Drizzle config - a configuration file that is used by Drizzle Kit and contains all the information about your database connection, migration folder and schema files.
Create a drizzle.config.ts file in the root of your project and add the following content:
import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
out: './drizzle',
schema: './src/db/schema.ts',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});You can directly apply changes to your database using the drizzle-kit push command. This is a convenient method for quickly testing new schema designs or modifications in a local development environment, allowing for rapid iterations without the need to manage migration files:
npx drizzle-kit pushRead more about the push command in documentation.
Alternatively, you can generate migrations using the drizzle-kit generate command and then apply them using the drizzle-kit migrate command:
Generate migrations:
npx drizzle-kit generateApply migrations:
npx drizzle-kit migrateRead more about migration process in documentation.
Let’s update the src/index.ts file with queries to create, read, update, and delete users
import 'dotenv/config';
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { PgClient } from '@effect/sql-pg';
import * as Effect from 'effect/Effect';
import * as Redacted from 'effect/Redacted';
import { types } from 'pg';
import { eq } from 'drizzle-orm';
import { usersTable } from './db/schema';
const PgClientLive = PgClient.layer({
url: Redacted.make(process.env.DATABASE_URL!),
types: {
getTypeParser: (typeId, format) => {
if ([1184, 1114, 1082, 1186, 1231, 1115, 1185, 1187, 1182].includes(typeId)) {
return (val: any) => val;
}
return types.getTypeParser(typeId, format);
},
},
});
const program = Effect.gen(function*() {
const db = yield* PgDrizzle.makeWithDefaults();
const user: typeof usersTable.$inferInsert = {
name: 'John',
age: 30,
email: 'john@example.com',
};
yield* db.insert(usersTable).values(user);
console.log('New user created!')
const users = yield* db.select().from(usersTable);
console.log('Getting all users from the database: ', users)
/*
const users: {
id: number;
name: string;
age: number;
email: string;
}[]
*/
yield* db
.update(usersTable)
.set({
age: 31,
})
.where(eq(usersTable.email, user.email));
console.log('User info updated!')
yield* db.delete(usersTable).where(eq(usersTable.email, user.email));
console.log('User deleted!')
});
Effect.runPromise(program.pipe(Effect.provide(PgClientLive)));To run any TypeScript files, you have several options, but let’s stick with one: using tsx
You’ve already installed tsx, so we can run our queries now
Run index.ts script
npx tsx src/index.tsWe suggest using bun to run TypeScript files. With bun, such scripts can be executed without issues or additional
settings, regardless of whether your project is configured with CommonJS (CJS), ECMAScript Modules (ESM), or any other module format.
To run a script with bun, use the following command:
bun src/index.tsIf you don’t have bun installed, check the Bun installation docs