Support Google Cloud Spanner

[jhalborg]

Hey! :)

It would be awesome if Prisma Client could be compatible with https://cloud.google.com/spanner/

[DustinJSilk]

Cloud Spanner now supports a Postgres interface (In preview)
They also recently reduced the lowest billable unit to 1/10th of what it was, making Spanner far more accessible.

[wgottschalk]

I'd love to see Spanner supported. Now that Spanner has granular instance sizing and cloud sql has mandatory downtime where Spanner doesn't, it makes it the preferred database over cloudsql.

Spanner is starting to rollout a postgres dialect to spanner. They mention it's not 100% compatible but it would be great have prisma add some documentation outlining limitations if any

https://cloud.google.com/spanner/docs/postgresql-interface

[janpio]

Nice.

(Note that the following does not indicate that we are committed to build a Google Spanner compatible connector any time soon. This is just me exploring and figuring out what might already work, and what does not. If Prisma will ever have this, and wants to spend more time on this, will be decided on a different level. I am just curious.)

Our next steps here would be...

• to understand GCP enough to be able to have a Spanner instance with some schema and data in it, and enable the PostgreSQL interface.
• We would then try to run npx prisma db pull on it and see what fails.
• If it magically works out of the box (If Introspection does not work out of the box, we would probably still continue with a manually written schema of the database - starting with one table probably.), we would use the generated schema to send some manualy queries.
• If that also magically works, we could even try migrate dev after a small schema change to see what happens.
• And if that also succeeds we would then start to think how to integrate a Cloud only service into our test suite so this could somehow potentially become a properly tested implementation - but that is quite a bit off.

So if you understand GCP well, have a Spanner instance with data and the PostgreSQL interface enabled - please try these steps! (And/or send me an email at jan@prisma.io and send over the connection string, so we can do so at the side).

[glebbash]

Tried to use PGAdapter and Spanner Emulator locally with Prisma using the following docker-compose file:

version: "3.3"
services:
  spanner:
    env_file: .env
    image: gcr.io/cloud-spanner-emulator/emulator
    ports:
      - '9010:9010'
      - '9020:9020'

  pg-adapter:
    image: gcr.io/cloud-spanner-pg-adapter/pgadapter:v0.6.1
    volumes:
      - ./test/fake-spanner-credentials.json:/acct_credentials.json
    ports:
      - 127.0.0.1:5432:5432
    command:
      - -p my_project
      - -i my_instance
      - -d my_database
      - -c /acct_credentials.json
      - -x

Getting this error:

Prisma CLI Version : 4.1.1
Error: Command failed: DATABASE_URL=cloudspanner:/projects/my_project/instances/my_instance/databases/my_database prisma migrate dev
Error: Get Config: Schema parsing - Error while interacting with query-engine-node-api library
Error code: P1012
error: Error validating datasource `db`: the URL must start with the protocol `postgresql://` or `postgres://`.
  -->  schema.prisma:6
   | 
 5 |   provider = "postgresql"
 6 |   url      = env("DATABASE_URL")

So the problem is that PGAdapter requires connection url to start with cloudspanner:// while Prisma wants it to be postgresql://.

Using cloudspanner:// will error out on Prisma side.

Using postgresql:// will error out on PGAdapter side.

[janpio]

Adding an env var that allows cloudspanner:// as an alternative to postgresql:// (in a dev version maybe) would unblock this problem I guess @glebbash?

[glebbash]

If there is no further validation for the rest of connection url: /projects/my_project/instances/my_instance/databases/my_database then yeah, it should work.

[janpio]

Thanks for clarifying that, we would indeed probably check for port and so on. So they way here would be to accept a completely different connection string. Interesting that psql seems to work for that out of the box (per PGAdapter docs). Can you share a full connection string that we would need to use?

[glebbash]

Now that I think about which url would be okay for PGAdapter to work I am kinda lost, as it requires cloudspanner prefix and after that you would need to point to a local IP and port.

