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).

Astro DB wraps Drizzle ORM behind the astro:db virtual module. That’s important to understand because most of the runtime errors you’ll see (“table not found,” “column type mismatch,” “missing relation”) are Drizzle errors surfacing through Astro’s abstraction. The schema in db/config.ts is essentially a Drizzle schema, the query builder in your pages is Drizzle’s, and the underlying SQL hits libSQL — either as a local SQLite file during development or a remote libSQL/Turso instance in production. When debugging, treat the layers separately: schema problems live in db/config.ts, query problems are Drizzle, and connectivity problems are libSQL.

The local-versus-remote split is the most confusing aspect for newcomers. During astro dev, you’re talking to a SQLite file in .astro/content.db. That file is recreated from your schema and reseeded every time the dev server starts (in some configurations), so any data you insert via API endpoints during development is throwaway by default. When you run astro db push --remote, only the schema is synchronized to the remote database — the seed file doesn’t run remotely, and your local data isn’t uploaded. Production data has to be inserted via API endpoints or via the Astro Studio UI. Many “data disappeared after deploy” reports trace back to assuming the local database mirrors to remote automatically.

Astro DB Version History

The release timeline directly shapes which advice still applies:

• Astro DB launch (March 2024) — released alongside Astro 4. Introduced the db/config.ts schema, astro:db import, and Astro Studio as the hosted backend. Originally bundled with Astro Studio as the recommended production target.
• Astro DB through mid-2024 — added column.json(), refined the references() API for foreign keys, improved the migration story with astro db push --force-reset for development resets, and tightened TypeScript types on the query builder.
• Astro 5 (November 2024) — shipped the Content Layer API, which overlaps with some Astro DB use cases for static content. Astro DB remained the right choice for dynamic data, but the content collection story shifted.
• Astro Studio shutdown (announced 2025) — Astro Studio, the hosted database service, was deprecated. The recommended production target became self-hosted libSQL via Turso or another libSQL-compatible provider. The astro db push --remote command still works against any libSQL endpoint configured through ASTRO_DB_REMOTE_URL and ASTRO_DB_APP_TOKEN.

The Studio deprecation is the single biggest gotcha. Older tutorials still walk you through astro login and astro link and assume Studio is your production database. Those commands fail or warn on current versions. New projects should provision a Turso (or other libSQL) database, set ASTRO_DB_REMOTE_URL to its libSQL URL, set ASTRO_DB_APP_TOKEN to the auth token, and run astro db push --remote against that. The local development flow is unchanged — only the remote target has migrated.

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 against Astro Studio — Astro Studio was deprecated in 2025. If you’re following an older guide that uses astro login and astro link, switch to a self-hosted libSQL provider like Turso. Set ASTRO_DB_REMOTE_URL and ASTRO_DB_APP_TOKEN in your environment and run astro db push --remote directly against the libSQL endpoint.

Queries work locally but fail in production — in production with output: 'server', you need a libSQL URL and auth token (ASTRO_DB_REMOTE_URL, ASTRO_DB_APP_TOKEN). Without them, the deployed app either falls back to an empty local SQLite file or throws “no database connection.” Check that env vars are set in your hosting provider’s dashboard, not just in .env locally.

column.json() returns a string in queries — libSQL stores JSON as TEXT under the hood. Astro DB serializes on write and deserializes on read for typed access, but if you wrote to the column with raw SQL (or via a different client), the value comes back as a string. Always insert/update through the Astro DB API, or JSON.parse on read.

Schema diff fails with “would lose data” — astro db push --remote refuses destructive changes (dropping columns, narrowing types) to protect production data. Use --force-reset in development only — it wipes the remote database. For production, write a migration that copies data to a new column before dropping the old one.

Drizzle types don’t match query results — the astro:db virtual module re-exports Drizzle types. If autocomplete is wrong after a schema edit, restart the dev server so the virtual module regenerates. Don’t import directly from drizzle-orm in route files — go through astro:db to keep types in sync.

For related database and Astro issues, see Fix: Astro Actions Not Working and Fix: Turso Not Working. The underlying ORM is documented at Fix: Drizzle ORM Not Working. For Astro’s separate static-content system, see Fix: Astro Content Collections Not Working.