db: Better error messages when querying remote (#10636)

* feat: clear error messages on remote db error

* refactor: use AstroDbError for correct error name

* refactor: errorMessage -> responseMessage

* chore: changeset

* fix: revert seed file change

* fix: format seed errors as AstroDbError

* fix: correctly log eager seed errors

6 files changed, 69 insertions, 32 deletions

diff --git a/.changeset/plenty-lobsters-design.md b/.changeset/plenty-lobsters-design.md
new file mode 100644
index 000000000..5f2a6bb39
--- /dev/null
+++ b/.changeset/plenty-lobsters-design.md
@@ -0,0 +1,5 @@
+---
+"@astrojs/db": patch
+---
+
+Detailed error messages for remote database exceptions.
diff --git a/packages/db/src/core/integration/index.ts b/packages/db/src/core/integration/index.ts
index 21da2f761..986b2f84c 100644
--- a/packages/db/src/core/integration/index.ts
+++ b/packages/db/src/core/integration/index.ts
@@ -2,7 +2,6 @@ import { existsSync } from 'fs';
 import { dirname } from 'path';
 import { fileURLToPath } from 'url';
 import type { AstroConfig, AstroIntegration } from 'astro';
-import { AstroError } from 'astro/errors';
 import { mkdir, writeFile } from 'fs/promises';
 import { blue, yellow } from 'kleur/colors';
 import { loadEnv } from 'vite';
@@ -16,6 +15,7 @@ import { fileURLIntegration } from './file-url.js';
 import { typegenInternal } from './typegen.js';
 import { type LateSeedFiles, type LateTables, resolved, vitePluginDb } from './vite-plugin-db.js';
 import { vitePluginInjectEnvTs } from './vite-plugin-inject-env-ts.js';
+import { AstroDbError } from '../../utils.js';
 
 function astroDBIntegration(): AstroIntegration {
 	let connectToStudio = false;
@@ -145,10 +145,17 @@ function astroDBIntegration(): AstroIntegration {
 						seedInFlight = true;
 						const mod = server.moduleGraph.getModuleById(resolved.seedVirtual);
 						if (mod) server.moduleGraph.invalidateModule(mod);
-						server.ssrLoadModule(resolved.seedVirtual).then(() => {
-							seedInFlight = false;
-							logger.info('Seeded database.');
-						});
+						server
+							.ssrLoadModule(resolved.seedVirtual)
+							.then(() => {
+								logger.info('Seeded database.');
+							})
+							.catch((e) => {
+								logger.error(e instanceof Error ? e.message : String(e));
+							})
+							.finally(() => {
+								seedInFlight = false;
+							});
 					}
 				}, 100);
 			},
@@ -161,7 +168,7 @@ function astroDBIntegration(): AstroIntegration {
 					const message = `Attempting to build without the --remote flag or the ASTRO_DATABASE_FILE environment variable defined. You probably want to pass --remote to astro build.`;
 					const hint =
 						'Learn more connecting to Studio: https://docs.astro.build/en/guides/astro-db/#connect-to-astro-studio';
-					throw new AstroError(message, hint);
+					throw new AstroDbError(message, hint);
 				}
 
 				logger.info('database: ' + (connectToStudio ? yellow('remote') : blue('local database.')));
diff --git a/packages/db/src/runtime/db-client.ts b/packages/db/src/runtime/db-client.ts
index 50e1861f4..4518af174 100644
--- a/packages/db/src/runtime/db-client.ts
+++ b/packages/db/src/runtime/db-client.ts
@@ -5,6 +5,7 @@ import { drizzle as drizzleLibsql } from 'drizzle-orm/libsql';
 import { type SqliteRemoteDatabase, drizzle as drizzleProxy } from 'drizzle-orm/sqlite-proxy';
 import { z } from 'zod';
 import { safeFetch } from './utils.js';
+import { AstroDbError } from '../utils.js';
 
 const isWebContainer = !!process.versions?.webcontainer;
 
@@ -55,10 +56,8 @@ export function createRemoteDatabaseClient(appToken: string, remoteDbURL: string
 					},
 					body: JSON.stringify(requestBody),
 				},
-				(response) => {
-					throw new Error(
-						`Failed to execute query.\nQuery: ${sql}\nFull error: ${response.status} ${response.statusText}`
-					);
+				async (response) => {
+					throw await parseRemoteError(response);
 				}
 			);
 
@@ -67,11 +66,7 @@ export function createRemoteDatabaseClient(appToken: string, remoteDbURL: string
 				const json = await res.json();
 				remoteResult = remoteResultSchema.parse(json);
 			} catch (e) {
-				throw new Error(
-					`Failed to execute query.\nQuery: ${sql}\nFull error: Unexpected JSON response. ${
-						e instanceof Error ? e.message : String(e)
-					}`
-				);
+				throw new AstroDbError(await getUnexpectedResponseMessage(res));
 			}
 
 			if (method === 'run') return remoteResult;
@@ -103,10 +98,8 @@ export function createRemoteDatabaseClient(appToken: string, remoteDbURL: string
 					},
 					body: JSON.stringify(stmts),
 				},
-				(response) => {
-					throw new Error(
-						`Failed to execute batch queries.\nFull error: ${response.status} ${response.statusText}}`
-					);
+				async (response) => {
+					throw await parseRemoteError(response);
 				}
 			);
 
