Fix: Astro DB Not Working — Tables Not Found, Queries Failing, or Seed Data Missing

The Problem

Queries return “table not found”:

import { db, Posts } from 'astro:db';

const posts = await db.select().from(Posts);
// Error: table "Posts" does not exist

Or seed data doesn’t appear:

astro dev
# Database created but tables are empty

Or pushing to remote fails:

astro db push --remote
# Error: Authentication required — or —
# Error: Schema mismatch

Why This Happens

Astro DB is Astro’s built-in database powered by libSQL (SQLite-compatible). It has a local development database and an optional remote database via Astro Studio:

• The schema must be defined in db/config.ts — Astro DB uses a declarative schema file. Tables aren’t created from SQL — they’re generated from the TypeScript config.
• Seed data goes in db/seed.ts — this file runs automatically in development to populate tables. Without it, tables exist but are empty.
• Local and remote are separate databases — astro dev uses a local SQLite file. astro db push --remote syncs the schema to Astro Studio’s hosted database. Data doesn’t sync between them.
• Schema changes require re-syncing — adding or modifying columns requires running astro db push (remote) or restarting the dev server (local).

Fix 1: Define Schema

// db/config.ts — database schema definition
import { defineDb, defineTable, column, NOW } from 'astro:db';

const Posts = defineTable({
  columns: {
    id: column.number({ primaryKey: true, autoIncrement: true }),
    title: column.text(),
    slug: column.text({ unique: true }),
    body: column.text(),
    excerpt: column.text({ optional: true }),
    published: column.boolean({ default: false }),
    authorId: column.number({ references: () => Authors.columns.id }),
    createdAt: column.date({ default: NOW }),
    updatedAt: column.date({ default: NOW }),
  },
});

const Authors = defineTable({
  columns: {
    id: column.number({ primaryKey: true, autoIncrement: true }),
    name: column.text(),
    email: column.text({ unique: true }),
    bio: column.text({ optional: true }),
    avatar: column.text({ optional: true }),
  },
});

const Tags = defineTable({
  columns: {
    id: column.number({ primaryKey: true, autoIncrement: true }),
    name: column.text({ unique: true }),
    slug: column.text({ unique: true }),
  },
});

const PostTags = defineTable({
  columns: {
    postId: column.number({ references: () => Posts.columns.id }),
    tagId: column.number({ references: () => Tags.columns.id }),
  },
});

const Comments = defineTable({
  columns: {
    id: column.number({ primaryKey: true, autoIncrement: true }),
    postId: column.number({ references: () => Posts.columns.id }),
    authorName: column.text(),
    authorEmail: column.text(),
    body: column.text(),
    approved: column.boolean({ default: false }),
    createdAt: column.date({ default: NOW }),
  },
});

export default defineDb({
  tables: { Posts, Authors, Tags, PostTags, Comments },
});

Fix 2: Seed Data

// db/seed.ts — runs on astro dev
import { db, Posts, Authors, Tags, PostTags } from 'astro:db';

export default async function seed() {
  // Insert authors
  await db.insert(Authors).values([
    { id: 1, name: 'Alice Johnson', email: '[email protected]', bio: 'Full-stack developer' },
    { id: 2, name: 'Bob Smith', email: '[email protected]', bio: 'Frontend specialist' },
  ]);

  // Insert tags
  await db.insert(Tags).values([
    { id: 1, name: 'JavaScript', slug: 'javascript' },
    { id: 2, name: 'TypeScript', slug: 'typescript' },
    { id: 3, name: 'React', slug: 'react' },
    { id: 4, name: 'Astro', slug: 'astro' },
  ]);

  // Insert posts
  await db.insert(Posts).values([
    {
      id: 1,
      title: 'Getting Started with Astro DB',
      slug: 'getting-started-astro-db',
      body: 'Astro DB is a built-in database for Astro projects...',
      excerpt: 'Learn how to use Astro DB in your projects',
      published: true,
      authorId: 1,
    },
    {
      id: 2,
      title: 'Building a Blog with Astro',
      slug: 'building-blog-astro',
      body: 'In this tutorial, we build a full-featured blog...',
      excerpt: 'A complete guide to blog development with Astro',
      published: true,
      authorId: 2,
    },
  ]);

  // Insert post-tag relationships
  await db.insert(PostTags).values([
    { postId: 1, tagId: 4 },
    { postId: 1, tagId: 2 },
    { postId: 2, tagId: 4 },
    { postId: 2, tagId: 1 },
  ]);
}

Fix 3: Query Data in Pages

---
// src/pages/blog/index.astro — list all posts
import { db, Posts, Authors, eq, desc } from 'astro:db';

const posts = await db
  .select({
    id: Posts.id,
    title: Posts.title,
    slug: Posts.slug,
    excerpt: Posts.excerpt,
    createdAt: Posts.createdAt,
    authorName: Authors.name,
  })
  .from(Posts)
  .innerJoin(Authors, eq(Posts.authorId, Authors.id))
  .where(eq(Posts.published, true))
  .orderBy(desc(Posts.createdAt));
---

<h1>Blog</h1>
<ul>
  {posts.map(post => (
    <li>
      <a href={`/blog/${post.slug}`}>
        <h2>{post.title}</h2>
        <p>{post.excerpt}</p>
        <span>By {post.authorName} · {post.createdAt.toLocaleDateString()}</span>
      </a>
    </li>
  ))}