So theoretically it should be comething like cloudspanner://localhost:5432.

But there is a big chance that it wont work like that as PGAdapter docs say that only psql client is supported.

[NOT-HAL9000]

Has there been any updates / further thoughts on this @janpio?

Prisma should seriously consider attempting to support a Spanner connector.

The PostgreSQL interface for Cloud Spanner is now in GA and their basic pricing tier has dropped down to as low as $40/month.

Having a cheap, strongly consistent cross-region, ACID compliant, relational database is basically the holy grail, but unfortunately, many fast-moving teams will either overlook or avoid using Spanner because there is no Type-safe Node ORM that connects to it (despite the efforts by Google to provide the PostgreSQL Interface).

By supporting Spanner, Prisma has the opportunity to capture the market of large enterprise companies who use and/or want to use it with Node. This could then be leveraged as a funnel to drive these large customers towards your paid and enterprise offerings, which pays off the effort spent integrating Spanner.

[janpio]

This is currently not a priority for us.

If someone (@glebbash?) confirms that for example allowing cloudspanner:// as a protocol is all it needs to test this further, we might be happy to add that so we get to a better understanding where blockers will be. But w can currently not invest in figuring all this out or even implementing support unfortunately.

[etifontaine]

Hello,

I have setup a Spanner instance, with the PostgreSQL dialect.

When starting my app, I get the following error:
Error querying the database: db error: ERROR: Invalid SET statement: SET NAMES 'UTF8'. Expected TO or =.

I use DATABASE_URL="postgresql://127.0.0.1:5432/my-database" in my Prisma config.

Plus the following Docker container,
docker run \ -d -p 5432:5432 \ -v ${GOOGLE_APPLICATION_CREDENTIALS}:${GOOGLE_APPLICATION_CREDENTIALS}:ro \ -e GOOGLE_APPLICATION_CREDENTIALS \ gcr.io/cloud-spanner-pg-adapter/pgadapter \ -p my-gcp-project-id -i my-spanner-instance-id \ -x

[janpio]

Naive question, what is the quickest way to setup up the required GOOGLE_APPLICATION_CREDENTIALS?

[NOT-HAL9000]

Edit: Update from the PGAdapter maintainer: GoogleCloudPlatform/pgadapter#581

This specific issue however is a PGAdapter problem. The SET NAMES 'UTF8' syntax is not a standard PostgreSQL statement, but it is something that PostgreSQL apparently has added support for to adhere to the ANSI SQL standard. See https://www.postgresql.org/docs/current/multibyte.html. PGAdapter currently does not take that possibility into account. Refer to the pgadapter issue above for further discussion.

It seems as though parseSetStatement would need to be updated on PGAdapter's end, or SET NAMES 'UTF8' removed on the Prisma's end (possibly with the addition of a flag when connecting to spanner) in order to move forward.

I'm too unfamiliar with Prisma's internals to try remove the statement myself to proceed.

---

I just tried the same thing as @etifontaine and got the same result.

To reproduce the error, follow these steps to set up the following:
([ Local NodeJS ] -> [ Local Docker PGAdapter ]) -> [ Remote Spanner Database ]

1. Create a Google Cloud Account
2. Search for Spanner in the Console Search bar and enable. Then set up a Spanner instance, regional, us-central1 with 100 processing units ($0.09/h). There is also a free trial for Spanner so testing might be free for you.
3. When it's done, create a database with PG Dialect. Remember the name.
4. Go to IAM (Cloud console search bar) > Services Accounts and create a new one with Owner Access.
5. Once created, click the 3 dots next to the new service account and 'Manage Keys'
6. Create and download a new JSON key, this will be your GOOGLE_APPLICATION_CREDENTIALS.
7. Follow the guide here and launch a local PGAdapter docker service. Below is the what I used. PROJECT_ID is found here, it should be the name of your project followed by some numbers. SPANNER_INSTANCE_ID is the name found under Spanner Page and the PG_DATABASE_NAME is what you named it in step 3 (you can find it again under the Spanner section). GOOGLE_APPLICATION_CREDENTIALS_PATH is the path to the downloaded JSON path from step 6.
8. Launch the docker container. (Optionally) Remove the -d flag when running the docker and add a -debug at the end to access the logs of PGAdapter. All options found here.

