The best way to control Claude Code, Codex and more from any computer or phone

main
mdx 141 lines 3.69 KB
Raw
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.