# Drizzle \<\> Effect Postgres

<Callout type='error'>
This page explains concepts available on drizzle versions `1.0.0-beta.9` and higher.

Effect is only available for PostgreSQL right now and soon be implemented for all other dialects

On how to upgrade (read more [here](/docs/upgrade-v1))
</Callout>

<Prerequisites>
- Database [connection basics](/docs/connect-overview) with Drizzle
- Effect - [website](https://effect.website/docs)
- **@effect/sql-pg** - A PostgreSQL toolkit for Effect - [read here](https://effect-ts.github.io/effect/docs/sql-pg)
</Prerequisites>

Drizzle has native support for Effect PostgreSQL connections with the `@effect/sql-pg` driver.

#### Step 1 - Install packages
<Npm>
drizzle-orm effect @effect/sql-pg pg
-D drizzle-kit @types/pg
</Npm>

#### Step 2 - Initialize the driver and make a query

Drizzle provides an Effect-native API that integrates with Effect's service pattern. Use `PgDrizzle.makeWithDefaults()` 
to quickly create a Drizzle database instance with sensible defaults (no logging, no caching).

```typescript copy
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 { sql } from 'drizzle-orm';
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 (no logging, no caching)
  const db = yield* PgDrizzle.makeWithDefaults();

  // Execute queries
  const result = yield* db.execute<{ id: number }>(sql`SELECT 1 as id`);
  console.log(result);
});

// Run the program with the PgClient layer
Effect.runPromise(program.pipe(Effect.provide(PgClientLive)));
```

#### Step 3 - Create a DB Layer for dependency injection

For larger applications, create a reusable DB layer that can be composed with other services. This follows Effect's 
recommended pattern for dependency injection:

```typescript copy
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { PgClient } from '@effect/sql-pg';
import * as Context from 'effect/Context';
import * as Effect from 'effect/Effect';
import * as Layer from 'effect/Layer';
import * as Redacted from 'effect/Redacted';
import { types } from 'pg';
import * as relations from './schema/relations';

// Configure the PgClient layer
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);
    },
  },
});

// Create the DB effect with default services
const dbEffect = PgDrizzle.make({ relations }).pipe(
  Effect.provide(PgDrizzle.DefaultServices)
);

// Define a DB service tag for dependency injection
class DB extends Context.Tag('DB')<DB, Effect.Effect.Success<typeof dbEffect>>() {}

// Create a layer that provides the DB service
const DBLive = Layer.effect(
  DB,
  Effect.gen(function*() {
    return yield* dbEffect;
  }),
);

// Compose all layers together
const AppLive = Layer.provideMerge(DBLive, PgClientLive);

// Use the DB service in your application
const program = Effect.gen(function*() {
  const db = yield* DB;
  const users = yield* db.select().from(usersTable);
  return users;
});

// Run with all dependencies provided
Effect.runPromise(program.pipe(Effect.provide(AppLive)));
```

#### Advanced: Custom logger and cache configuration

For more control over logging and caching, use `PgDrizzle.make()` directly and provide your own service implementations.

##### Logger configuration

By default, `makeWithDefaults()` uses a no-op logger (no logging). You can enable logging by providing 
a different `EffectLogger` implementation:

```typescript copy
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { EffectLogger } from 'drizzle-orm/effect-postgres';
import * as Effect from 'effect/Effect';

const program = Effect.gen(function*() {
  const db = yield* PgDrizzle.make({ /* schema, relations, casing */ }).pipe(
    // Enable Effect-based logging (uses Effect.log with annotations)
    Effect.provide(EffectLogger.layer),
    // Provide remaining default services (cache)
    Effect.provide(PgDrizzle.DefaultServices),
  );

  const users = yield* db.select().from(usersTable);
  return users;
});
```

**Available logger options:**
- `EffectLogger.Default` - No-op logger (no logging occurs) - this is the default
- `EffectLogger.layer` - Logs queries using Effect's `Effect.log()` with annotations for query SQL and parameters. Integrates with Effect's logging infrastructure.
- `EffectLogger.fromDrizzle(logger)` - Wraps a Drizzle `Logger` instance for use with Effect
- `EffectLogger.layerFromDrizzle(logger)` - Creates an Effect Layer from a Drizzle logger

<Callout type='info'>
When using `EffectLogger.layer`, queries are logged via Effect's logging system. You can configure the output 
format by providing a different Effect logger layer (e.g., `Logger.pretty` for development, `Logger.json` for production).
</Callout>

**Using a Drizzle logger:**

```typescript copy
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { EffectLogger } from 'drizzle-orm/effect-postgres';
import * as Effect from 'effect/Effect';
import { DefaultLogger } from 'drizzle-orm';

const program = Effect.gen(function*() {
  const db = yield* PgDrizzle.make({ /* schema, relations, casing */ }).pipe(
    // Use a Drizzle logger wrapped for Effect
    Effect.provide(EffectLogger.layerFromDrizzle(new DefaultLogger())),
    // Provide remaining default services (cache)
    Effect.provide(PgDrizzle.DefaultServices),
  );

  const users = yield* db.select().from(usersTable);
  return users;
});
```

##### Cache configuration

Similarly, you can provide a custom cache implementation:

```typescript copy
import * as PgDrizzle from 'drizzle-orm/effect-postgres';
import { EffectLogger } from 'drizzle-orm/effect-postgres';
import { EffectCache } from 'drizzle-orm/cache/core/cache-effect';
import * as Effect from 'effect/Effect';
import { MyCustomCache } from './cache';

const program = Effect.gen(function*() {
  const db = yield* PgDrizzle.make({ /* schema, relations, casing */ }).pipe(
    // Provide a custom cache wrapped for Effect
    Effect.provide(EffectCache.layerFromDrizzle(new MyCustomCache())),
    // Provide remaining default services (logger)
    Effect.provide(PgDrizzle.DefaultServices),
  );

  const users = yield* db.select().from(usersTable);
  return users;
});
```

**Available cache options:**
- `EffectCache.Default` - No-op cache (no caching occurs) - this is the default
- `EffectCache.fromDrizzle(cache)` - Wraps a Drizzle `Cache` instance for use with Effect
- `EffectCache.layerFromDrizzle(cache)` - Creates an Effect Layer from a Drizzle cache (useful for composing with other layers)

#### What's next?

<WhatsNextPostgres/>


Source: https://orm.drizzle.team/docs/connect-expo-sqlite

import Npm from "@mdx/Npm.astro";
import Callout from '@mdx/Callout.astro';
import AnchorCards from '@mdx/AnchorCards.astro';
import Steps from '@mdx/Steps.astro';