@sqlfx/sql v0.47.0
sqlfx
A SQL toolkit for Effect-TS
https://tim-smart.github.io/sqlfx
Basic example
import { pipe } from "effect/Function"
import * as Config from "effect/Config"
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
const PgLive = Pg.makeLayer({
database: Config.succeed("effect_pg_dev"),
})
const program = Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
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.provideLayer(PgLive), Effect.runPromise)
INSERT resolver
import { pipe } from "effect/Function"
import * as Effect from "effect/Effect"
import * as Schema from "@effect/schema/Schema"
import * as Pg from "@sqlfx/pg"
class Person extends Schema.class({
id: Schema.number,
name: Schema.string,
createdAt: Schema.DateFromSelf,
updatedAt: Schema.DateFromSelf,
}) {}
const InsertPersonSchema = pipe(
Person.schemaStruct(),
Schema.omit("id", "createdAt", "updatedAt"),
)
export const makePersonService = Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const insert = sql.resolver(
"InsertPerson",
InsertPersonSchema,
Person.schema(),
requests =>
sql`
INSERT INTO people
${sql.insert(requests)}
RETURNING people.*
`,
).execute
return { insert }
})
SELECT resolver
import * as Effect from "effect/Effect"
import * as Schema from "@effect/schema/Schema"
import * as Pg from "@sqlfx/pg"
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* _(Pg.tag)
const getByIdResolver = sql.idResolver(
"GetPersonById",
Schema.number,
Person.schema(),
_ => _.id,
ids => sql`SELECT * FROM people WHERE id IN ${sql(ids)}`,
)
const getById = (id: number) =>
Effect.withRequestCaching("on")(getByIdResolver.execute(id))
return { getById }
})
Building queries
Safe interpolation
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
export const make = (limit: number) =>
Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const statement = sql`SELECT * FROM people LIMIT ${limit}`
// e.g. SELECT * FROM people LIMIT ?
})
Unsafe interpolation
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
type OrderBy = "id" | "created_at" | "updated_at"
type SortOrder = "ASC" | "DESC"
export const make = (orderBy: OrderBy, sortOrder: SortOrder) =>
Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const statement = sql`SELECT * FROM people ORDER BY ${sql(orderBy)} ${sql.unsafe(sortOrder)}`
// e.g. SELECT * FROM people ORDER BY `id` ASC
})
Where clause combinators
AND
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
export const make = (names: string[], cursor: string) =>
Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const statement = sql`SELECT * FROM people WHERE ${sql.and([
sql`name IN ${sql(names)}`,
sql`created_at < ${sql(cursor)}`,
])}`
// SELECT * FROM people WHERE (name IN ? AND created_at < ?)
})
OR
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
export const make = (names: string[], cursor: Date) =>
Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const statement = sql`SELECT * FROM people WHERE ${sql.or([
sql`name IN ${sql(names)}`,
sql`created_at < ${sql(cursor)}`,
])}`
// SELECT * FROM people WHERE (name IN ? OR created_at < ?)
})
Mixed
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
export const make = (names: string[], afterCursor: Date, beforeCursor: Date) =>
Effect.gen(function* (_) {
const sql = yield* _(Pg.tag)
const statement = sql`SELECT * FROM people WHERE ${sql.or([
sql`name IN ${sql(names)}`,
sql.and([
`created_at >${sql(afterCursor)}`,
`created_at < ${sql(beforeCursor)}`,
]),
])}`
// SELECT * FROM people WHERE (name IN ? OR (created_at > ? AND created_at < ?))
})
Migrations
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:
// src/migrations/0001_add_users.ts
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
export default Effect.flatMap(
Pg.tag,
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:
// src/main.ts
import * as Effect from "effect/Effect"
import * as Pg from "@sqlfx/pg"
import * as Migrator from "@sqlfx/pg/Migrator"
import * as Config from "effect/Config"
import { fileURLToPath } from "node:url"
import * as Layer from "effect/Layer"
import { pipe } from "effect/Function"
const program = Effect.gen(function* (_) {
// ...
})
const PgLive = Pg.makeLayer({
database: Config.succeed("example_database"),
})
const MigratorLive = Layer.provide(
Migrator.makeLayer({
directory: fileURLToPath(new URL("migrations", import.meta.url)),
// Where to put the `_schema.sql` file
schemaDirectory: "src/migrations",
}),
PgLive,
)
const EnvLive = Layer.mergeAll(PgLive, MigratorLive)
pipe(
program,
Effect.provideLayer(EnvLive),
Effect.tapErrorCause(Effect.logErrorCause),
Effect.runFork,
)
4 days ago
1 month ago
2 months ago
3 months ago
3 months ago
3 months ago
3 months ago
3 months ago
3 months ago
3 months ago
4 months ago
4 months ago
4 months ago
4 months ago
4 months ago
4 months ago
5 months ago
5 months ago
5 months ago
5 months ago
5 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
6 months ago
10 months ago
10 months ago
9 months ago
8 months ago
8 months ago
6 months ago
6 months ago
6 months ago
6 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
10 months ago
10 months ago
10 months ago
10 months ago
7 months ago
7 months ago
8 months ago
5 months ago
5 months ago
10 months ago
10 months ago
10 months ago
9 months ago
10 months ago
9 months ago
8 months ago
10 months ago
6 months ago
6 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
7 months ago
10 months ago
11 months ago
11 months ago
11 months ago
11 months ago
11 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago
12 months ago