Open olavloite opened 1 year ago
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?
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:
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.
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 .
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.)
To be fair, why does a simple
npx prima db push
require all the queries Prisma makes topg_*
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?
That is very kind of you @olavloite. But as of now we decided to stick with using native Postgres with MikroORM.
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?
@jillxuu Certainly, could you drop me an email on the email address list for my GitHub account? (https://github.com/olavloite).
Add support for the NodeJS Prisma ORM.
Related to #581