Astro DB — Integrated Database for Astro Projects

In this tutorial, you will learn about Astro DB. We cover key concepts, practical examples, and best practices to help you master this topic.

Learn Astro DB: set up a database, define tables with schemas, query and mutate data, and integrate with SSR pages and API endpoints.

In this lesson, you'll configure Astro DB, define table schemas using astro:db, perform CRUD operations, and use the database in server-rendered pages and API routes.

What You'll Learn

How to install Astro DB, define table schemas with columns and relations, insert and query data, and use it in SSR pages and API endpoints.

Why It Matters

Astro DB provides a built-in database layer that integrates seamlessly with your Astro project, eliminating the need for separate backend services or external database setup.

Real-World Use

DodaTech uses Astro DB to store user progress, newsletter subscribers, and tutorial ratings directly within the Astro deployment.

flowchart LR
    A[Define Schema] --> B[Astro DB]
    B --> C[API Endpoints]
    B --> D[SSR Pages]
    C --> E[JSON Responses]
    D --> F[Dynamic Content]
    style B fill:#ff5a03,color:#fff

Setup

Install Astro DB:

npx astro add db

Configure in astro.config.mjs:

import { defineConfig } from "astro/config";
import db from "@astrojs/db";

export default defineConfig({
  integrations: [db()],
});

Defining Tables

Create src/db/config.ts:

import { defineDb, defineTable, column } from "astro:db";

const User = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    name: column.text(),
    email: column.text({ unique: true }),
    createdAt: column.date({ default: new Date() }),
  },
});

const Post = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    title: column.text(),
    content: column.text(),
    authorId: column.number({ references: () => User.columns.id }),
    published: column.boolean({ default: false }),
  },
});

export default defineDb({
  tables: { User, Post },
});

CRUD Operations

Insert data:

import { db, User, Post } from "astro:db";

// Insert a user
const newUser = await db.insert(User).values({
  name: "Alice",
  email: "alice@example.com",
}).returning();

Query data:

// Get all users
const users = await db.select().from(User);

// Get published posts with author
const posts = await db.select()
  .from(Post)
  .where(eq(Post.published, true))
  .leftJoin(User, eq(Post.authorId, User.id));

Update data:

await db.update(User)
  .set({ name: "Alice Updated" })
  .where(eq(User.id, 1));

Delete data:

await db.delete(User).where(eq(User.id, 1));

Using DB in API Endpoints

// src/pages/api/users.ts
import { db, User } from "astro:db";

export async function GET() {
  const users = await db.select().from(User);
  return new Response(JSON.stringify(users), {
    headers: { "Content-Type": "application/json" },
  });
}

export async function POST({ request }) {
  const body = await request.json();
  const user = await db.insert(User).values(body).returning();
  return new Response(JSON.stringify(user), {
    status: 201,
    headers: { "Content-Type": "application/json" },
  });
}

Using DB in SSR Pages

---
import { db, Post } from "astro:db";
import { eq } from "astro:db";

const slug = Astro.params.slug;
const [post] = await db.select()
  .from(Post)
  .where(eq(Post.slug, slug));
---
<h1>{post.title}</h1>
<div>{post.content}</div>

Common Mistakes

1. Forgetting to run astro db push: Schema changes require running npx astro db push to sync the database schema.
2. Not using returning() after inserts: Without .returning(), insert operations return the count of affected rows, not the inserted data.
3. Using reserved column names: Names like id, createdAt, and updatedAt are reserved with specific types.
4. Querying unindexed columns in production: Large tables need indexes. Define indexes in the table schema for performant queries.
5. Exposing database errors to clients: Catch database errors in API endpoints and return sanitized error messages.

Practice Questions

1. What command syncs your schema to the database? Answer: npx astro db push. It applies schema changes to the database.

2. How do you define a table in Astro DB? Answer: Use defineTable() with a columns object defining each column's type and constraints.

3. What does .returning() do on an insert? Answer: It returns the inserted rows instead of just the count. Required for accessing auto-generated IDs and defaults.

4. How do you reference another table's column? Answer: Use column.number({ references: () => OtherTable.columns.id }) for foreign key relationships.

Challenge

Build a blog with Astro DB: define User and Post tables, create API endpoints for CRUD operations, and render a dynamic blog page in SSR mode that queries posts with their authors.

Mini Project

Create a newsletter subscription system: define a Subscriber table with email and confirmed fields, build an API endpoint for signups with validation, and render an admin page showing all subscribers.

FAQ

What's Next

Learn about Astro Images for automatic image optimization and responsive images.