You don't need to run PGAdapter in Debug mode. I have included the output at the bottom

docker run -d -p 5432:5432 \
    -v GOOGLE_APPLICATION_CREDENTIALS_PATH:/acct_credentials.json \
    gcr.io/cloud-spanner-pg-adapter/pgadapter:latest \
    -p PROJECT_ID -i SPANNER_INSTANCE_ID -d PG_DATABASE_NAME  \
    -c /acct_credentials.json -q -x

9. Create a basic node project with with the following

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// .env
DATABASE_URL="postgresql://localhost:5432/{DATABASE_NAME from step 3}"

// index.js
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient();

(async () => {
    const result = await prisma.$queryRaw`SELECT 1`
    console.log(result)
})()

10. Run the script node index.js and get the following error.
    Error querying the database: db error: ERROR: Invalid SET statement: SET NAMES 'UTF8'. Expected TO or =.

I also got the same error when trying with a simple migrate User schema using the following command.

npx prisma migrate dev --name init

PGAdapter Debug Logs

-- Starting PGAdapter version 0.14.1 --
It is no longer necessary to add psql mode (-q) to the command line arguments.
PGAdapter now automatically recognizes connections from psql.
-- PostgreSQL version: 14.1 -- 
Jan 11, 2023 2:18:52 AM com.google.cloud.spanner.pgadapter.ProxyServer startServer
INFO: Server started on port 5432
Jan 11, 2023 2:19:29 AM com.google.cloud.spanner.pgadapter.ConnectionHandler <init>
INFO: Connection handler with ID ConnectionHandler-1 created for client 172.17.0.1
Jan 11, 2023 2:19:29 AM com.google.cloud.spanner.pgadapter.ConnectionHandler run
INFO: Connection handler with ID ConnectionHandler-1 starting for client 172.17.0.1
Jan 11, 2023 2:19:34 AM com.google.cloud.spanner.pgadapter.ConnectionHandler handleError
WARNING: Exception on connection handler with ID ConnectionHandler-1: com.google.cloud.spanner.pgadapter.error.PGException: java.io.EOFException
com.google.cloud.spanner.pgadapter.error.PGException: java.io.EOFException
        at com.google.cloud.spanner.pgadapter.error.PGException$Builder.build(PGException.java:59)
        at com.google.cloud.spanner.pgadapter.ConnectionHandler.handleMessages(ConnectionHandler.java:417)
        at com.google.cloud.spanner.pgadapter.ConnectionHandler.runConnection(ConnectionHandler.java:334)
        at com.google.cloud.spanner.pgadapter.ConnectionHandler.run(ConnectionHandler.java:257)
Caused by: java.io.EOFException
        at java.base/java.io.DataInputStream.readUnsignedByte(Unknown Source)
        at com.google.cloud.spanner.pgadapter.wireprotocol.ControlMessage.create(ControlMessage.java:113)
        at com.google.cloud.spanner.pgadapter.wireprotocol.WireMessage.nextHandler(WireMessage.java:193)
        at com.google.cloud.spanner.pgadapter.wireprotocol.StartupMessage.nextHandler(StartupMessage.java:117)
        at com.google.cloud.spanner.pgadapter.ConnectionHandler.handleMessages(ConnectionHandler.java:410)
        ... 2 more

