Effect Typescript SQL Repository

This article is the second installment about Effect-TS. You can find the first part [here].

Recontextualization

In the previous article:

I set up the Bun project.
Installed the necessary dependencies for Effect.
Wrote the initial SQL migration.
Applied it using effect/sql.

The next step is to create a TaskRepository and proceed with testing.

Since this article won’t be too lengthy, I will focus on creating only the insert and getById functions.

TaskRepository

Foreword

I am currently learning how to use Effect, and my code is not perfect. The documentation inspires me mainly. You can refer to the documentation here.

Practice

I’ll be using the generator approach for my code (documentation).

The repository will be split into 3 parts.

Repository Initialization — Part 1

export const InitTaskRepository = Effect.gen(function* (_) {
    const sql = yield* _(Sql.client.PgClient)
    
    //part2
    //par3
    return { insert, getById }
}

We start by using the yield* keyword for dependency injection to obtain the SQL client. To use it, we need to provide it like this.

const insertP = await pipe(insert, Effect.provide(SqlLive), Effect.runPromise)

To conclude, I return the two functions inside an object.

Insert Part 2

I’ve divided the insert operation into two parts. The first part is a SQL resolver provided by the SQL package of Effect.

const InsertPerson = yield* _(
        Sql.resolver.ordered("InsertTask", {
            Request: InsertTaskSchema,
            Result: Task,
            execute: (requests) =>
                sql`
        INSERT INTO tasks ${sql.insert(requests)} RETURNING tasks.*
      `
        })
    )

The intriguing aspect is the second argument of the function. This object contains three fields:

Request: Define the type of data you will insert.
Result: Specifies the type returned by your SQL query.
execute: A function that encapsulates the SQL query execution.

Next, I define the insert function, invoking InsertPerson.execute.

const insert = InsertPerson.execute

Within the test file, we test the insert function like this:

const repository = yield* InitTaskRepository
    const task: TaskInsert = {
        task_name: "test1",
        task_description: "the description",
        status: "open"
    }
    return yield* repository.insert(task)

Part3 get by id

The process for the getById the function is very similar to the insert function. First, I define the GetById resolver.

const GetById = yield* _(
        Sql.resolver.findById("GetPersonById", {
            Id: Schema.UUID,
            Result: Task,
            ResultId: (_) => _.id,
            execute: (ids) => sql`SELECT * FROM tasks WHERE ${sql.in("id", ids)}`
        })
    )

Similar to InsertPerson, GetById is a SQL resolver. In the second parameter, I define a schema with an object containing:

Id: Defines the type of ID used, which is a UUID.
Result: Specifies the type of the result.
ResultId: I'm not entirely sure of its utility.
execute: This function wraps the SQL call.

An interesting feature of the getById function in Effect is that it provides a caching system for the request, though it caches the request rather than the result, if I understand correctly, based on what I've read in Discord.

const getById = (id: string) =>
        Effect.withRequestCaching(true)(GetById.execute(id))

Full code

import { Effect} from "effect"
import * as Sql from "@effect/sql-pg"
import {InsertTaskSchema, Task, TaskInsert, TaskSchema} from "../domain/task.schema.ts";
import {Schema} from "@effect/schema";
import {SqlResolver} from "@effect/sql/Resolver";
import {None, Some} from "effect/Option";
import {ResultLengthMismatch, SqlError} from "@effect/sql/Error";

export const InitTaskRepository = Effect.gen(function* (_) {
    const sql = yield* _(Sql.client.PgClient)    const InsertPerson: SqlResolver<"InsertTask", { [K in keyof TaskInsert]: TaskInsert[K] }, { [K in keyof TaskSchema]: TaskSchema[K] }, ResultLengthMismatch | SqlError, never> = yield* _(
        Sql.resolver.ordered("InsertTask", {
            Request: InsertTaskSchema,
            Result: Task,
            execute: (requests) =>
                sql`
        INSERT INTO tasks ${sql.insert(requests)} RETURNING tasks.*
      `
        })
    )    const GetById: SqlResolver<"GetPersonById", string, None<{ [K in keyof TaskSchema]: TaskSchema[K] }> | Some<{ [K in keyof TaskSchema]: TaskSchema[K] }>, SqlError, never> = yield* _(
        Sql.resolver.findById("GetPersonById", {
            Id: Schema.UUID,
            Result: Task,
            ResultId: (_) => _.id,
            execute: (ids) => sql`SELECT * FROM tasks WHERE ${sql.in("id", ids)}`
        })
    )
    //todo change the type of the
    const getById = (id: string) =>
        Effect.withRequestCaching(true)(GetById.execute(id))    const insert = InsertPerson.execute    return { insert, getById }
})

Conclusion

In the previous articles, we used the Effect SQL package to interact with PostgreSQL and create migrations.

In this article, I share how to create an SQL repository using Effects. My code is here.

In the next article, I will finish building my endpoint using Effect. If you don’t want to miss my future articles, you can subscribe and join my mailing list.