import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import Npm from "@mdx/Npm.astro"; import Callout from '@mdx/Callout.astro'; import Steps from '@mdx/Steps.astro'; import AnchorCards from '@mdx/AnchorCards.astro'; import Breadcrumbs from '@mdx/Breadcrumbs.astro'; import Prerequisites from "@mdx/Prerequisites.astro"; import CodeTabs from "@mdx/CodeTabs.astro"; import FileStructure from '@mdx/get-started/FileStructure.mdx'; import InstallPackages from '@mdx/get-started/InstallPackages.mdx'; import ConnectEffect from '@mdx/get-started/postgresql/ConnectEffect.mdx' import CreateTable from '@mdx/get-started/postgresql/CreateTable.mdx' import SetupConfig from '@mdx/get-started/SetupConfig.mdx'; import ApplyChanges from '@mdx/get-started/ApplyChanges.mdx'; import RunFile from '@mdx/get-started/RunFile.mdx'; import QueryDatabase from '@mdx/get-started/QueryDatabase.mdx'; import SetupEnv from '@mdx/get-started/SetupEnv.mdx';

Get Started with Drizzle and Effect PostgreSQL

<Callout type='error'> This page explains concepts available on drizzle versions `1.0.0-beta.13` 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) </Callout>

<Prerequisites> - **Effect** - Effect is a powerful TS library designed to help developers easily create complex, synchronous, and asynchronous programs. - [read more](https://effect.website/docs) - **dotenv** - package for managing environment variables - [read here](https://www.npmjs.com/package/dotenv) - **tsx** - package for running TypeScript files - [read here](https://tsx.is/) - **@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 required packages

Step 2 - Setup connection variables

<SetupEnv env_variable='DATABASE_URL' /> <Callout title='tips'> 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 </Callout>

Step 3 - Connect Drizzle ORM to the database

Step 4 - Create a table

Step 5 - Setup Drizzle config file

Step 6 - Applying changes to the database

Step 7 - Seed and Query the database

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: '[email protected]',
  };

  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)));

Step 8 - Run index.ts file