A SQL toolkit for Effect
npm install @effect/sqlA SQL toolkit for Effect.
``ts
import { Effect, Struct, pipe } from "effect"
import { PgClient } from "@effect/sql-pg"
import { SqlClient } from "@effect/sql"
const SqlLive = PgClient.layer({
database: "effect_pg_dev"
})
const program = Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const people = yield* sql<{
readonly id: number
readonly name: string
}>SELECT id, name FROM people
yield* Effect.log(Got ${people.length} results!)
})
pipe(program, Effect.provide(SqlLive), Effect.runPromise)
`
Alternatively, you can also create the SqlLive layer using the PgClient.layerConfig constructor.
`ts
import { Config } from "effect"
import { PgClient } from "@effect/sql-pg"
const SqlLive = PgClient.layerConfig({
database: Config.string("DATABASE")
})
`
If you are coming from the sqlfx package, here are some differences that should be noted:
#### All the modules are now re-exported from the top level for easy access
For example, to create the client Layer, instead of:
`ts
import { Config } from "effect"
import { PgClient } from "@sqlfx/pg"
const SqlLive = PgClient.makeLayer({
database: Config.succeed("effect_pg_dev")
})
`
You now do:
`ts
import { PgClient } from "@effect/sql-pg"
const SqlLive = PgClient.layer({
database: "effect_pg_dev"
})
`
#### The default table name for migrations has changed
To continue using your sqlfx migrations table, you can setup your migrator Layer as below:
`ts
import { PgMigrator } from "@effect/sql-pg"
const MigratorLive = Layer.provide(
PgMigrator.layer({
loader: PgMigrator.fromFileSystem(
fileURLToPath(new URL("migrations", import.meta.url))
),
table: "sqlfx_migrations"
}),
SqlLive
)
`
Or you can rename the sqlfx_migrations table to effect_sql_migrations.
#### The resolver & schema apis have moved
- sql.resolver -> SqlResolver.orderedsql.resolverVoid
- -> SqlResolver.voidsql.resolverId
- -> SqlResolver.findByIdsql.resolverIdMany
- -> SqlResolver.groupedsql.resolverSingle*
- has been removed in favour of using the effect/Cache module with the schema apissql.schema
- -> SqlSchema.findAllsql.schemaSingle
- -> SqlSchema.singlesql.schemaSingleOption
- -> SqlSchema.findOnesql.schemaVoid
- -> SqlSchema.void
#### The array helper has moved
In sqlfx you could pass an array to the sql(array) function to pass an list of items to a SQL IN clause. Now you have to use sql.in(array).
`ts
import { Effect, Schema, pipe } from "effect"
import { SqlResolver, SqlClient } from "@effect/sql"
class Person extends Schema.Class
id: Schema.Number,
name: Schema.String,
createdAt: Schema.DateFromSelf,
updatedAt: Schema.DateFromSelf
}) {}
const InsertPersonSchema = Schema.Struct(
Struct.omit(Person.fields, "id", "createdAt", "updatedAt")
)
export const makePersonService = Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const InsertPerson = yield* SqlResolver.ordered("InsertPerson", {
Request: InsertPersonSchema,
Result: Person,
execute: (requests) =>
sql
INSERT INTO people
${sql.insert(requests)}
RETURNING people.*
})
const insert = InsertPerson.execute
return { insert }
})
`
`ts
import { Effect, Schema, pipe } from "effect"
import { SqlResolver, SqlClient } from "@effect/sql"
class Person extends Schema.Class
id: Schema.Number,
name: Schema.String,
createdAt: Schema.DateFromSelf,
updatedAt: Schema.DateFromSelf
}) {}
export const makePersonService = Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const GetById = yield* SqlResolver.findById("GetPersonById", {
Id: Schema.Number,
Result: Person,
ResultId: (_) => _.id,
execute: (ids) => sqlSELECT * FROM people WHERE ${sql.in("id", ids)}
})
const getById = (id: number) =>
Effect.withRequestCaching("on")(GetById.execute(id))
return { getById }
})
`
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
export const make = (limit: number) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM people LIMIT ${limit}`
// e.g. SELECT * FROM people LIMIT ?
})
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
const table = "people"
export const make = (limit: number) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM ${sql(table)} LIMIT ${limit}`
// e.g. SELECT * FROM "people" LIMIT ?
})
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
type OrderBy = "id" | "created_at" | "updated_at"
type SortOrder = "ASC" | "DESC"
export const make = (orderBy: OrderBy, sortOrder: SortOrder) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM people ORDER BY ${sql(orderBy)} ${sql.unsafe(sortOrder)}id
// e.g. SELECT * FROM people ORDER BY ASC`
})
#### AND
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
export const make = (names: string[], cursor: string) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM people WHERE ${sql.and([
sql.in("name", names),
sqlcreated_at < ${cursor}
])}`
// SELECT * FROM people WHERE ("name" IN (?,?,?) AND created_at < ?)
})
#### OR
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
export const make = (names: string[], cursor: Date) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM people WHERE ${sql.or([
sql.in("name", names),
sqlcreated_at < ${cursor}
])}`
// SELECT * FROM people WHERE ("name" IN (?,?,?) OR created_at < ?)
})
#### Mixed
`ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
export const make = (names: string[], afterCursor: Date, beforeCursor: Date) =>
Effect.gen(function* () {
const sql = yield* SqlClient.SqlClient
const statement = sqlSELECT * FROM people WHERE ${sql.or([
sql.in("name", names),
sql.and([created_at > ${afterCursor}, created_at < ${beforeCursor}])
])}`
// SELECT * FROM people WHERE ("name" IN (?,?,?) OR (created_at > ? AND created_at < ?))
})
A Migrator module is provided, for running migrations.
Migrations are forward-only, and are written in Typescript as Effect's.
Here is an example migration:
`ts
// src/migrations/0001_add_users.ts
import { Effect } from "effect"
import { SqlClient } from "@effect/sql"
export default Effect.flatMap(
SqlClient.SqlClient,
(sql) => sql
CREATE TABLE users (
id serial PRIMARY KEY,
name varchar(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
)
`
)
To run your migrations:
`ts
// src/main.ts
import { Effect, Layer, pipe } from "effect"
import { NodeContext, NodeRuntime } from "@effect/platform-node"
import { PgClient, PgMigrator } from "@effect/sql-pg"
import { fileURLToPath } from "node:url"
const program = Effect.gen(function* () {
// ...
})
const SqlLive = PgClient.layer({
database: "example_database"
})
const MigratorLive = PgMigrator.layer({
loader: PgMigrator.fromFileSystem(
fileURLToPath(new URL("migrations", import.meta.url))
),
// Where to put the _schema.sql file
schemaDirectory: "src/migrations"
}).pipe(Layer.provide(SqlLive))
const EnvLive = Layer.mergeAll(SqlLive, MigratorLive).pipe(
Layer.provide(NodeContext.layer)
)
pipe(program, Effect.provide(EnvLive), NodeRuntime.runMain)
``