Feature Request: Add support for Prisma

[olavloite]

Add support for the NodeJS Prisma ORM.

Related to #581

[NOT-HAL9000]

Basic querying now works after the SET NAMES 'UTF8' fix, which is great.

We have now come to the next hurdle when testing a simple migrate.

When running the following command
npx prisma migrate dev --name init

//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
}

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()'

Here is where the relevant line in Primsa which throw the error.

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

How should we proceed?

[olavloite]

I've also been testing this today, and I've run into multiple challenges with running migrations. There are multiple pg_catalog tables and functions that are not yet supported by Cloud Spanner. We can work around many of those in PGAdapter, but it seems that Prisma is using a lot of them for introspection. Also, the setup with a shadow database for development migrations is one that needs some work for Cloud Spanner. So the TLDR is that:

1. Support for migrations will probably be very limited (and will need some time to get to a level where it is actually usable).
2. Normal data operations seem much more viable.

[janpio]

Prisma indeed talks to these system tables a lot to get all the information about the tables and columns to craft our models and fields (and everything related to these). So db pull needs those, but of course a user can also hand write all the models if needed.

If the shadow database problem is related to CREATE/DROP DATABASE, then it might be viable to use shadowDatabaseUrl in your datasource block to point to an existing database - either via PgAdapter or maybe even just a real PostgreSQL for it to be able to quickly apply the migration files.

[belmeopmenieuwesim]

To be fair, why does a simple npx prima db push require all the queries Prisma makes to pg_* tables? It should just run the plain .sql files inside migrations folder. If prisma did that, then it would already support Google Cloud Spanner to a workable extend @janpio .

[janpio]

db push does not actually run any SQL files, it just takes the current Prisma Schema file and makes your database look like that. Hence db push has to figure out the current state of the database schema, so it can know what "diff" it needs to apply to get it to the desired state defined in the Prisma Schema. It ad hoc generates the SQL for that and runs it.

What you are suggesting already exists as prisma migrate deploy. That command reads the _prisma_migrations table to figure out which of the migrations (from your prisma/migrations folder) have already been applied, then applies the ones that have not yet (which means executing plain SQL) and then records that into the migrations table. (You can either hand write those migration files by hand to match what you have defined in your Prisma Schema, or use prisma migrate dev - but that again works like db push under the hood to figure out what SQL files should be generated.)

[olavloite]

To be fair, why does a simple npx prima db push require all the queries Prisma makes to pg_* tables? It should just run the plain .sql files inside migrations folder. If prisma did that, then it would already support Google Cloud Spanner to a workable extend @janpio .

@belmeopmenieuwesim We have also been experimenting with an alternative development model where migration commands are executed on a local PostgreSQL copy of your Cloud Spanner database. This would mean that commands like prisma migrate dev would require a couple of intermediate steps to generate the diff. This diff is then stored in the prisma/migrations folder and can be applied to the Cloud Spanner database by running prisma migrate deploy.

Would you be interested in a quick call to see if this would be a possibility for your use case?

[belmeopmenieuwesim]

That is very kind of you @olavloite. But as of now we decided to stick with using native Postgres with MikroORM.

[jillxuu]

hi @olavloite , im also running into same issue where i'd like to use prisma to manage my spanner db, and im maintaining a shadow postgres db to do db schema management, and then apply these migration files to spanner. the solution you mentioned above sounds reasonable to me. wondering can we hop on a quick call if you're available?

[olavloite]

@jillxuu Certainly, could you drop me an email on the email address list for my GitHub account? (https://github.com/olavloite).