Provide guidance when --remote is omitted (#10579)

* Provide guidance when --remote is omitted

* Only restrict to server mode

* Use an AstroError

* Update code

11 files changed, 156 insertions, 2 deletions

diff --git a/.changeset/neat-stingrays-judge.md b/.changeset/neat-stingrays-judge.md
new file mode 100644
index 000000000..d461ca811
--- /dev/null
+++ b/.changeset/neat-stingrays-judge.md
@@ -0,0 +1,7 @@
+---
+"@astrojs/db": patch
+---
+
+Provide guidance when --remote is missing
+
+When running the build `astro build` without the `--remote`, either require a `DATABASE_FILE` variable be defined, which means you are going expert-mode and having your own database, or error suggesting to use the `--remote` flag.
diff --git a/packages/db/src/core/integration/index.ts b/packages/db/src/core/integration/index.ts
index e48b95c09..d288475a5 100644
--- a/packages/db/src/core/integration/index.ts
+++ b/packages/db/src/core/integration/index.ts
@@ -1,7 +1,8 @@
 import { existsSync } from 'fs';
 import { dirname } from 'path';
 import { fileURLToPath } from 'url';
-import type { AstroIntegration } from 'astro';
+import type { AstroConfig, AstroIntegration } from 'astro';
+import { AstroError } from 'astro/errors';
 import { mkdir, writeFile } from 'fs/promises';
 import { blue, yellow } from 'kleur/colors';
 import parseArgs from 'yargs-parser';
@@ -13,6 +14,7 @@ import { fileURLIntegration } from './file-url.js';
 import { typegenInternal } from './typegen.js';
 import { type LateSeedFiles, type LateTables, vitePluginDb } from './vite-plugin-db.js';
 import { vitePluginInjectEnvTs } from './vite-plugin-inject-env-ts.js';
+import { loadEnv } from 'vite';
 
 function astroDBIntegration(): AstroIntegration {
 	let connectToStudio = false;
@@ -33,12 +35,14 @@ function astroDBIntegration(): AstroIntegration {
 		},
 	};
 	let command: 'dev' | 'build' | 'preview';
+	let output: AstroConfig['output'] = 'server';
 	return {
 		name: 'astro:db',
 		hooks: {
 			'astro:config:setup': async ({ updateConfig, config, command: _command, logger }) => {
 				command = _command;
 				root = config.root;
+				output = config.output;
 
 				if (command === 'preview') return;
 
@@ -111,6 +115,12 @@ function astroDBIntegration(): AstroIntegration {
 				});
 			},
 			'astro:build:start': async ({ logger }) => {
+				if(!connectToStudio && !databaseFileEnvDefined() && (output === 'server' || output === 'hybrid')) {
+					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);
+				}
+
 				logger.info('database: ' + (connectToStudio ? yellow('remote') : blue('local database.')));
 			},
 			'astro:build:done': async ({}) => {
@@ -120,6 +130,11 @@ function astroDBIntegration(): AstroIntegration {
 	};
 }
 
+function databaseFileEnvDefined() {
+	const env = loadEnv('', process.cwd());
+	return env.ASTRO_DATABASE_FILE != null || process.env.ASTRO_DATABASE_FILE != null;
+}
+
 export function integration(): AstroIntegration[] {
 	return [astroDBIntegration(), fileURLIntegration()];
 }
diff --git a/packages/db/src/core/integration/vite-plugin-db.ts b/packages/db/src/core/integration/vite-plugin-db.ts
index 60a9b7539..34685e958 100644
--- a/packages/db/src/core/integration/vite-plugin-db.ts
+++ b/packages/db/src/core/integration/vite-plugin-db.ts
@@ -118,7 +118,8 @@ import { asDrizzleTable, createLocalDatabaseClient } from ${RUNTIME_IMPORT};
 ${shouldSeed ? `import { seedLocal } from ${RUNTIME_IMPORT};` : ''}
 ${shouldSeed ? integrationSeedImportStatements.join('\n') : ''}
 
-const dbUrl = ${JSON.stringify(dbUrl)};
+const dbUrl = import.meta.env.ASTRO_DATABASE_FILE ?? ${JSON.stringify(dbUrl)};
+
 export const db = createLocalDatabaseClient({ dbUrl });
 
 ${
diff --git a/packages/db/test/basics.test.js b/packages/db/test/basics.test.js
index 348e9066c..d944b1c48 100644
--- a/packages/db/test/basics.test.js
+++ b/packages/db/test/basics.test.js
@@ -183,6 +183,7 @@ describe('astro:db', () => {
 		});
 
 		after(async () => {
+			process.env.ASTRO_STUDIO_APP_TOKEN = '';
 			await remoteDbServer?.stop();
 		});
 
diff --git a/packages/db/test/fixtures/local-prod/astro.config.ts b/packages/db/test/fixtures/local-prod/astro.config.ts
new file mode 100644
index 000000000..983a6947d
--- /dev/null
+++ b/packages/db/test/fixtures/local-prod/astro.config.ts
@@ -0,0 +1,10 @@
+import db from '@astrojs/db';
+import { defineConfig } from 'astro/config';
+
+// https://astro.build/config
+export default defineConfig({
+	integrations: [db()],
+	devToolbar: {
+		enabled: false,
+	},
+});
diff --git a/packages/db/test/fixtures/local-prod/db/config.ts b/packages/db/test/fixtures/local-prod/db/config.ts
new file mode 100644
index 000000000..44c15abe7
--- /dev/null
+++ b/packages/db/test/fixtures/local-prod/db/config.ts
@@ -0,0 +1,13 @@
+import { column, defineDb, defineTable } from 'astro:db';
+
+const User = defineTable({
+	columns: {
+		id: column.text({ primaryKey: true, optional: false }),
+		username: column.text({ optional: false, unique: true }),
+		password: column.text({ optional: false }),
+	},
+});
+
+export default defineDb({
+	tables: { User },
+});
diff --git a/packages/db/test/fixtures/local-prod/db/seed.ts b/packages/db/test/fixtures/local-prod/db/seed.ts
new file mode 100644
index 000000000..7ff9f5f30
--- /dev/null
+++ b/packages/db/test/fixtures/local-prod/db/seed.ts
@@ -0,0 +1,8 @@
+import { asDrizzleTable } from '@astrojs/db/utils';
+import { User, db } from 'astro:db';
+
+export default async function () {
+	await db.batch([
+		db.insert(User).values([{ id: 'mario', username: 'Mario', password: 'itsame' }]),
+	]);
+}
diff --git a/packages/db/test/fixtures/local-prod/package.json b/packages/db/test/fixtures/local-prod/package.json
new file mode 100644
index 000000000..2d11d5347
--- /dev/null
+++ b/packages/db/test/fixtures/local-prod/package.json
@@ -0,0 +1,14 @@
+{
+  "name": "@test/db-local-prod",
+  "version": "0.0.0",
+  "private": true,
+  "scripts": {
+    "dev": "astro dev",
+    "build": "astro build",
+    "preview": "astro preview"
+  },
+  "dependencies": {
+    "@astrojs/db": "workspace:*",
+    "astro": "workspace:*"
+  }
+}
diff --git a/packages/db/test/fixtures/local-prod/src/pages/index.astro b/packages/db/test/fixtures/local-prod/src/pages/index.astro
new file mode 100644
index 000000000..1e2bdc7b9
--- /dev/null
+++ b/packages/db/test/fixtures/local-prod/src/pages/index.astro
@@ -0,0 +1,11 @@
+---
+/// <reference path="../../.astro/db-types.d.ts" />
+import { db, User } from 'astro:db';
+
+const users = await db.select().from(User);
+---
+
+<h2>Users</h2>
+<ul class="users-list">
+	{users.map((user) => <li>{user.name}</li>)}
+</ul>
diff --git a/packages/db/test/local-prod.test.js b/packages/db/test/local-prod.test.js
new file mode 100644
index 000000000..16682d57a
--- /dev/null
+++ b/packages/db/test/local-prod.test.js
@@ -0,0 +1,65 @@
+import { expect } from 'chai';
+import testAdapter from '../../astro/test/test-adapter.js';
+import { loadFixture } from '../../astro/test/test-utils.js';
+
+describe('astro:db local database', () => {
+	let fixture;
+	before(async () => {
+		fixture = await loadFixture({
+			root: new URL('./fixtures/local-prod/', import.meta.url),
+			output: 'server',
+			adapter: testAdapter(),
+		});
+	});
+
+	describe('build (not remote) with DATABASE_FILE env', () => {
+		const prodDbPath = new URL('./fixtures/basics/dist/astro.db', import.meta.url).toString();
+		before(async () => {
+			process.env.ASTRO_DATABASE_FILE = prodDbPath;
+			await fixture.build();
+		});
+
+		after(async () => {
+			delete process.env.ASTRO_DATABASE_FILE;
+		});
+
+		it('Can render page', async () => {
+			const app = await fixture.loadTestAdapterApp();
+			const request = new Request('http://example.com/');
+			const response = await app.render(request);
+			expect(response.status).to.equal(200);
+		});
+	});
+
+	describe('build (not remote)', () => {
+		it('should throw during the build for server output', async () => {
+			delete process.env.ASTRO_DATABASE_FILE;
+			let buildError = null;
+			try {
+				await fixture.build();
+			} catch(err) {
+				buildError = err;
+			}
+
+			expect(buildError).to.be.an('Error');
+		});
+
+		it('should throw during the build for hybrid output', async () => {
+			let fixture2 = await loadFixture({
+				root: new URL('./fixtures/local-prod/', import.meta.url),
+				output: 'hybrid',
+				adapter: testAdapter(),
+			});
+
+			delete process.env.ASTRO_DATABASE_FILE;
+			let buildError = null;
+			try {
+				await fixture2.build();
+			} catch(err) {
+				buildError = err;
+			}
+
+			expect(buildError).to.be.an('Error');
+		});
+	});
+});
diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml
index 06c90798a..57b8ccc4e 100644
--- a/pnpm-lock.yaml
+++ b/pnpm-lock.yaml
@@ -3948,6 +3948,15 @@ importers:
         specifier: workspace:*
         version: link:../../../../astro
 
+  packages/db/test/fixtures/local-prod:
+    dependencies:
+      '@astrojs/db':
+        specifier: workspace:*
+        version: link:../../..
+      astro:
+        specifier: workspace:*
+        version: link:../../../../astro
+
   packages/db/test/fixtures/no-apptoken:
     dependencies:
       '@astrojs/db':