The best way to control Claude Code, Codex and more from any computer or phone
Mirrored from https://github.com/Camo67/t3code.git
Synced Nov 21, 04:45 UTC
| 1 | --- |
| 2 | title: libSQL |
| 3 | description: Run SQL on a libSQL database, such as Turso, with Effect SQL. |
| 4 | --- |
| 5 | |
| 6 | import PackageManagers from "../../../../components/PackageManagers.astro"; |
| 7 | |
| 8 | export const deps = `@alchemy.run/sql @effect/sql-libsql`; |
| 9 | |
| 10 | `SQL.LibSQL` runs SQL on a libSQL database, such as [Turso](/turso). Queries are Effects, and failures are `SqlError`s. |
| 11 | |
| 12 | Install `@alchemy.run/sql` and the driver: |
| 13 | |
| 14 | <PackageManagers add={deps} /> |
| 15 | |
| 16 | ## Connect |
| 17 | |
| 18 | Connect a Turso database with `Turso.Connect` and pass it to `SQL.LibSQL`: |
| 19 | |
| 20 | ```typescript |
| 21 | // src/api.ts |
| 22 | import * as Cloudflare from "@alchemy.run/cloudflare"; |
| 23 | import * as SQL from "@alchemy.run/sql/SQLite"; |
| 24 | import * as Turso from "@alchemy.run/turso"; |
| 25 | import * as Effect from "effect/Effect"; |
| 26 | import * as HttpServerResponse from "effect/http/HttpServerResponse"; |
| 27 | import { Db } from "./db.ts"; |
| 28 | |
| 29 | export default class Api extends Cloudflare.Worker<Api>()( |
| 30 | "Api", |
| 31 | { main: import.meta.url }, |
| 32 | Effect.gen(function* () { |
| 33 | const conn = yield* Turso.Connect(Db); |
| 34 | const sql = yield* SQL.LibSQL(conn); |
| 35 | |
| 36 | return { |
| 37 | fetch: Effect.gen(function* () { |
| 38 | const users = yield* sql`SELECT * FROM users`; |
| 39 | return yield* HttpServerResponse.json({ users }); |
| 40 | }), |
| 41 | }; |
| 42 | }).pipe(Effect.provide(Turso.ConnectHttp)), |
| 43 | ) {} |
| 44 | ``` |
| 45 | |
| 46 | The connection opens on the first query and closes when the request ends. See [Connection lifecycle](/sql/effect-sql/lifecycle). |
| 47 | |
| 48 | ## Connect to any libSQL server |
| 49 | |
| 50 | ```typescript |
| 51 | const sql = yield* SQL.LibSQL({ |
| 52 | url: "libsql://my-db.example.com", |
| 53 | authToken: Redacted.make(token), |
| 54 | }); |
| 55 | ``` |
| 56 | |
| 57 | `url` and `authToken` can be plain values or Effects. |
| 58 | |
| 59 | ## Queries |
| 60 | |
| 61 | Values you put in `${}` are sent safely as parameters: |
| 62 | |
| 63 | ```typescript |
| 64 | const user = yield* sql`SELECT * FROM users WHERE id = ${id}`; |
| 65 | |
| 66 | yield* sql`INSERT INTO users ${sql.insert({ name, email })}`; |
| 67 | ``` |
| 68 | |
| 69 | Set the row type with `sql<{ id: number; name: string }>\`...\``. |
| 70 | |
| 71 | ## Transactions |
| 72 | |
| 73 | ```typescript |
| 74 | yield* sql.withTransaction( |
| 75 | Effect.gen(function* () { |
| 76 | yield* sql`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${from}`; |
| 77 | yield* sql`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${to}`; |
| 78 | }), |
| 79 | ); |
| 80 | ``` |
| 81 | |
| 82 | If any statement fails, none of the changes are kept. |
| 83 | |
| 84 | ## Errors |
| 85 | |
| 86 | ```typescript |
| 87 | const users = yield* sql`SELECT * FROM users`.pipe( |
| 88 | Effect.catchTag("SqlError", () => Effect.succeed([])), |
| 89 | ); |
| 90 | ``` |
| 91 | |
| 92 | ## Provide as a service |
| 93 | |
| 94 | Write code against the generic `SqlClient`, then give it the database with `SQL.LibSQLLayer`: |
| 95 | |
| 96 | ```typescript |
| 97 | import * as SqlClient from "effect/sql/SqlClient"; |
| 98 | |
| 99 | const makeUsers = Effect.gen(function* () { |
| 100 | const sql = yield* SqlClient.SqlClient; |
| 101 | return { |
| 102 | find: (id: number) => sql<User>`SELECT * FROM users WHERE id = ${id}`, |
| 103 | }; |
| 104 | }); |
| 105 | |
| 106 | const users = yield* makeUsers.pipe(Effect.provide(SQL.LibSQLLayer(conn))); |
| 107 | ``` |
| 108 | |
| 109 | ## Where next |
| 110 | |
| 111 | [Turso connections](/turso/connections) shows how to connect a database. [Drizzle: libSQL](/sql/drizzle/libsql) adds typed schemas. [Migrations](/turso/migrations) runs SQL on deploy. |