The best way to control Claude Code, Codex and more from any computer or phone
| 1 | --- |
| 2 | title: libSQL |
| 3 | description: Use Drizzle with a libSQL database, such as Turso. |
| 4 | --- |
| 5 | |
| 6 | import PackageManagers from "../../../../components/PackageManagers.astro"; |
| 7 | |
| 8 | export const deps = `@alchemy.run/drizzle drizzle-orm@1.0.0-rc.5 @effect/sql-libsql`; |
| 9 | export const devDeps = `drizzle-kit@1.0.0-rc.5`; |
| 10 | |
| 11 | `Drizzle.LibSQL` gives you typed Drizzle queries on a libSQL database, such as [Turso](/turso). |
| 12 | |
| 13 | Install the packages: |
| 14 | |
| 15 | <PackageManagers add={deps} dev={devDeps} /> |
| 16 | |
| 17 | ## Define the schema |
| 18 | |
| 19 | ```typescript |
| 20 | // src/schema.ts |
| 21 | import { defineRelations } from "drizzle-orm"; |
| 22 | import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core"; |
| 23 | |
| 24 | export const Users = sqliteTable("users", { |
| 25 | id: integer("id").primaryKey({ autoIncrement: true }), |
| 26 | email: text("email").notNull().unique(), |
| 27 | }); |
| 28 | |
| 29 | export const Posts = sqliteTable("posts", { |
| 30 | id: integer("id").primaryKey({ autoIncrement: true }), |
| 31 | userId: integer("user_id") |
| 32 | .notNull() |
| 33 | .references(() => Users.id, { onDelete: "cascade" }), |
| 34 | title: text("title").notNull(), |
| 35 | }); |
| 36 | |
| 37 | export const relations = defineRelations({ Users, Posts }, (t) => ({ |
| 38 | Users: { posts: t.many.Posts() }, |
| 39 | Posts: { user: t.one.Users({ from: t.Posts.userId, to: t.Users.id }) }, |
| 40 | })); |
| 41 | ``` |
| 42 | |
| 43 | ## Make migrations |
| 44 | |
| 45 | ```typescript |
| 46 | // drizzle.config.ts |
| 47 | import { defineConfig } from "drizzle-kit"; |
| 48 | |
| 49 | export default defineConfig({ |
| 50 | schema: "./src/schema.ts", |
| 51 | out: "./migrations", |
| 52 | dialect: "sqlite", |
| 53 | }); |
| 54 | ``` |
| 55 | |
| 56 | ```sh |
| 57 | bunx drizzle-kit generate |
| 58 | git add src/schema.ts drizzle.config.ts migrations |
| 59 | git commit -m "Add database migration" |
| 60 | ``` |
| 61 | |
| 62 | ## Create the database |
| 63 | |
| 64 | ```typescript |
| 65 | // src/db.ts |
| 66 | import * as Turso from "@alchemy.run/turso"; |
| 67 | import * as Effect from "effect/Effect"; |
| 68 | |
| 69 | export const Db = Effect.gen(function* () { |
| 70 | const group = yield* Turso.Group("Group", { location: "aws-us-east-1" }); |
| 71 | return yield* Turso.Database("Db", { |
| 72 | group: group.name, |
| 73 | migrations: "./migrations", |
| 74 | }); |
| 75 | }); |
| 76 | ``` |
| 77 | |
| 78 | Each deploy runs new migrations. See [Turso migrations](/turso/migrations). |
| 79 | |
| 80 | ## Connect in a Worker |
| 81 | |
| 82 | ```typescript |
| 83 | // src/api.ts |
| 84 | import * as Cloudflare from "@alchemy.run/cloudflare"; |
| 85 | import * as Drizzle from "@alchemy.run/drizzle/LibSQL"; |
| 86 | import * as Turso from "@alchemy.run/turso"; |
| 87 | import * as Effect from "effect/Effect"; |
| 88 | import * as HttpServerResponse from "effect/http/HttpServerResponse"; |
| 89 | import { Db } from "./db.ts"; |
| 90 | import { relations, Users } from "./schema.ts"; |
| 91 | |
| 92 | export default class Api extends Cloudflare.Worker<Api>()( |
| 93 | "Api", |
| 94 | { main: import.meta.url }, |
| 95 | Effect.gen(function* () { |
| 96 | const conn = yield* Turso.Connect(Db); |
| 97 | const db = yield* Drizzle.LibSQL(conn, { relations }); |
| 98 | |
| 99 | return { |
| 100 | fetch: Effect.gen(function* () { |
| 101 | const users = yield* db.select().from(Users); |
| 102 | return yield* HttpServerResponse.json({ users }); |
| 103 | }), |
| 104 | }; |
| 105 | }).pipe(Effect.provide(Turso.ConnectHttp)), |
| 106 | ) {} |
| 107 | ``` |
| 108 | |
| 109 | The connection opens on the first query and closes when the request ends. |
| 110 | |
| 111 | ## Queries are Effects |
| 112 | |
| 113 | ```typescript |
| 114 | import { eq } from "drizzle-orm"; |
| 115 | |
| 116 | const [created] = yield* db.insert(Users).values({ email }).returning(); |
| 117 | |
| 118 | const [removed] = yield* db.delete(Users).where(eq(Users.id, id)).returning(); |
| 119 | ``` |
| 120 | |
| 121 | With `relations`, use the `db.query.*` API: |
| 122 | |
| 123 | ```typescript |
| 124 | const user = yield* db.query.Users.findFirst({ |
| 125 | where: { id }, |
| 126 | with: { posts: true }, |
| 127 | }); |
| 128 | ``` |
| 129 | |
| 130 | ## Connect to any libSQL server |
| 131 | |
| 132 | ```typescript |
| 133 | const db = yield* Drizzle.LibSQL({ |
| 134 | url: "libsql://my-db.example.com", |
| 135 | authToken: Redacted.make(token), |
| 136 | }); |
| 137 | ``` |
| 138 | |
| 139 | ## Where next |
| 140 | |
| 141 | [Turso migrations](/turso/migrations#make-migrations-with-drizzle) shows how to make migrations from your schema. The [tutorial](/turso/tutorial/part-1) uses one database per customer. [Effect SQL: libSQL](/sql/effect-sql/libsql) shows plain SQL on the same connection. |