Postgres database with Effect and Drizzle

The postgres database connection combines @effect/sql-pg and @effect/sql-drizzle to export a single Layer configuration.

We define a PgClient from @effect/sql-pg, providing all the required configuration parameters:

import { PgClient } from "@effect/sql-pg";
import { Config } from "effect";

const PgLive = PgClient.layer({
  password: Config.redacted("POSTGRES_PW"),
  username: Config.succeed("postgres"),
  database: Config.succeed("postgres"),
  host: Config.succeed("localhost"),
  port: Config.succeed(5435),
});

Make sure to provide the correct configuration for your local environment. POSTGRES_PW is extracted from the .env file using dotenv.

@effect/sql-drizzle exports a layer that is composed with PgClient to use Drizzle as ORM:

import * as PgDrizzle from "@effect/sql-drizzle/Pg";
import { PgClient } from "@effect/sql-pg";
import { Cause, Config, Console, Layer } from "effect";

const PgLive = PgClient.layer({
  password: Config.redacted("POSTGRES_PW"),
  username: Config.succeed("postgres"),
  database: Config.succeed("postgres"),
  host: Config.succeed("localhost"),
  port: Config.succeed(5435),
});

const DrizzleLive = PgDrizzle.layer.pipe(Layer.provide(PgLive));

The last step is exporting the final Layer configuration that combines PgLive and DrizzleLive:

import * as PgDrizzle from "@effect/sql-drizzle/Pg";
import { PgClient } from "@effect/sql-pg";
import { Cause, Config, Console, Layer } from "effect";

const PgLive = PgClient.layer({
  password: Config.redacted("POSTGRES_PW"),
  username: Config.succeed("postgres"),
  database: Config.succeed("postgres"),
  host: Config.succeed("localhost"),
  port: Config.succeed(5435),
});

const DrizzleLive = PgDrizzle.layer.pipe(Layer.provide(PgLive));

export const DatabaseLive = Layer.mergeAll(PgLive, DrizzleLive);

DatabaseLive is of type Layer.Layer<PgClient.PgClient | SqlClient | PgDrizzle.PgDrizzle, ConfigError | SqlError>.

It provides access to both a postgres client and the Drizzle ORM.

Inside a new schema folder we use Drizzle to define the database tables:

import { relations } from "drizzle-orm";
import { pgEnum, pgTable, varchar } from "drizzle-orm/pg-core";

export const currencyCodeEnum = pgEnum("currencyCode", ["USD", "EUR", "GBP", "JPY", "AUD", "CAD", "CHF", "HKD", "SGD", "SEK", "ARS", "BRL", "CNY", "COP", "CZK", "DKK", "HUF", "ILS", "INR", "KRW", "MXN", "NOK", "NZD", "PLN", "RUB", "THB", "TRY", "TWD", "UAH", "ZAR"]);

export const productTable = pgTable("product", {
  id: varchar({ length: 255 }).notNull().primaryKey(),
  slug: varchar({ length: 255 }).notNull().unique(),
  name: varchar({ length: 255 }).notNull(),
  description: varchar({ length: 255 }),
  imageUrl: varchar({ length: 255 }),
});

export const priceTable = pgTable("price", {
  id: varchar({ length: 255 }).notNull().primaryKey(),
  productId: varchar({ length: 255 }).references(() => productTable.id),
  amount: varchar({ length: 255 }).notNull(),
  currencyCode: currencyCodeEnum().notNull(),
});

export const priceProductRelation = relations(productTable, ({ many }) => ({
  prices: many(priceTable),
}));

We then reference this schema definition inside drizzle.config.ts on the root of the project:

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/schema/drizzle.ts",
  dialect: "postgresql",
  dbCredentials: {
    password: "postgres",
    host: "localhost",
    port: 5435,
    user: "postgres",
    database: "postgres",
    ssl: false,
  },
});

Using this configuration Drizzle will generate schema and migrations inside a new drizzle folder.

We can now use PgDrizzle to execute queries:

export class PaddleApi extends Effect.Service<PaddleApi>()("PaddleApi", {
  succeed: {
    webhook: ({
      paddleSignature,
      payload,
    }: {
      payload: string;
      paddleSignature: string;
    }) =>
      Effect.gen(function* () {
        /// ...
      }),
    getProduct: ({ slug }: { slug: string }) =>
      Effect.gen(function* () {
        const drizzle = yield* PgDrizzle;

        const { price, product } = yield* drizzle
          .select()
          .from(productTable)
          .where(eq(productTable.slug, slug))
          .limit(1)
          .leftJoin(priceTable, eq(productTable.id, priceTable.productId))
          .pipe(
            Effect.flatMap(Array.head),
            Effect.mapError(() => new ErrorInvalidProduct())
          );

        /// ...
      }),
  },
}) {}