The Complete Production SOP: How to Deploy and Run Astro DB Embedded SQL Database & Studio Integration in Autonomous AI Agents

---
name: "astro-db-libsql-schema"
description: "Manage lightweight, type-safe SQL databases directly within Astro projects using Astro DB."
metadata:
  author: "Astro"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "setup astro db"
  - "define astro db table"
  - "query astro db drizzle"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

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

export const Comments = defineTable({
  columns: {
    id: column.number({ primaryKey: true }),
    author: column.text(),
    body: column.text(),
    publishedAt: column.date({ default: new Date() }),
  },
});

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

import type { APIRoute } from 'astro';
import { db, Comments, desc } from 'astro:db';

export const GET: APIRoute = async () => {
  const allComments = await db.select().from(Comments).orderBy(desc(Comments.publishedAt));
  return new Response(JSON.stringify(allComments), {
    status: 200,
    headers: { 'Content-Type': 'application/json' },
  });
};