[db] Fix duplicate calls to recreate tables on startup (#10919)

* fix: move recreateTables() to integration hooks

* feat: recreate and seed at load, not in virtual runtime

* feat: eager build db on startup and seed file change

* fix: respect database_file in dbUrl

* chore: remove duplicate recreateTables call

* chore: remove now self-explanatory comments

* fix: remove invalidateModule call for eager loading

* feat: respect seed package paths

* fix: remove duplicate recreateTables() call

* refactor: move recreateTables() to vite-plugin-db

* refactor: move queries.ts from runtime/ to core/

* fix: update test import to core/queries

* refactor: move executeSeedFile to vite-plugin-db

* refactor: extract seeding and recreating to helper fns

* chore: changeset

* chore: revert connectToStudio refactor

* wip: log db url

* fix(test): normalize astro_database_file flag for windows

* Revert "wip: log db url"

This reverts commit 558e2de67a09a611377929b625127c649b8504d6.

* Revert "Revert "wip: log db url""

This reverts commit ffd004e00dff485b7bc5ddde0278dde6ff058b9e.

* fix: correctly resolve relative paths with unit test

* chore: remove unused dbDirPath

Co-authored-by: Chris Swithinbank <swithinbank@gmail.com>

* chore: remove unused import

Co-authored-by: Chris Swithinbank <swithinbank@gmail.com>

* chore: remove unused type

Co-authored-by: Chris Swithinbank <swithinbank@gmail.com>

* fix: remove bad import

* [db] Load seed files with vite dev server (#10941)

* feat: load seed files with full vite dev server

* chore: remove unused export

---------

Co-authored-by: Chris Swithinbank <swithinbank@gmail.com>

14 files changed, 211 insertions, 182 deletions

diff --git a/.changeset/young-pots-brake.md b/.changeset/young-pots-brake.md
new file mode 100644
index 000000000..33b5e7fd6
--- /dev/null
+++ b/.changeset/young-pots-brake.md
@@ -0,0 +1,6 @@
+---
+"@astrojs/db": minor
+---
+
+- Fix duplicate table recreations when you start your dev server.
+- Remove eager re-seeding when updating your seed file in development. Seeding still runs on dev server startup for SQLite inspector tools.
diff --git a/packages/db/src/core/cli/commands/execute/index.ts b/packages/db/src/core/cli/commands/execute/index.ts
index 05a04ad61..b596d4c0d 100644
--- a/packages/db/src/core/cli/commands/execute/index.ts
+++ b/packages/db/src/core/cli/commands/execute/index.ts
@@ -15,7 +15,7 @@ import {
 } from '../../../integration/vite-plugin-db.js';
 import { bundleFile, importBundledFile } from '../../../load-file.js';
 import { getManagedAppTokenOrExit } from '../../../tokens.js';
-import { type DBConfig } from '../../../types.js';
+import type { DBConfig } from '../../../types.js';
 
 export async function cmd({
 	astroConfig,
@@ -51,8 +51,6 @@ export async function cmd({
 		virtualModContents = getLocalVirtualModContents({
 			tables: dbConfig.tables ?? {},
 			root: astroConfig.root,
-			shouldSeed: false,
-			seedFiles: [],
 		});
 	}
 	const { code } = await bundleFile({ virtualModContents, root: astroConfig.root, fileUrl });
diff --git a/packages/db/src/core/cli/commands/shell/index.ts b/packages/db/src/core/cli/commands/shell/index.ts
index 57fb6e959..0c1883cd2 100644
--- a/packages/db/src/core/cli/commands/shell/index.ts
+++ b/packages/db/src/core/cli/commands/shell/index.ts
@@ -9,7 +9,8 @@ import { DB_PATH } from '../../../consts.js';
 import { SHELL_QUERY_MISSING_ERROR } from '../../../errors.js';
 import { getManagedAppTokenOrExit } from '../../../tokens.js';
 import type { DBConfigInput } from '../../../types.js';
-import { getRemoteDatabaseUrl } from '../../../utils.js';
+import { getAstroEnv, getRemoteDatabaseUrl } from '../../../utils.js';
+import { normalizeDatabaseUrl } from '../../../../runtime/index.js';
 
 export async function cmd({
 	flags,
@@ -31,7 +32,12 @@ export async function cmd({
 		await appToken.destroy();
 		console.log(result);
 	} else {
-		const db = createLocalDatabaseClient({ dbUrl: new URL(DB_PATH, astroConfig.root).href });
+		const { ASTRO_DATABASE_FILE } = getAstroEnv();
+		const dbUrl = normalizeDatabaseUrl(
+			ASTRO_DATABASE_FILE,
+			new URL(DB_PATH, astroConfig.root).href
+		);
+		const db = createLocalDatabaseClient({ dbUrl });
 		const result = await db.run(sql.raw(query));
 		console.log(result);
 	}
diff --git a/packages/db/src/core/cli/migration-queries.ts b/packages/db/src/core/cli/migration-queries.ts
index d5fe959cb..94674f9d1 100644
--- a/packages/db/src/core/cli/migration-queries.ts
+++ b/packages/db/src/core/cli/migration-queries.ts
@@ -12,7 +12,7 @@ import {
 	getReferencesConfig,
 	hasDefault,
 	schemaTypeToSqlType,
-} from '../../runtime/queries.js';
+} from '../queries.js';
 import { isSerializedSQL } from '../../runtime/types.js';
 import { safeFetch } from '../../runtime/utils.js';
 import { MIGRATION_VERSION } from '../consts.js';
diff --git a/packages/db/src/core/integration/index.ts b/packages/db/src/core/integration/index.ts
index 2e3c1f2ea..403d1901b 100644
--- a/packages/db/src/core/integration/index.ts
+++ b/packages/db/src/core/integration/index.ts
@@ -4,9 +4,16 @@ import { fileURLToPath } from 'url';
 import type { AstroConfig, AstroIntegration } from 'astro';
 import { mkdir, writeFile } from 'fs/promises';
 import { blue, yellow } from 'kleur/colors';
-import { loadEnv } from 'vite';
+import {
+	createServer,
+	loadEnv,
+	mergeConfig,
+	type HMRPayload,
+	type UserConfig,
+	type ViteDevServer,
+} from 'vite';
 import parseArgs from 'yargs-parser';
-import { SEED_DEV_FILE_NAME } from '../../runtime/queries.js';
+import { SEED_DEV_FILE_NAME } from '../queries.js';
 import { AstroDbError } from '../../runtime/utils.js';
 import { CONFIG_FILE_NAMES, DB_PATH } from '../consts.js';
 import { resolveDbConfig } from '../load-file.js';
@@ -14,14 +21,24 @@ import { type ManagedAppToken, getManagedAppTokenOrExit } from '../tokens.js';
 import { type VitePlugin, getDbDirectoryUrl } from '../utils.js';
 import { fileURLIntegration } from './file-url.js';
 import { typegenInternal } from './typegen.js';
-import { type LateSeedFiles, type LateTables, resolved, vitePluginDb } from './vite-plugin-db.js';
+import {
+	type LateSeedFiles,
+	type LateTables,
+	vitePluginDb,
+	type SeedHandler,
+	resolved,
+} from './vite-plugin-db.js';
 import { vitePluginInjectEnvTs } from './vite-plugin-inject-env-ts.js';
+import { LibsqlError } from '@libsql/client';
+import { EXEC_DEFAULT_EXPORT_ERROR, EXEC_ERROR } from '../errors.js';
 
 function astroDBIntegration(): AstroIntegration {
 	let connectToStudio = false;
 	let configFileDependencies: string[] = [];
 	let root: URL;
 	let appToken: ManagedAppToken | undefined;
+	// Used during production builds to load seed files.
+	let tempViteServer: ViteDevServer | undefined;
 
 	// Make table loading "late" to pass to plugins from `config:setup`,
 	// but load during `config:done` to wait for integrations to settle.
@@ -35,6 +52,13 @@ function astroDBIntegration(): AstroIntegration {
 			throw new Error('[astro:db] INTERNAL Seed files not loaded yet');
 		},
 	};
+	let seedHandler: SeedHandler = {
+		execute: () => {
+			throw new Error('[astro:db] INTERNAL Seed handler not loaded yet');
+		},
+		inProgress: false,
+	};
+
 	let command: 'dev' | 'build' | 'preview';
 	let output: AstroConfig['output'] = 'server';
 	return {
@@ -60,6 +84,7 @@ function astroDBIntegration(): AstroIntegration {
 						root: config.root,
 						srcDir: config.srcDir,
 						output: config.output,
+						seedHandler,
 					});
 				} else {
 					dbPlugin = vitePluginDb({
@@ -69,6 +94,8 @@ function astroDBIntegration(): AstroIntegration {
 						root: config.root,
 						srcDir: config.srcDir,
 						output: config.output,
+						logger,
+						seedHandler,
 					});
 				}
 
@@ -98,6 +125,9 @@ function astroDBIntegration(): AstroIntegration {
 				await typegenInternal({ tables: tables.get() ?? {}, root: config.root });
 			},
 			'astro:server:setup': async ({ server, logger }) => {
+				seedHandler.execute = async (fileUrl) => {
+					await executeSeedFile({ fileUrl, viteServer: server });
+				};
 				const filesToWatch = [
 					...CONFIG_FILE_NAMES.map((c) => new URL(c, getDbDirectoryUrl(root))),
 					...configFileDependencies.map((c) => new URL(c, root)),
@@ -118,46 +148,11 @@ function astroDBIntegration(): AstroIntegration {
 					const localSeedPaths = SEED_DEV_FILE_NAME.map(
 						(name) => new URL(name, getDbDirectoryUrl(root))
 					);
-					let seedInFlight = false;
-					// Load seed file on dev server startup.
+					// Eager load astro:db module on startup
 					if (seedFiles.get().length || localSeedPaths.find((path) => existsSync(path))) {
-						loadSeedModule();
-					}
-					const eagerReloadIntegrationSeedPaths = seedFiles
-						.get()
-						// Map integration seed paths to URLs, if possible.
-						// Module paths like `@example/seed` will be ignored
-						// from eager reloading.
-						.map((s) => (typeof s === 'string' && s.startsWith('.') ? new URL(s, root) : s))
-						.filter((s): s is URL => s instanceof URL);
-					const eagerReloadSeedPaths = [...eagerReloadIntegrationSeedPaths, ...localSeedPaths];
-					server.watcher.on('all', (event, relativeEntry) => {
-						if (event === 'unlink' || event === 'unlinkDir') return;
-						// When a seed file changes, load manually
-						// to track when seeding finishes and log a message.
-						const entry = new URL(relativeEntry, root);
-						if (eagerReloadSeedPaths.find((path) => entry.href === path.href)) {
-							loadSeedModule();
-						}
-					});
-
-					function loadSeedModule() {
-						if (seedInFlight) return;
-
-						seedInFlight = true;
-						const mod = server.moduleGraph.getModuleById(resolved.seedVirtual);
-						if (mod) server.moduleGraph.invalidateModule(mod);
-						server
-							.ssrLoadModule(resolved.seedVirtual)
-							.then(() => {
-								logger.info('Seeded database.');
-							})
-							.catch((e) => {
-								logger.error(e instanceof Error ? e.message : String(e));
-							})
-							.finally(() => {
-								seedInFlight = false;
-							});
+						server.ssrLoadModule(resolved.module).catch((e) => {
+							logger.error(e instanceof Error ? e.message : String(e));
+						});
 					}
 				}, 100);
 			},
@@ -175,8 +170,15 @@ function astroDBIntegration(): AstroIntegration {
 
 				logger.info('database: ' + (connectToStudio ? yellow('remote') : blue('local database.')));
 			},
+			'astro:build:setup': async ({ vite }) => {
+				tempViteServer = await getTempViteServer({ viteConfig: vite });
+				seedHandler.execute = async (fileUrl) => {
+					await executeSeedFile({ fileUrl, viteServer: tempViteServer! });
+				};
+			},
 			'astro:build:done': async ({}) => {
 				await appToken?.destroy();
+				await tempViteServer?.close();
 			},
 		},
 	};
@@ -190,3 +192,48 @@ function databaseFileEnvDefined() {
 export function integration(): AstroIntegration[] {
 	return [astroDBIntegration(), fileURLIntegration()];
 }
+
+async function executeSeedFile({
+	fileUrl,
+	viteServer,
+}: {
+	fileUrl: URL;
+	viteServer: ViteDevServer;
+}) {
+	const mod = await viteServer.ssrLoadModule(fileUrl.pathname);
+	if (typeof mod.default !== 'function') {
+		throw new AstroDbError(EXEC_DEFAULT_EXPORT_ERROR(fileURLToPath(fileUrl)));
+	}
+	try {
+		await mod.default();
+	} catch (e) {
+		if (e instanceof LibsqlError) {
+			throw new AstroDbError(EXEC_ERROR(e.message));
+		}
+		throw e;
+	}
+}
+
+/**
+ * Inspired by Astro content collection config loader.
+ */
+async function getTempViteServer({ viteConfig }: { viteConfig: UserConfig }) {
+	const tempViteServer = await createServer(
+		mergeConfig(viteConfig, {
+			server: { middlewareMode: true, hmr: false, watch: null },
+			optimizeDeps: { noDiscovery: true },
+			ssr: { external: [] },
+			logLevel: 'silent',
+		})
+	);
+
+	const hotSend = tempViteServer.hot.send;
+	tempViteServer.hot.send = (payload: HMRPayload) => {
+		if (payload.type === 'error') {
+			throw payload.err;
+		}
+		return hotSend(payload);
+	};
+
+	return tempViteServer;
+}
diff --git a/packages/db/src/core/integration/vite-plugin-db.ts b/packages/db/src/core/integration/vite-plugin-db.ts
index df31d5276..865fcd168 100644
--- a/packages/db/src/core/integration/vite-plugin-db.ts
+++ b/packages/db/src/core/integration/vite-plugin-db.ts
@@ -1,16 +1,19 @@
 import { fileURLToPath } from 'node:url';
-import type { AstroConfig } from 'astro';
-import { normalizePath } from 'vite';
-import { SEED_DEV_FILE_NAME } from '../../runtime/queries.js';
+import type { AstroConfig, AstroIntegrationLogger } from 'astro';
+import { SEED_DEV_FILE_NAME, getCreateIndexQueries, getCreateTableQuery } from '../queries.js';
 import { DB_PATH, RUNTIME_IMPORT, RUNTIME_VIRTUAL_IMPORT, VIRTUAL_MODULE_ID } from '../consts.js';
 import type { DBTables } from '../types.js';
-import { type VitePlugin, getDbDirectoryUrl, getRemoteDatabaseUrl } from '../utils.js';
-
-const WITH_SEED_VIRTUAL_MODULE_ID = 'astro:db:seed';
+import { type VitePlugin, getDbDirectoryUrl, getRemoteDatabaseUrl, getAstroEnv } from '../utils.js';
+import { createLocalDatabaseClient } from '../../runtime/db-client.js';
+import { type SQL, sql } from 'drizzle-orm';
+import { existsSync } from 'node:fs';
+import { normalizeDatabaseUrl } from '../../runtime/index.js';
+import { getResolvedFileUrl } from '../load-file.js';
+import { SQLiteAsyncDialect } from 'drizzle-orm/sqlite-core';
 
 export const resolved = {
-	virtual: '\0' + VIRTUAL_MODULE_ID,
-	seedVirtual: '\0' + WITH_SEED_VIRTUAL_MODULE_ID,
+	module: '\0' + VIRTUAL_MODULE_ID,
+	importedFromSeedFile: '\0' + VIRTUAL_MODULE_ID + ':seed',
 };
 
 export type LateTables = {
@@ -19,6 +22,10 @@ export type LateTables = {
 export type LateSeedFiles = {
 	get: () => Array<string | URL>;
 };
+export type SeedHandler = {
+	inProgress: boolean;
+	execute: (fileUrl: URL) => Promise<void>;
+};
 
 type VitePluginDBParams =
 	| {
@@ -27,7 +34,9 @@ type VitePluginDBParams =
 			seedFiles: LateSeedFiles;
 			srcDir: URL;
 			root: URL;
+			logger?: AstroIntegrationLogger;
 			output: AstroConfig['output'];
+			seedHandler: SeedHandler;
 	  }
 	| {
 			connectToStudio: true;
@@ -36,11 +45,10 @@ type VitePluginDBParams =
 			srcDir: URL;
 			root: URL;
 			output: AstroConfig['output'];
+			seedHandler: SeedHandler;
 	  };
 
 export function vitePluginDb(params: VitePluginDBParams): VitePlugin {
-	const srcDirPath = normalizePath(fileURLToPath(params.srcDir));
-	const dbDirPath = normalizePath(fileURLToPath(getDbDirectoryUrl(params.root)));
 	let command: 'build' | 'serve' = 'build';
 	return {
 		name: 'astro:db',
@@ -48,22 +56,15 @@ export function vitePluginDb(params: VitePluginDBParams): VitePlugin {
 		configResolved(resolvedConfig) {
 			command = resolvedConfig.command;
 		},
-		async resolveId(id, rawImporter) {
+		async resolveId(id) {
 			if (id !== VIRTUAL_MODULE_ID) return;
-			if (params.connectToStudio) return resolved.virtual;
-
-			const importer = rawImporter ? await this.resolve(rawImporter) : null;
-			if (!importer) return resolved.virtual;
-
-			if (importer.id.startsWith(srcDirPath) && !importer.id.startsWith(dbDirPath)) {
-				// Seed only if the importer is in the src directory.
-				// Otherwise, we may get recursive seed calls (ex. import from db/seed.ts).
-				return resolved.seedVirtual;
+			if (params.seedHandler.inProgress) {
+				return resolved.importedFromSeedFile;
 			}
-			return resolved.virtual;
+			return resolved.module;
 		},
 		async load(id) {
-			if (id !== resolved.virtual && id !== resolved.seedVirtual) return;
+			if (id !== resolved.module && id !== resolved.importedFromSeedFile) return;
 
 			if (params.connectToStudio) {
 				return getStudioVirtualModContents({
@@ -73,11 +74,35 @@ export function vitePluginDb(params: VitePluginDBParams): VitePlugin {
 					output: params.output,
 				});
 			}
+
+			// When seeding, we resolved to a different virtual module.
+			// this prevents an infinite loop attempting to rerun seed files.
+			// Short circuit with the module contents in this case.
+			if (id === resolved.importedFromSeedFile) {
+				return getLocalVirtualModContents({
+					root: params.root,
+					tables: params.tables.get(),
+				});
+			}
+
+			await recreateTables(params);
+			const seedFiles = getResolvedSeedFiles(params);
+			for await (const seedFile of seedFiles) {
+				// Use `addWatchFile()` to invalidate the `astro:db` module
+				// when a seed file changes.
+				this.addWatchFile(fileURLToPath(seedFile));
+				if (existsSync(seedFile)) {
+					params.seedHandler.inProgress = true;
+					await params.seedHandler.execute(seedFile);
+				}
+			}
+			if (params.seedHandler.inProgress) {
+				(params.logger ?? console).info('Seeded database.');
+				params.seedHandler.inProgress = false;
+			}
 			return getLocalVirtualModContents({
 				root: params.root,
 				tables: params.tables.get(),
-				seedFiles: params.seedFiles.get(),
-				shouldSeed: id === resolved.seedVirtual,
 			});
 		},
 	};
@@ -90,53 +115,17 @@ export function getConfigVirtualModContents() {
 export function getLocalVirtualModContents({
 	tables,
 	root,
-	seedFiles,
-	shouldSeed,
 }: {
 	tables: DBTables;
-	seedFiles: Array<string | URL>;
 	root: URL;
-	shouldSeed: boolean;
 }) {
-	const userSeedFilePaths = SEED_DEV_FILE_NAME.map(
-		// Format as /db/[name].ts
-		// for Vite import.meta.glob
-		(name) => new URL(name, getDbDirectoryUrl('file:///')).pathname
-	);
-	const resolveId = (id: string) =>
-		id.startsWith('.') ? normalizePath(fileURLToPath(new URL(id, root))) : id;
-	// Use top-level imports to correctly resolve `astro:db` within seed files.
-	// Dynamic imports cause a silent build failure,
-	// potentially because of circular module references.
-	const integrationSeedImportStatements: string[] = [];
-	const integrationSeedImportNames: string[] = [];
-	seedFiles.forEach((pathOrUrl, index) => {
-		const path = typeof pathOrUrl === 'string' ? resolveId(pathOrUrl) : pathOrUrl.pathname;
-		const importName = 'integration_seed_' + index;
-		integrationSeedImportStatements.push(`import ${importName} from ${JSON.stringify(path)};`);
-		integrationSeedImportNames.push(importName);
-	});
-
 	const dbUrl = new URL(DB_PATH, root);
 	return `
 import { asDrizzleTable, createLocalDatabaseClient, normalizeDatabaseUrl } from ${RUNTIME_IMPORT};
-${shouldSeed ? `import { seedLocal } from ${RUNTIME_IMPORT};` : ''}
-${shouldSeed ? integrationSeedImportStatements.join('\n') : ''}
 
 const dbUrl = normalizeDatabaseUrl(import.meta.env.ASTRO_DATABASE_FILE, ${JSON.stringify(dbUrl)});
 export const db = createLocalDatabaseClient({ dbUrl });
 
-${
-	shouldSeed
-		? `await seedLocal({
-	db,
-	tables: ${JSON.stringify(tables)},
-	userSeedGlob: import.meta.glob(${JSON.stringify(userSeedFilePaths)}, { eager: true }),
-	integrationSeedFunctions: [${integrationSeedImportNames.join(',')}],
-});`
-		: ''
-}
-
 export * from ${RUNTIME_VIRTUAL_IMPORT};
 
 ${getStringifiedTableExports(tables)}`;
@@ -194,3 +183,34 @@ function getStringifiedTableExports(tables: DBTables) {
 		)
 		.join('\n');
 }
+
+const sqlite = new SQLiteAsyncDialect();
+
+async function recreateTables({ tables, root }: { tables: LateTables; root: URL }) {
+	const { ASTRO_DATABASE_FILE } = getAstroEnv();
+	const dbUrl = normalizeDatabaseUrl(ASTRO_DATABASE_FILE, new URL(DB_PATH, root).href);
+	const db = createLocalDatabaseClient({ dbUrl });
+	const setupQueries: SQL[] = [];
+	for (const [name, table] of Object.entries(tables.get() ?? {})) {
+		const dropQuery = sql.raw(`DROP TABLE IF EXISTS ${sqlite.escapeName(name)}`);
+		const createQuery = sql.raw(getCreateTableQuery(name, table));
+		const indexQueries = getCreateIndexQueries(name, table);
+		setupQueries.push(dropQuery, createQuery, ...indexQueries.map((s) => sql.raw(s)));
+	}
+	await db.batch([
+		db.run(sql`pragma defer_foreign_keys=true;`),
+		...setupQueries.map((q) => db.run(q)),
+	]);
+}
+
+function getResolvedSeedFiles({
+	root,
+	seedFiles,
+}: {
+	root: URL;
+	seedFiles: LateSeedFiles;
+}) {
+	const localSeedFiles = SEED_DEV_FILE_NAME.map((name) => new URL(name, getDbDirectoryUrl(root)));
+	const integrationSeedFiles = seedFiles.get().map((s) => getResolvedFileUrl(root, s));
+	return [...integrationSeedFiles, ...localSeedFiles];
+}
diff --git a/packages/db/src/core/load-file.ts b/packages/db/src/core/load-file.ts
index 7bc7387c8..dd48df928 100644
--- a/packages/db/src/core/load-file.ts
+++ b/packages/db/src/core/load-file.ts
@@ -10,7 +10,7 @@ import { errorMap } from './integration/error-map.js';
 import { getConfigVirtualModContents } from './integration/vite-plugin-db.js';
 import { dbConfigSchema } from './schemas.js';
 import { type AstroDbIntegration } from './types.js';
-import { getDbDirectoryUrl } from './utils.js';
+import { getAstroEnv, getDbDirectoryUrl } from './utils.js';
 
 const isDbIntegration = (integration: AstroIntegration): integration is AstroDbIntegration =>
 	'astro:db:setup' in integration.hooks;
@@ -85,15 +85,17 @@ async function loadUserConfigFile(
 	return await loadAndBundleDbConfigFile({ root, fileUrl: configFileUrl });
 }
 
-async function loadIntegrationConfigFile(root: URL, filePathOrUrl: string | URL) {
-	let fileUrl: URL;
+export function getResolvedFileUrl(root: URL, filePathOrUrl: string | URL): URL {
 	if (typeof filePathOrUrl === 'string') {
 		const { resolve } = createRequire(root);
 		const resolvedFilePath = resolve(filePathOrUrl);
-		fileUrl = pathToFileURL(resolvedFilePath);
-	} else {
-		fileUrl = filePathOrUrl;
+		return pathToFileURL(resolvedFilePath);
 	}
+	return filePathOrUrl;
+}
+
+async function loadIntegrationConfigFile(root: URL, filePathOrUrl: string | URL) {
+	const fileUrl = getResolvedFileUrl(root, filePathOrUrl);
 	return await loadAndBundleDbConfigFile({ root, fileUrl });
 }
 
@@ -133,6 +135,7 @@ export async function bundleFile({
 	root: URL;
 	virtualModContents: string;
 }) {
+	const { ASTRO_DATABASE_FILE } = getAstroEnv();
 	const result = await esbuild({
 		absWorkingDir: process.cwd(),
 		entryPoints: [fileURLToPath(fileUrl)],
@@ -147,6 +150,7 @@ export async function bundleFile({
 		metafile: true,
 		define: {
 			'import.meta.env.ASTRO_STUDIO_REMOTE_DB_URL': 'undefined',
+			'import.meta.env.ASTRO_DATABASE_FILE': JSON.stringify(ASTRO_DATABASE_FILE ?? ''),
 		},
 		plugins: [
 			{
diff --git a/packages/db/src/runtime/queries.ts b/packages/db/src/core/queries.ts
index f58cb1ce2..ff4d56a5d 100644
--- a/packages/db/src/runtime/queries.ts
+++ b/packages/db/src/core/queries.ts
@@ -10,15 +10,15 @@ import type {
 	JsonColumn,
 	NumberColumn,
 	TextColumn,
-} from '../core/types.js';
+} from './types.js';
 import {
 	FOREIGN_KEY_DNE_ERROR,
 	FOREIGN_KEY_REFERENCES_EMPTY_ERROR,
 	FOREIGN_KEY_REFERENCES_LENGTH_ERROR,
 	REFERENCE_DNE_ERROR,
-} from './errors.js';
-import { hasPrimaryKey } from './index.js';
-import { isSerializedSQL } from './types.js';
+} from '../runtime/errors.js';
+import { hasPrimaryKey } from '../runtime/index.js';
+import { isSerializedSQL } from '../runtime/types.js';
 
 const sqlite = new SQLiteAsyncDialect();
 
diff --git a/packages/db/src/core/utils.ts b/packages/db/src/core/utils.ts
index ebc2547b3..d9f1e0323 100644
--- a/packages/db/src/core/utils.ts
+++ b/packages/db/src/core/utils.ts
@@ -9,6 +9,11 @@ export function getAstroStudioEnv(envMode = ''): Record<`ASTRO_STUDIO_${string}`
 	return env;
 }
 
+export function getAstroEnv(envMode = ''): Record<`ASTRO_${string}`, string> {
+	const env = loadEnv(envMode, process.cwd(), 'ASTRO_');
+	return env;
+}
+
 export function getRemoteDatabaseUrl(): string {
 	const env = getAstroStudioEnv();
 	return env.ASTRO_STUDIO_REMOTE_DB_URL || 'https://db.services.astro.build';
diff --git a/packages/db/src/runtime/index.ts b/packages/db/src/runtime/index.ts
index 544df372a..ba157f01d 100644
--- a/packages/db/src/runtime/index.ts
+++ b/packages/db/src/runtime/index.ts
@@ -14,7 +14,6 @@ import { pathToFileURL } from './utils.js';
 
 export type { Table } from './types.js';
 export { createRemoteDatabaseClient, createLocalDatabaseClient } from './db-client.js';
-export { seedLocal } from './seed-local.js';
 
 export function hasPrimaryKey(column: DBColumn) {
 	return 'primaryKey' in column.schema && !!column.schema.primaryKey;
diff --git a/packages/db/src/runtime/seed-local.ts b/packages/db/src/runtime/seed-local.ts
deleted file mode 100644
index 3fb6c61d0..000000000
--- a/packages/db/src/runtime/seed-local.ts
+++ /dev/null
@@ -1,59 +0,0 @@
-import { LibsqlError } from '@libsql/client';
-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 } from './errors.js';
-import { getCreateIndexQueries, getCreateTableQuery } from './queries.js';
-import { AstroDbError } from './utils.js';
-
-const sqlite = new SQLiteAsyncDialect();
-
-export async function seedLocal({
-	db,
-	tables,
-	// Glob all potential seed files to catch renames and deletions.
-	userSeedGlob,
-	integrationSeedFunctions,
-}: {
-	db: LibSQLDatabase;
-	tables: DBTables;
-	userSeedGlob: Record<string, { default?: () => Promise<void> }>;
-	integrationSeedFunctions: Array<() => Promise<void>>;
-}) {
-	await recreateTables({ db, tables });
-	const seedFunctions: Array<() => Promise<void>> = [];
-	const seedFilePath = Object.keys(userSeedGlob)[0];
-	if (seedFilePath) {
-		const mod = userSeedGlob[seedFilePath];
-		if (!mod.default) throw new AstroDbError(SEED_DEFAULT_EXPORT_ERROR(seedFilePath));
-		seedFunctions.push(mod.default);
-	}
-	for (const seedFn of integrationSeedFunctions) {
-		seedFunctions.push(seedFn);
-	}
-	for (const seed of seedFunctions) {
-		try {
-			await seed();
-		} catch (e) {
-			if (e instanceof LibsqlError) {
-				throw new AstroDbError(`Failed to seed database:\n${e.message}`);
-			}
-			throw e;
-		}
-	}
-}
-
-async function recreateTables({ db, tables }: { db: LibSQLDatabase; tables: DBTables }) {
-	const setupQueries: SQL[] = [];
-	for (const [name, table] of Object.entries(tables)) {
-		const dropQuery = sql.raw(`DROP TABLE IF EXISTS ${sqlite.escapeName(name)}`);
-		const createQuery = sql.raw(getCreateTableQuery(name, table));
-		const indexQueries = getCreateIndexQueries(name, table);
-		setupQueries.push(dropQuery, createQuery, ...indexQueries.map((s) => sql.raw(s)));
-	}
-	await db.batch([
-		db.run(sql`pragma defer_foreign_keys=true;`),
-		...setupQueries.map((q) => db.run(q)),
-	]);
-}
diff --git a/packages/db/src/runtime/utils.ts b/packages/db/src/runtime/utils.ts
index 97ad21ccd..2fe837d8f 100644
--- a/packages/db/src/runtime/utils.ts
+++ b/packages/db/src/runtime/utils.ts
@@ -25,7 +25,7 @@ export class AstroDbError extends AstroError {
 	name = 'Astro DB Error';
 }
 
-export default function slash(path: string) {
+function slash(path: string) {
 	const isExtendedLengthPath = path.startsWith('\\\\?\\');
 
 	if (isExtendedLengthPath) {
diff --git a/packages/db/test/local-prod.test.js b/packages/db/test/local-prod.test.js
index 3a2793392..ff3b2c633 100644
--- a/packages/db/test/local-prod.test.js
+++ b/packages/db/test/local-prod.test.js
@@ -2,6 +2,7 @@ import { fileURLToPath } from 'url';
 import { expect } from 'chai';
 import testAdapter from '../../astro/test/test-adapter.js';
 import { loadFixture } from '../../astro/test/test-utils.js';
+import { relative } from 'path';
 
 describe('astro:db local database', () => {
 	let fixture;
@@ -32,8 +33,10 @@ describe('astro:db local database', () => {
 		});
 	});
 
-	describe('build (not remote) with DATABASE_FILE env (file path)', () => {
-		const prodDbPath = fileURLToPath(new URL('./fixtures/basics/dist/astro.db', import.meta.url));
+	describe('build (not remote) with DATABASE_FILE env (relative file path)', () => {
+		const absoluteFileUrl = new URL('./fixtures/basics/dist/astro.db', import.meta.url);
+		const prodDbPath = relative(process.cwd(), fileURLToPath(absoluteFileUrl));
+
 		before(async () => {
 			process.env.ASTRO_DATABASE_FILE = prodDbPath;
 			await fixture.build();
diff --git a/packages/db/test/test-utils.js b/packages/db/test/test-utils.js
index 2ff59e552..8be80a879 100644
--- a/packages/db/test/test-utils.js
+++ b/packages/db/test/test-utils.js
@@ -3,7 +3,7 @@ import { createClient } from '@libsql/client';
 import { z } from 'zod';
 import { cli } from '../dist/core/cli/index.js';
 import { resolveDbConfig } from '../dist/core/load-file.js';
-import { getCreateIndexQueries, getCreateTableQuery } from '../dist/runtime/queries.js';
+import { getCreateIndexQueries, getCreateTableQuery } from '../dist/core/queries.js';
 
 const singleQuerySchema = z.object({
 	sql: z.string(),