Astro JS - Astro DB

---

---

What is Astro DB?

Astro DB is a fully managed SQL database integrated into the Astro ecosystem. It automatically handles database schema definitions and migrations, which reduces manual work and speeds up the development process. Astro DB is built on top of SQLite, a lightweight and fast SQL database engine. Developers can work with a local SQLite database during development, which can be easily connected to a hosted SQL database in production.

Features of Astro DB

Astro DB offers several features, such as:

• Automated Schema Management: Astro DB handles database schema definitions and migrations automatically, reducing manual intervention and potential error
• Integration with Astro: Astro DB is built to work effortlessly within the Astro ecosystem. It provide a unified development experience
• Local and Hosted Development: Developers can work with a local SQLite database during development, which can be seamlessly connected to a hosted SQL database in production

Astro DB Technical Architecture

Astro DB uses libSQL and Turso to provide a scalable database management solution:

• libSQL: An open-source, cloud-native SQL database that extends SQLite capabilities, offering enhanced performance and scalability. libSQL is the core database engine for Astro DB.
• Turso: A platform built on libSQL, Turso powers Astro DB's database abstraction, simplifying content management within Astro projects. Turso provides a unified interface for managing database operations.

Getting Started with Astro DB

To get started with Astro DB, follow these steps:

Step 1: Install Astro DB Integration

After setting up your Astro project, install the Astro DB integration using astro add command. This command will add the necessary dependencies and configurations to your project.

>> npx astro add db

Step 2: Define Database Schema

The astro add command will automatically create a db/config.ts file in your project. This file contains the database configuration settings. Configure your database tables in the db/config.ts file using the defineTable function:

// File - db/config.ts

import { defineDb } from 'astro:db';

export default defineDb({
  tables: { },
})

Step 3: Create Database Tables

Define your database tables using the defineTable function within the tables object. You can specify the table name, columns, and constraints in the defineTable function.

// File - db/config.ts

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

const Comment = defineTable({
  columns: {
    author: column.text(),
    body: column.text(),
  }
})

export default defineDb({
  tables: { Comment },
})

Query The Database

You can query your database from any Astro page, endpoint, or action in your project using the provided db ORM and query builder. Astro DB includes a built-in Drizzle ORM client. There is no setup or manual configuration required to use the client. The Astro DB db client is automatically configured to communicate with your database (local or remote) when you run Astro.

Example - Select Query

The example below selects all rows of a Comment table. This returns the complete array of seeded development data from db/seed.ts which is then available for use in your page template:

---
import { db, Comment } from 'astro:db';

const comments = await db.select().from(Comment);
---

<h2>Comments</h2>

{
  comments.map(({ author, body }) => (
    <article>
      <p>Author: {author}</p>
      <p>{body}</p>
    </article>
  ))
}

Example - Insert Query

Insert query is used to add new data from user input to the database. To accept user input, such as handling form requests and inserting data into your remote hosted database, configure your Astro project for on-demand rendering and add an adapter for your deployment environment. This example inserts a row into a Comment table based on a parsed form POST request:

---
import { db, Comment } from 'astro:db';

if (Astro.request.method === 'POST') {
    // Parse form data
    const formData = await Astro.request.formData();
    const author = formData.get('author');
    const body = formData.get('body');
    if (typeof author === 'string' && typeof body === 'string') {
        // Insert form data into the Comment table
        await db.insert(Comment).values({ author, body });
    }
}

// Render the new list of comments on each request
const comments = await db.select().from(Comment);
---

<form method="POST" style="display: grid">
  <label for="author">Author</label>
  <input id="author" name="author" />

  <label for="body">Body</label>
  <textarea id="body" name="body"></textarea>

  <button type="submit">Submit</button>
</form>

<!-- Render `comments` -->

Example - Delete Query

The example below deletes a row from the Comment table based on the comment ID. This example uses a DELETE request to remove a comment from the database:

import type { APIRoute } from "astro";
import { db, Comment, eq } from 'astro:db';

export const DELETE: APIRoute = async (ctx) => {
  await db.delete(Comment).where(eq(Comment.id, ctx.params.id ));
  return new Response(null, { status: 204 });
}