Astro DB - when using --remote flag I get "Invalid URL"

[thomasledoux1]

Astro Info

Astro                    v4.5.8
Node                     v20.11.0
System                   macOS (arm64)
Package Manager          npm
Output                   hybrid
Adapter                  @astrojs/vercel/serverless
Integrations             @astrojs/tailwind
                         @astrojs/react
                         auto-import
                         @astrojs/code-snippets
                         @astrojs/mdx
                         astro:db
                         @astrojs/db/file-url

If this issue only occurs in one browser, which browser is a problem?

No response

Describe the Bug

When I run "astro dev --remote", I get the following error:

22:06:14 [ERROR] Invalid URL
Stack trace:
at /Users/thomasledoux/Documents/website-thomas-astro/node_modules/@astrojs/db/dist/runtime/db-client.js:31:15
[...] See full stack trace in the browser, or rerun with --verbose.

This seems to be coming from createRemoteDatabaseClient() where the remoteDbURL that's passed to it is not valid apparently.

What's the expected result?

Connecting to the remote Astro DB

Link to Minimal Reproducible Example

https://github.com/thomasledoux1/website-thomas-astro

Participation

• I am willing to submit a pull request for this issue.

[santiagoavilez]

I've managed to replicate the issue in my development environment, and I've identified a workaround that might provide a temporary solution until the root cause is addressed.

It appears that the problem lies in the runtime handling of environment variables, specifically when retrieving credentials using the getRemoteDatabaseUrl() function. Although environment variables set at build time seem to work fine, runtime variables are causing issues.

To circumvent this, I've made a temporary adjustment in the createRemoteDatabaseClient function to hardcode my credentials. This ensures that the function operates correctly in my local setup. However, this is not a sustainable solution for production environments.

The issue seems to stem from discrepancies in how environment variables are handled between build time and runtime. For instance, while the command npx astro db shell --query 'select "*" from Courses' --remote works as expected, npx astro db execute db/seed.ts --remote fails due to the same problem.

In @astrojs/db/dist/core/utils.js, I've observed that the getRemoteDatabaseUrl() function is where the issue originates, particularly in fetching environment variables. I've included a console.log statement to track the ASTRO_STUDIO_APP_TOKEN variable.

In terms of code, I've made modifications in one file:

In @astrojs/db/dist/runtime/db-client.js, I've made changes to ensure that the getRemoteDatabaseUrl() function fetches the correct environment variables. Additionally, I've modified the createRemoteDatabaseClient function to accept hardcoded credentials temporarily.

//@astrojs/db/dist/runtime/db-client.js file
import { createClient } from "@libsql/client";
import { drizzle as drizzleLibsql } from "drizzle-orm/libsql";
import { drizzle as drizzleProxy } from "drizzle-orm/sqlite-proxy";
import { z } from "zod";
import { safeFetch } from "./utils.js";
const isWebContainer = !!process.versions?.webcontainer;
function applyTransactionNotSupported(db) {
  Object.assign(db, {
    transaction() {
      throw new Error(
        "`db.transaction()` is not currently supported. We recommend `db.batch()` for automatic error rollbacks across multiple queries."
      );
    }
  });
}
function createLocalDatabaseClient({ dbUrl }) {
  const url = isWebContainer ? "file:content.db" : dbUrl;
  const client = createClient({ url });
  const db = drizzleLibsql(client);
  applyTransactionNotSupported(db);
  return db;
}
const remoteResultSchema = z.object({
  columns: z.array(z.string()),
  columnTypes: z.array(z.string()),
  rows: z.array(z.array(z.unknown())),
  rowsAffected: z.number(),
  lastInsertRowid: z.unknown().optional()
});