@@ -115,11 +108,7 @@ export function createRemoteDatabaseClient(appToken: string, remoteDbURL: string
 				const json = await res.json();
 				remoteResults = z.array(remoteResultSchema).parse(json);
 			} catch (e) {
-				throw new Error(
-					`Failed to execute batch queries.\nFull error: Unexpected JSON response. ${
-						e instanceof Error ? e.message : String(e)
-					}`
-				);
+				throw new AstroDbError(await getUnexpectedResponseMessage(res));
 			}
 			let results: any[] = [];
 			for (const [idx, rawResult] of remoteResults.entries()) {
@@ -149,3 +138,36 @@ export function createRemoteDatabaseClient(appToken: string, remoteDbURL: string
 	applyTransactionNotSupported(db);
 	return db;
 }
+
+const errorSchema = z.object({
+	success: z.boolean(),
+	error: z.object({
+		code: z.string(),
+		details: z.string().optional(),
+	}),
+});
+
+const KNOWN_ERROR_CODES = {
+	SQL_QUERY_FAILED: 'SQL_QUERY_FAILED',
+};
+
+const getUnexpectedResponseMessage = async (response: Response) =>
+	`Unexpected response from remote database:\n(Status ${response.status}) ${await response.text()}`;
+
+async function parseRemoteError(response: Response): Promise<AstroDbError> {
+	let error;
+	try {
+		error = errorSchema.parse(await response.json()).error;
+	} catch (e) {
+		return new AstroDbError(await getUnexpectedResponseMessage(response));
+	}
+	// Strip LibSQL error prefixes
+	let details =
+		error.details?.replace(/.*SQLite error: /, '') ??
+		`(Code ${error.code}) \nError querying remote database.`;
+	let hint = `See the Astro DB guide for query and push instructions: https://docs.astro.build/en/guides/astro-db/#query-your-database`;
+	if (error.code === KNOWN_ERROR_CODES.SQL_QUERY_FAILED && details.includes('no such table')) {
+		hint = `Did you run \`astro db push\` to push your latest table schemas?`;
+	}
+	return new AstroDbError(details, hint);
+}
diff --git a/packages/db/src/runtime/errors.ts b/packages/db/src/runtime/errors.ts
index 2026e57e9..51febbff4 100644
--- a/packages/db/src/runtime/errors.ts
+++ b/packages/db/src/runtime/errors.ts
@@ -24,10 +24,6 @@ export const REFERENCE_DNE_ERROR = (columnName: string) => {
 	)} references a table that does not exist. Did you apply the referenced table to the \`tables\` object in your db config?`;
 };
 
-export const SEED_ERROR = (error: string) => {
-	return `${red(`Error while seeding database:`)}\n\n${error}`;
-};
-
 export const SEED_DEFAULT_EXPORT_ERROR = (fileName: string) => {
-	return SEED_ERROR(`Missing default function export in ${bold(fileName)}`);
+	return `Missing default function export in ${bold(fileName)}`;
 };
diff --git a/packages/db/src/runtime/seed-local.ts b/packages/db/src/runtime/seed-local.ts
index e4d8064ed..ee6492380 100644
--- a/packages/db/src/runtime/seed-local.ts
+++ b/packages/db/src/runtime/seed-local.ts
@@ -3,8 +3,9 @@ import { type SQL, sql } from 'drizzle-orm';
 import type { LibSQLDatabase } from 'drizzle-orm/libsql';
 import { SQLiteAsyncDialect } from 'drizzle-orm/sqlite-core';
 import { type DBTables } from '../core/types.js';
-import { SEED_DEFAULT_EXPORT_ERROR, SEED_ERROR } from './errors.js';
+import { SEED_DEFAULT_EXPORT_ERROR } from './errors.js';
 import { getCreateIndexQueries, getCreateTableQuery } from './queries.js';
+import { AstroDbError } from '../utils.js';
 
 const sqlite = new SQLiteAsyncDialect();
 
@@ -25,7 +26,7 @@ export async function seedLocal({
 	const seedFilePath = Object.keys(userSeedGlob)[0];
 	if (seedFilePath) {
 		const mod = userSeedGlob[seedFilePath];
-		if (!mod.default) throw new Error(SEED_DEFAULT_EXPORT_ERROR(seedFilePath));
+		if (!mod.default) throw new AstroDbError(SEED_DEFAULT_EXPORT_ERROR(seedFilePath));
 		seedFunctions.push(mod.default);
 	}
 	for (const seedFn of integrationSeedFunctions) {
@@ -36,7 +37,7 @@ export async function seedLocal({
 			await seed();
 		} catch (e) {
 			if (e instanceof LibsqlError) {
-				throw new Error(SEED_ERROR(e.message));
+				throw new AstroDbError(`Failed to seed database:\n${e.message}`);
 			}
 			throw e;
 		}
diff --git a/packages/db/src/utils.ts b/packages/db/src/utils.ts
index 4e1a18685..1e7b00b12 100644
--- a/packages/db/src/utils.ts
+++ b/packages/db/src/utils.ts
@@ -1,2 +1,8 @@
+import { AstroError } from 'astro/errors';
+
 export { defineDbIntegration } from './core/utils.js';
 export { asDrizzleTable } from './runtime/index.js';
+
+export class AstroDbError extends AstroError {
+	name = 'Astro DB Error';
+}