</ul>

---
// src/pages/blog/[slug].astro — single post
import { db, Posts, Authors, Comments, eq } from 'astro:db';

const { slug } = Astro.params;

const [post] = await db
  .select()
  .from(Posts)
  .innerJoin(Authors, eq(Posts.authorId, Authors.id))
  .where(eq(Posts.slug, slug!))
  .limit(1);

if (!post) return Astro.redirect('/404');

const comments = await db
  .select()
  .from(Comments)
  .where(eq(Comments.postId, post.Posts.id))
  .where(eq(Comments.approved, true))
  .orderBy(desc(Comments.createdAt));
---

<article>
  <h1>{post.Posts.title}</h1>
  <p>By {post.Authors.name}</p>
  <div set:html={post.Posts.body} />

  <h2>Comments ({comments.length})</h2>
  {comments.map(comment => (
    <div>
      <strong>{comment.authorName}</strong>
      <p>{comment.body}</p>
    </div>
  ))}
</article>

Fix 4: Mutations (Server Endpoints / Actions)

// src/pages/api/comments.ts — API endpoint for adding comments
import type { APIRoute } from 'astro';
import { db, Comments } from 'astro:db';

export const POST: APIRoute = async ({ request }) => {
  const body = await request.json();
  const { postId, authorName, authorEmail, content } = body;

  if (!postId || !authorName || !content) {
    return new Response(JSON.stringify({ error: 'Missing required fields' }), {
      status: 400,
    });
  }

  const [comment] = await db.insert(Comments).values({
    postId,
    authorName,
    authorEmail: authorEmail || '',
    body: content,
    approved: false,  // Require moderation
  }).returning();

  return new Response(JSON.stringify({ success: true, comment }), {
    status: 201,
  });
};

// src/pages/api/posts/[id].ts — update a post
export const PATCH: APIRoute = async ({ params, request }) => {
  const { id } = params;
  const body = await request.json();

  await db.update(Posts)
    .set({ ...body, updatedAt: new Date() })
    .where(eq(Posts.id, Number(id)));

  return new Response(JSON.stringify({ success: true }));
};

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

Fix 5: Astro Studio (Remote Database)

# Link to Astro Studio
npx astro login
npx astro link

# Push schema to remote
astro db push --remote

# Verify remote connection
astro db verify --remote

# Use remote in production
# Set ASTRO_DATABASE_FILE or ASTRO_STUDIO_APP_TOKEN env var

// astro.config.mjs — enable remote DB
import { defineConfig } from 'astro/config';
import db from '@astrojs/db';

export default defineConfig({
  integrations: [db()],
  output: 'server',  // Required for mutations
});

# Environment variables for production
ASTRO_STUDIO_APP_TOKEN=your-studio-token
# Or self-hosted libSQL:
ASTRO_DB_REMOTE_URL=libsql://your-db.turso.io
ASTRO_DB_APP_TOKEN=your-turso-token

Fix 6: Advanced Queries

import { db, Posts, Authors, Tags, PostTags, eq, like, and, or, desc, count, sql } from 'astro:db';

// Full-text search
const results = await db
  .select()
  .from(Posts)
  .where(
    or(
      like(Posts.title, `%${query}%`),
      like(Posts.body, `%${query}%`),
    )
  );

// Aggregation
const stats = await db
  .select({
    authorId: Posts.authorId,
    authorName: Authors.name,
    postCount: count(Posts.id),
  })
  .from(Posts)
  .innerJoin(Authors, eq(Posts.authorId, Authors.id))
  .groupBy(Posts.authorId, Authors.name)
  .orderBy(desc(count(Posts.id)));

// Posts with their tags (many-to-many)
const postsWithTags = await db
  .select({
    postId: Posts.id,
    postTitle: Posts.title,
    tagName: Tags.name,
  })
  .from(Posts)
  .innerJoin(PostTags, eq(Posts.id, PostTags.postId))
  .innerJoin(Tags, eq(PostTags.tagId, Tags.id))
  .where(eq(Posts.published, true));

// Transaction
await db.batch([
  db.insert(Posts).values({ title: 'New Post', slug: 'new-post', body: '...', authorId: 1 }),
  db.update(Authors).set({ bio: 'Updated bio' }).where(eq(Authors.id, 1)),
]);

Still Not Working?

“Table does not exist” — the schema in db/config.ts hasn’t been applied. Restart the dev server (astro dev) — it recreates the local database from the schema. For remote, run astro db push --remote.

Seed data doesn’t appear — db/seed.ts only runs in development mode. Check the file exports a default async function. If seed has errors, the dev server may start without seeding — check the terminal for error messages.

Remote push fails — ensure you’re logged in (astro login) and linked to a project (astro link). Schema changes that would lose data (removing columns) may be rejected. Use --force-reset with caution in development.

Queries work locally but fail in production — in production with output: 'server', you need either Astro Studio credentials (ASTRO_STUDIO_APP_TOKEN) or a self-hosted libSQL URL. Without them, the database is empty in production.

For related database and Astro issues, see Fix: Astro Not Working and Fix: Turso Not Working.