Astro DB adds a type-safe SQL database to Astro — powered by LibSQL in development and Astro Studio (or self-hosted Turso) in production. Tables are defined in db/config.ts with defineTable() and column helpers. db.select(), db.insert(), and db.update() provide a Drizzle-based query API with full TypeScript inference. Astro Actions handle type-safe form mutations — defineAction() with Zod input validation, called from <form> elements or JavaScript with actions.myAction(). Seed files populate the local database during development. Claude Code generates Astro DB table definitions, seed files, server-side queries in .astro pages, Astro Actions, and the Astro Studio configuration for production data-driven Astro applications.
CLAUDE.md for Astro DB Projects
## Astro DB Stack
- Version: astro >= 4.5, @astrojs/db >= 0.14
- Tables: define in db/config.ts with defineTable({ columns: { ... } })
- Queries: import { db, Table } from 'astro:db' — db.select().from(Table)
- Seed: db/seed.ts for local dev data — runs with astro dev
- Actions: define in src/actions/index.ts — export const server = { myAction: defineAction(...) }
- Drizzle: full drizzle-orm API works — eq(), and(), like(), orderBy()
- Production: astro db push (Astro Studio) or ASTRO_DB_REMOTE_URL (Turso/LibSQL)
- Migrations: astro db push --remote applies schema changes to remoteTable Definitions
// db/config.ts — Astro DB schema
import { defineDb, defineTable, column, sql } from "astro:db"
const Orders = defineTable({
columns: {
id: column.text({ primaryKey: true }),
customerId: column.text({ references: () => Users.columns.id }),
status: column.text({ default: "pending" }),
totalCents: column.number(),
items: column.json(),
trackingNumber: column.text({ optional: true }),
createdAt: column.date({ default: sql`CURRENT_TIMESTAMP` }),
updatedAt: column.date({ default: sql`CURRENT_TIMESTAMP` }),
},
indexes: [
{ on: ["customerId"], unique: false },
{ on: ["status"], unique: false },
{ on: ["createdAt"], unique: false },
],
})
const Users = defineTable({
columns: {
id: column.text({ primaryKey: true }),
email: column.text({ unique: true }),
name: column.text(),
role: column.text({ default: "customer" }),
createdAt: column.date({ default: sql`CURRENT_TIMESTAMP` }),
},
})
const Products = defineTable({
columns: {
id: column.text({ primaryKey: true }),
name: column.text(),
slug: column.text({ unique: true }),
description: column.text({ optional: true }),
priceCents: column.number(),
stock: column.number({ default: 0 }),
active: column.boolean({ default: true }),
},
})
export default defineDb({
tables: { Orders, Users, Products }
})Seed File
// db/seed.ts — populate local dev database
import { db, Orders, Users, Products } from "astro:db"
export default async function () {
// Seed users
await db.insert(Users).values([
{ id: "user-1", email: "[email protected]", name: "Alice Smith", role: "customer" },
{ id: "user-2", email: "[email protected]", name: "Bob Jones", role: "customer" },
{ id: "admin-1", email: "[email protected]", name: "Admin User", role: "admin" },
])
// Seed products
await db.insert(Products).values([
{ id: "prod-1", name: "Widget Pro", slug: "widget-pro", priceCents: 2999, stock: 100 },
{ id: "prod-2", name: "Widget Basic", slug: "widget-basic", priceCents: 999, stock: 500 },
{ id: "prod-3", name: "Widget Bundle", slug: "widget-bundle", priceCents: 4999, stock: 50 },
])
// Seed orders with JSON items
await db.insert(Orders).values([
{
id: "order-1",
customerId: "user-1",
status: "delivered",
totalCents: 3998,
items: [
{ productId: "prod-1", name: "Widget Pro", quantity: 1, priceCents: 2999 },
{ productId: "prod-2", name: "Widget Basic", quantity: 1, priceCents: 999 },
],
},
{
id: "order-2",
customerId: "user-1",
status: "pending",
totalCents: 4999,
items: [
{ productId: "prod-3", name: "Widget Bundle", quantity: 1, priceCents: 4999 },
],
},
{
id: "order-3",
customerId: "user-2",
status: "shipped",
totalCents: 5998,
trackingNumber: "1Z999AA10123456784",
items: [
{ productId: "prod-1", name: "Widget Pro", quantity: 2, priceCents: 2999 },
],
},
])
}Queries in Astro Pages
---
// src/pages/orders/index.astro — server-side Astro DB query
import { db, Orders, Users, and, eq, desc, like } from "astro:db"
import Layout from "../../layouts/Layout.astro"
import { actions } from "astro:actions"
const session = Astro.locals.session
if (!session?.userId) return Astro.redirect("/login")
const status = Astro.url.searchParams.get("status")
const search = Astro.url.searchParams.get("search")
// Build query with optional filters
let ordersQuery = db
.select({
orderId: Orders.id,
status: Orders.status,
totalCents: Orders.totalCents,
items: Orders.items,
createdAt: Orders.createdAt,
customerName: Users.name,
customerEmail: Users.email,
})
.from(Orders)
.innerJoin(Users, eq(Orders.customerId, Users.id))
.where(eq(Orders.customerId, session.userId))
.orderBy(desc(Orders.createdAt))
if (status) {
ordersQuery = ordersQuery.where(
and(
eq(Orders.customerId, session.userId),
eq(Orders.status, status)
)
)
}
const orders = await ordersQuery.limit(50)
const totalSpent = orders.reduce((sum, o) => sum + o.totalCents, 0)
---
<Layout title="My Orders">
<div class="orders-page">
<h1>Orders</h1>
<p>Total spent: ${(totalSpent / 100).toFixed(2)}</p>
<div class="filters">
<a href="?status=pending">Pending</a>
<a href="?status=shipped">Shipped</a>
<a href="/orders">All</a>
</div>
{orders.map(order => (
<div class="order-card" key={order.orderId}>
<span>#{order.orderId.slice(-8)}</span>
<span>{order.status}</span>
<span>${(order.totalCents / 100).toFixed(2)}</span>
{order.status === "pending" && (
<form method="POST" action={actions.cancelOrder}>
<input type="hidden" name="orderId" value={order.orderId} />
<button type="submit">Cancel</button>
</form>
)}
</div>
))}
</div>
</Layout>Astro Actions
// src/actions/index.ts — type-safe server mutations
import { defineAction, ActionError } from "astro:actions"
import { z } from "astro:schema" // or zod
import { db, Orders, eq } from "astro:db"
export const server = {
cancelOrder: defineAction({
accept: "form", // Works with native <form> POST
input: z.object({
orderId: z.string().min(1),
}),
handler: async ({ orderId }, context) => {
const session = context.locals.session
if (!session?.userId) {
throw new ActionError({ code: "UNAUTHORIZED" })
}
// Verify ownership
const [order] = await db
.select()
.from(Orders)
.where(eq(Orders.id, orderId))
.limit(1)
if (!order) {
throw new ActionError({ code: "NOT_FOUND", message: "Order not found" })
}
if (order.customerId !== session.userId) {
throw new ActionError({ code: "FORBIDDEN" })
}
if (order.status !== "pending") {
throw new ActionError({
code: "BAD_REQUEST",
message: `Cannot cancel order with status: ${order.status}`,
})
}
await db
.update(Orders)
.set({ status: "cancelled", updatedAt: new Date() })
.where(eq(Orders.id, orderId))
return { cancelled: true, orderId }
},
}),
createOrder: defineAction({
accept: "json",
input: z.object({
items: z.array(z.object({
productId: z.string(),
quantity: z.number().int().positive(),
})).min(1),
}),
handler: async ({ items }, context) => {
const session = context.locals.session
if (!session?.userId) throw new ActionError({ code: "UNAUTHORIZED" })
// Compute total from DB prices
const productIds = items.map(i => i.productId)
const products = await db
.select()
.from(Products)
.where(inArray(Products.id, productIds))
const totalCents = items.reduce((sum, item) => {
const product = products.find(p => p.id === item.productId)
if (!product) throw new ActionError({ code: "BAD_REQUEST", message: `Product ${item.productId} not found` })
return sum + product.priceCents * item.quantity
}, 0)
const orderId = crypto.randomUUID()
await db.insert(Orders).values({
id: orderId,
customerId: session.userId,
status: "pending",
totalCents,
items: items.map(item => ({
...item,
name: products.find(p => p.id === item.productId)!.name,
priceCents: products.find(p => p.id === item.productId)!.priceCents,
})),
})
return { orderId }
},
}),
}For the standalone Drizzle ORM + Turso setup that provides the same LibSQL queries outside of Astro’s framework constraints when building APIs rather than content sites, see the Turso guide for libSQL client configuration. For the SvelteKit equivalent with its own integrated form actions and database patterns using Drizzle, the SvelteKit guide covers load functions and form actions. The Claude Skills 360 bundle includes Astro DB skill sets covering table definitions, seed files, and Actions. Start with the free tier to try Astro DB schema generation.