TS: SQLDatabase's connection string fails when connecting to Prisma CLI

[luisnquin]

At my organization we're trying to integrate Prisma with Encore.dev(TS). Fow now it looks like Prisma doesn't allows you to programatically migrate everything so we decided to spawn the Prisma CLI as a workaround.

The problem here is that the connection string provided by SQLDatabase is wrong but the Encore CLI is able to give me the correct connection string.

Code

// auth/auth.ts
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { prismaMigrate } from "@/lib/prisma";

const db = new SQLDatabase("auth");

console.log("[1] connection string is...", db.connectionString);

const result = await db.queryRow`SELECT 1`;
console.log("result is...", result);

console.log("[2] connection string is...", db.connectionString);

await prismaMigrate(db.connectionString);

// lib/prisma.ts
// Code from https://github.com/prisma/prisma/issues/4703#issuecomment-1447354363
import { promisify } from "node:util";
import { exec as execCb } from "node:child_process";

const exec = promisify(execCb);

// TODO: re-write this when Prisma.io gets a programmatic migration API
// https://github.com/prisma/prisma/issues/4703
export async function prismaMigrate(databaseUrl: string): Promise<void> {
  // throws an error if migration fails
  const { stdout, stderr } = await exec("npx prisma db push", {
    env: {
      ...process.env,
      DATABASE_URL: databaseUrl,
    },
  });
  console.log(stdout);
  console.warn(stderr);
}

Terminal

Logs of my local server

As you can see in the image below, I'm using encore v1.41.1.

[fredr]

It looks like the connection string is correct, e.g you can use that to connect via psql, but when prisma tries to connect it fails for some reason. I'm currently looking into why and if we can find a way to fix that

[luisnquin]

Thanks, @fredr! It would be great if the TS Database Documentation could also include references to ORMs that integrate seamlessly with this tool and its microservices architecture.

[fredr]

I've tested your migrate code, and for me it works if I delay execution like this:

(async () => {
  await prismaMigrate(db.connectionString);
})();

Does that work for you as well?

[luisnquin]

@fredr yup, it's working! :)

[fredr]

Great,

in the next release of encore there is a fix for this (#1407) so you won't need to wrap it for long

[fredr]

If you update to v1.41.4, then your original code should work