Fix building static sites with Astro DB (#10655)

* Fix building static sites with Astro DB

* Adding a changeset

* Try a different port range

12 files changed, 128 insertions, 3 deletions

diff --git a/.changeset/serious-elephants-push.md b/.changeset/serious-elephants-push.md
new file mode 100644
index 000000000..4721d89f3
--- /dev/null
+++ b/.changeset/serious-elephants-push.md
@@ -0,0 +1,5 @@
+---
+"@astrojs/db": patch
+---
+
+Pass through appToken on static sites with Astro DB
diff --git a/packages/db/src/core/cli/commands/execute/index.ts b/packages/db/src/core/cli/commands/execute/index.ts
index 44a2c44fd..05a04ad61 100644
--- a/packages/db/src/core/cli/commands/execute/index.ts
+++ b/packages/db/src/core/cli/commands/execute/index.ts
@@ -45,6 +45,7 @@ export async function cmd({
 			tables: dbConfig.tables ?? {},
 			appToken: appToken.token,
 			isBuild: false,
+			output: 'server',
 		});
 	} else {
 		virtualModContents = getLocalVirtualModContents({
diff --git a/packages/db/src/core/integration/index.ts b/packages/db/src/core/integration/index.ts
index 5c40c8521..2e3c1f2ea 100644
--- a/packages/db/src/core/integration/index.ts
+++ b/packages/db/src/core/integration/index.ts
@@ -59,6 +59,7 @@ function astroDBIntegration(): AstroIntegration {
 						tables,
 						root: config.root,
 						srcDir: config.srcDir,
+						output: config.output,
 					});
 				} else {
 					dbPlugin = vitePluginDb({
@@ -67,6 +68,7 @@ function astroDBIntegration(): AstroIntegration {
 						seedFiles,
 						root: config.root,
 						srcDir: config.srcDir,
+						output: config.output,
 					});
 				}
 
diff --git a/packages/db/src/core/integration/vite-plugin-db.ts b/packages/db/src/core/integration/vite-plugin-db.ts
index 769070976..9d9d50ca1 100644
--- a/packages/db/src/core/integration/vite-plugin-db.ts
+++ b/packages/db/src/core/integration/vite-plugin-db.ts
@@ -4,6 +4,7 @@ import { SEED_DEV_FILE_NAME } from '../../runtime/queries.js';
 import { DB_PATH, RUNTIME_CONFIG_IMPORT, RUNTIME_IMPORT, VIRTUAL_MODULE_ID } from '../consts.js';
 import type { DBTables } from '../types.js';
 import { type VitePlugin, getDbDirectoryUrl, getRemoteDatabaseUrl } from '../utils.js';
+import type { AstroConfig } from 'astro';
 
 const WITH_SEED_VIRTUAL_MODULE_ID = 'astro:db:seed';
 
@@ -26,6 +27,7 @@ type VitePluginDBParams =
 			seedFiles: LateSeedFiles;
 			srcDir: URL;
 			root: URL;
+			output: AstroConfig['output'];
 	  }
 	| {
 			connectToStudio: true;
@@ -33,6 +35,7 @@ type VitePluginDBParams =
 			appToken: string;
 			srcDir: URL;
 			root: URL;
+			output: AstroConfig['output'];
 	  };
 
 export function vitePluginDb(params: VitePluginDBParams): VitePlugin {
@@ -66,6 +69,7 @@ export function vitePluginDb(params: VitePluginDBParams): VitePlugin {
 					appToken: params.appToken,
 					tables: params.tables.get(),
 					isBuild: command === 'build',
+					output: params.output,
 				});
 			}
 			return getLocalVirtualModContents({
@@ -141,15 +145,22 @@ export function getStudioVirtualModContents({
 	tables,
 	appToken,
 	isBuild,
+	output,
 }: {
 	tables: DBTables;
 	appToken: string;
 	isBuild: boolean;
+	output: AstroConfig['output'];
 }) {
 	function appTokenArg() {
 		if (isBuild) {
-			// In production build, always read the runtime environment variable.
-			return 'process.env.ASTRO_STUDIO_APP_TOKEN';
+			if(output === 'server') {
+				// In production build, always read the runtime environment variable.
+				return 'process.env.ASTRO_STUDIO_APP_TOKEN';
+			} else {
+				// Static mode or prerendering needs the local app token.
+				return `process.env.ASTRO_STUDIO_APP_TOKEN ?? ${JSON.stringify(appToken)}`;
+			}
 		} else {
 			return JSON.stringify(appToken);
 		}
diff --git a/packages/db/test/fixtures/static-remote/astro.config.ts b/packages/db/test/fixtures/static-remote/astro.config.ts
new file mode 100644
index 000000000..bd6088769
--- /dev/null
+++ b/packages/db/test/fixtures/static-remote/astro.config.ts
@@ -0,0 +1,6 @@
+import astroDb from '@astrojs/db';
+import { defineConfig } from 'astro/config';
+
+export default defineConfig({
+	integrations: [astroDb()],
+});
diff --git a/packages/db/test/fixtures/static-remote/db/config.ts b/packages/db/test/fixtures/static-remote/db/config.ts
new file mode 100644
index 000000000..8df4674d8
--- /dev/null
+++ b/packages/db/test/fixtures/static-remote/db/config.ts
@@ -0,0 +1,12 @@
+import { column, defineDb, defineTable } from 'astro:db';
+
+const User = defineTable({
+	columns: {
+		id: column.number({ primaryKey: true }),
+		name: column.text(),
+	},
+});
+
+export default defineDb({
+	tables: { User },
+});
diff --git a/packages/db/test/fixtures/static-remote/db/seed.ts b/packages/db/test/fixtures/static-remote/db/seed.ts
new file mode 100644
index 000000000..7d88d1ec4
--- /dev/null
+++ b/packages/db/test/fixtures/static-remote/db/seed.ts
@@ -0,0 +1,9 @@
+import { User, db } from 'astro:db';
+
+export default async function () {
+	await db.insert(User).values([
+		{
+			name: 'Houston'
+		}
+	]);
+}
diff --git a/packages/db/test/fixtures/static-remote/package.json b/packages/db/test/fixtures/static-remote/package.json
new file mode 100644
index 000000000..aa2c9c23c
--- /dev/null
+++ b/packages/db/test/fixtures/static-remote/package.json
@@ -0,0 +1,16 @@
+{
+  "name": "@test/db-static-remote",
+  "version": "1.0.0",
+  "description": "",
+  "main": "index.js",
+  "scripts": {
+    "test": "echo \"Error: no test specified\" && exit 1"
+  },
+  "keywords": [],
+  "author": "",
+  "license": "ISC",
+  "dependencies": {
+    "@astrojs/db": "workspace:*",
+    "astro": "workspace:*"
+  }
+}
diff --git a/packages/db/test/fixtures/static-remote/src/pages/index.astro b/packages/db/test/fixtures/static-remote/src/pages/index.astro
new file mode 100644
index 000000000..7956b482c
--- /dev/null
+++ b/packages/db/test/fixtures/static-remote/src/pages/index.astro
@@ -0,0 +1,20 @@
+---
+import { User, db } from 'astro:db';
+
+const users = await db.select().from(User);
+---
+<html>
+	<head>
+		<title>Testing</title>
+	</head>
+	<body>
+		<h1>Testing</h1>
+
+		<h2>Users</h2>
+		<ul>
+			{users.map(user => (
+				<li>{user.name}</li>
+			))}
+		</ul>
+	</body>
+</html>
diff --git a/packages/db/test/static-remote.test.js b/packages/db/test/static-remote.test.js
new file mode 100644
index 000000000..7adcf6976
--- /dev/null
+++ b/packages/db/test/static-remote.test.js
@@ -0,0 +1,34 @@
+import { expect } from 'chai';
+import { load as cheerioLoad } from 'cheerio';
+import { loadFixture } from '../../astro/test/test-utils.js';
+import { setupRemoteDbServer } from './test-utils.js';
+
+describe('astro:db', () => {
+	let fixture;
+	before(async () => {
+		fixture = await loadFixture({
+			root: new URL('./fixtures/static-remote/', import.meta.url),
+			output: 'static',
+		});
+	});
+
+	describe('static build --remote', () => {
+		let remoteDbServer;
+
+		before(async () => {
+			remoteDbServer = await setupRemoteDbServer(fixture.config);
+			await fixture.build();
+		});
+
+		after(async () => {
+			await remoteDbServer?.stop();
+		});
+
+		it('Can render page', async () => {
+			const html = await fixture.readFile('/index.html');
+			const $ = cheerioLoad(html);
+
+			expect($('li').length).to.equal(1);
+		});
+	});
+});
diff --git a/packages/db/test/test-utils.js b/packages/db/test/test-utils.js
index 860033756..2ff59e552 100644
--- a/packages/db/test/test-utils.js
+++ b/packages/db/test/test-utils.js
@@ -12,7 +12,7 @@ const singleQuerySchema = z.object({
 
 const querySchema = singleQuerySchema.or(z.array(singleQuerySchema));
 
-let portIncrementer = 8081;
+let portIncrementer = 8030;
 
 /**
  * @param {import('astro').AstroConfig} astroConfig
diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml
index 3558c882f..fc37e93b0 100644
--- a/pnpm-lock.yaml
+++ b/pnpm-lock.yaml
@@ -3996,6 +3996,15 @@ importers:
         specifier: workspace:*
         version: link:../../../../astro
 
+  packages/db/test/fixtures/static-remote:
+    dependencies:
+      '@astrojs/db':
+        specifier: workspace:*
+        version: link:../../..
+      astro:
+        specifier: workspace:*
+        version: link:../../../../astro
+
   packages/db/test/fixtures/ticketing-example:
     dependencies:
       '@astrojs/check':