Jan 11, 2023 2:19:34 AM com.google.cloud.spanner.pgadapter.ConnectionHandler runConnection
INFO: Closing connection handler with ID ConnectionHandler-1
Jan 11, 2023 2:19:34 AM com.google.cloud.spanner.pgadapter.ConnectionHandler runConnection
INFO: Connection handler with ID ConnectionHandler-1 closed```

[janpio]

Awesome. I responded on that issue - let's see if we can figure this out together to move forward here.

[janpio]

Some experimental work started over there: GoogleCloudPlatform/pgadapter#589 + GoogleCloudPlatform/pgadapter#590 <3

[patwhite]

I'll add my two cents here - it would actually be nice to have a spanner native (non-postgres dialect) version. There are a few really interesting caveats to the way the postgres dialect works as have been noted in this thread. First, you have to run a separate process (the pgadapter) to do the translation, but secondly, there are actually some really important features missing when you use the postgres dialect - specifically, the change log that spanner produces and is consumable for CDC doesn't work with postgres (as I understand it). I also remember the journey we went through here for cockroach where, even though it said wire compatible, there are enough special things that prisma does that it didn't really work perfectly until there was a full, flavored release.

[janpio]

We fundamentally agree, but to explain why I am pushing on this: Native Spanner support is not happening for sure this year. If it turns out that the PgAdapter is close enough or can be adapted though, we could possibly find a way to make this usable sooner.

[NOT-HAL9000]

@janpio PGAdapter has been updated to version 15. I have re-tried the basic query and it worked as expected.

const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient();

(async () => {
    const result = await prisma.$queryRaw`SELECT 1 as hello_world`
    console.log(result)
})()

Now produces the following as expected.

[ { hello_world: 1n } ] // 1n is a bigint

---

Before you throw the PG test suite at the Adapter to check compatibility, I have already tested basic migrations and found the next blocker. When attempting to migrate the database with the following schema

//schema.prisma
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
}

I received the following error when calling npx prisma migrate dev --name init

Error: Migration engine error:
db error: ERROR: Postgres function version() is not supported - Statement: 'with pg_namespace as (
  select case schema_name when 'pg_catalog' then 11 when 'public' then 2200 else 0 end as oid,
        schema_name as nspname, null as nspowner, null as nspacl
  from information_schema.schemata
)
SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = $1), version()'

This error is also thrown when directly querying the SELECT version(); in the spanner console.

How should we proceed? Is this something that should be handled upstream by PGAdapter? Or should it be handled by Prisma? Once we decide, then we can re-engage Knut Olav Løite for possible fixes.

[janpio]

I think version() again is something the adapter could handle. It is one of the default system functions of PostgreSQL, which is why Prisma expects it to exist: https://www.postgresql.org/docs/current/functions-info.html#:~:text=to%20current_user.-,version%20()%20%E2%86%92%20text,-Returns%20a%20string

(We could rewrite our code to handle this not being present, but for normal PostgreSQL (and compatible databases) that is never expected to fail, so would be a bit weird - but we most probably could: https://github.com/prisma/prisma-engines/blob/af46365b40aa4864b99acec97dc2dd5bdc27411c/migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs#L562-L594)

I expect a few more of these to pop up on db pull as well, where Prisma queries a lot of the PostgreSQL system tables for information about the available tables and columns and so on.

[NOT-HAL9000]

I've mentioned this on the prisma support issue: GoogleCloudPlatform/pgadapter#589

[jillxuu]

im running into similar errors when using pgadapter with prisma migrate

Error: Schema engine error:
db error: ERROR: Postgres function current_setting(text) is not supported - Statement: 'with pg_namespace as (
  select case schema_name when 'pg_catalog' then 11 when 'public' then 2200 else 0 end as oid,
        schema_name as nspname, null as nspowner, null as nspacl
  from information_schema.schemata
)
SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = $1), '14.1', current_setting('server_version_num')::integer as numeric_version;'

[fuvidani]

Hello everyone, do you have any update on the Spanner support? Has anybody been able to make it work reliably with Prisma? 🙇‍♂️

[m3adow]

We recently tried to use Spanner as a Prisma backend but gave it up in the end. If I remember correctly, we couldn't make migrations work properly without more work than it was worth.

I'd love to hear if and how anyone got this to work properly