// taken from node_modules/@astrojsdb/dist/core/utils.js
import { loadEnv } from "vite";
function getAstroStudioEnv(envMode = "") {
  const env = loadEnv(envMode, process.cwd(), "ASTRO_STUDIO_");
  return env;
}
// taken from node_modules/@astrojsdb/dist/core/utils.js
function getRemoteDatabaseUrl() {
  const env = getAstroStudioEnv();
  return env.ASTRO_STUDIO_REMOTE_DB_URL || "https://db.services.astro.build";
}
// taken from node_modules/@astrojsdb/dist/core/utils.js
function getAppToken() {
  const env = getAstroStudioEnv();
  return env.ASTRO_STUDIO_APP_TOKEN;
}

function createRemoteDatabaseClient(appToken , remoteDbURL) {
   remoteDbURL = getRemoteDatabaseUrl(); // added this line in order to ensure the remoteDbURL is set
   appToken = getAppToken();  // added this line in order to ensure the appToken is set
  const url = new URL("/db/query", remoteDbURL);
  const db = drizzleProxy(
    async (sql, parameters, method) => {
      const requestBody = { sql, args: parameters };
      const res = await safeFetch(
        url,
        {
          method: "POST",
          headers: {
            Authorization: `Bearer ${appToken}`,
            "Content-Type": "application/json"
          },
          body: JSON.stringify(requestBody)
        },
        (response) => {
          throw new Error(
            `Failed to execute query.
Query: ${sql}
Full error: ${response.status} ${response.statusText}`
          );
        }
      );
      let remoteResult;
      try {
        const json = await res.json();
        remoteResult = remoteResultSchema.parse(json);
        // console.log("Remote result", remoteResult);
      } catch (e) {
        throw new Error(
          `Failed to execute query.
Query: ${sql}
Full error: Unexpected JSON response. ${e instanceof Error ? e.message : String(e)}`
        );
      }
      if (method === "run")
        return remoteResult;
      const rowValues = [];
      for (const row of remoteResult.rows) {
        if (row != null && typeof row === "object") {
          rowValues.push(Object.values(row));
        }
      }
      if (method === "get") {
        return { rows: rowValues[0] };
      }
      return { rows: rowValues };
    },
    async (queries) => {
      const stmts = queries.map(({ sql, params }) => ({ sql, args: params }));
      const res = await safeFetch(
        url,
        {
          method: "POST",
          headers: {
            Authorization: `Bearer ${appToken}`,
            "Content-Type": "application/json"
          },
          body: JSON.stringify(stmts)
        },
        (response) => {
          throw new Error(
            `Failed to execute batch queries.
Full error: ${response.status} ${response.statusText}}`
          );
        }
      );
      let remoteResults;
      try {
        const json = await res.json();
        remoteResults = z.array(remoteResultSchema).parse(json);
      } catch (e) {
        throw new Error(
          `Failed to execute batch queries.
Full error: Unexpected JSON response. ${e instanceof Error ? e.message : String(e)}`
        );
      }
      let results = [];
      for (const [idx, rawResult] of remoteResults.entries()) {
        if (queries[idx]?.method === "run") {
          results.push(rawResult);
          continue;
        }
        const rowValues = [];
        for (const row of rawResult.rows) {
          if (row != null && typeof row === "object") {
            rowValues.push(Object.values(row));
          }
        }
        if (queries[idx]?.method === "get") {
          results.push({ rows: rowValues[0] });
        }
        results.push({ rows: rowValues });
      }
      return results;
    }
  );
  applyTransactionNotSupported(db);
  return db;
}
export {
  createLocalDatabaseClient,
  createRemoteDatabaseClient
};

While this workaround helps me locally, it's crucial to address the underlying problem with runtime environment variables. I'm willing to contribute a pull request to implement a more robust solution if needed.

Looking forward to hearing your thoughts on this and finding a permanent fix.

Best regards,
Santiago Avilez

[santiagoavilez]

Is there a way to replicate the hardcoded functionality I've implemented in my local development environment for production? I'm nearing completion on an MVP for a client project, and switching to another database provider like Turso would be quite inconvenient. I've found Astro DB integration incredibly seamless and would prefer to stick with it if possible.

[matthewp]

Should be fixed by #10520 in @astrojs/db@0.9.3! So sorry about this one, let me know if you still have any issues.