Hi there I m trying to use the instrospect tool with a produ Prisma #orm-help

Hi there! I’m trying to use the instrospect tool w...

Saulo Aguiar

03/31/2022, 6:31 PM

Hi there! I’m trying to use the instrospect tool with a production ready database right now. Should it read functions and sequences (maybe triggers?) as well as tables, columns and indexes? After the introspection worked, I generated a migration by running

prisma migrate dev

. However, when I try to re create the database from scratch in my CI server, I get the following error:

Copy code

Database error:
ERROR: relation "patient_unique_id_seq" does not exist

I retrieved a dump from the production-ready database, and I saw that this sequence is properly defined in there. But it looks like the prisma introspection - and the migration it generated - is missing it. Any suggestions on how to fix this?

Saulo Aguiar

04/01/2022, 3:31 PM

hey @Nurul 👋 is this something you’d be able to help?

Nurul

04/01/2022, 3:39 PM

Hey Saulo 👋 Introspection should read the entities which are supported in the prisma schema. So I am sure that triggers won’t be read.

Nurul

04/01/2022, 3:40 PM

How did you create the new migration?

Nurul

04/01/2022, 3:41 PM

Was this migration run on an empty database?

Saulo Aguiar

04/01/2022, 4:12 PM

tks Nurul! so I introspected the database by running ``prisma db pull` . After that I ran

prisma migrate dev --create-only

to have a migration that would mimic the prisma schema created after the introspection. (I did that so I can run e2e tests in the ci where I create the database and apply the migrations for each test run). However this was when the error happened. One function that was defined in the database wasn’t captured by prisma

Saulo Aguiar

04/01/2022, 4:13 PM

and because of that the

prisma deploy

command wasn’t completing fine. one of the attributes in a model references a sequence (that references a function in the original database schema)

Nurul

04/01/2022, 4:33 PM

Just to confirm, you got this error when you executed

npx prisma migrate dev --create-only

correct?

Saulo Aguiar

04/01/2022, 4:37 PM

sorry, no. the migration was created and it had a reference to a function defined in pg

janpio

04/01/2022, 11:41 PM

So the function usage was picked up in the schema, probably as

@default(dbgenerated(...))

but the function itself was not created, then causing a problem?

Saulo Aguiar

04/02/2022, 1:43 PM

hi @janpio thanks for the question. Yes! the attribute that gets generated in prisma schema file is this one

Copy code

unique_id                String?                  @default(dbgenerated("(bounded_pseudo_encrypt((nextval('patient_unique_id_seq'::regclass))::integer, 99999999, 10000000))::text"))

I also exported the db schema manually and the functions and sequence called are defined as below

Copy code

-- Name: bounded_pseudo_encrypt(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.bounded_pseudo_encrypt(value integer, max integer, min integer) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
BEGIN
  LOOP
    VALUE := pseudo_encrypt_28(nextval('patient_unique_id_seq')::int);
    EXIT WHEN VALUE <= MAX AND VALUE >= MIN;
  END LOOP;
  RETURN VALUE;
END
$$;
--
-- Name: patient_unique_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.patient_unique_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    MAXVALUE 2147483647
    CACHE 1;

janpio

04/02/2022, 11:17 PM

Yes, so that is definitely not supported. You should be able to edit the migration before executing it and add that SQL before the function is used though. Then it should work.

💯 1

Saulo Aguiar

04/04/2022, 1:17 PM

tks @janpio! Another issue that I’ve ran into was some string columns having diff uuid sizes? I get this error when I try to save a model using

prisma.model.create({...data})

Copy code

Inconsistent column data: Error creating UUID, invalid length: expected one of [36, 32], found 16

In the schema file this is how the column is defined

Copy code

id String  @id(map: "entity_id_pk") @default(uuid()) @db.Uuid

janpio

04/04/2022, 1:55 PM

Interesting. What is the SQL behind that? This could be a bug...

Saulo Aguiar

04/04/2022, 2:07 PM

Copy code

SELECT 1 []
BEGIN []
INSERT INTO "public"."entity" 
("name","code","scheduler_id","tenant_id","domain","website","province","time_zone","from_email","status","report_token","privacy_policy","organization","banner","hide_shared_field","hide_personal_field")
   VALUES 
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16)
  RETURNING "public"."entity"."id" ["entity name","entity_code","4b35d7a4-05c9-4ece-b701-725be7214ef8","will_we_use_this","<http://domain.entity.com|domain.entity.com>","<http://www.entity.com|www.entity.com>","ON","America/Toronto","<mailto:no-reply@email.com|no-reply@email.com>","ACTIVE","","","","",false,false]
ROLLBACK []

Saulo Aguiar

04/04/2022, 2:27 PM

probably worth mentioning that

prisma db pull

generated a warning for this id field saying

Copy code

These id fields were enriched with `@default(uuid())` information taken from the previous Prisma schema.

janpio

04/04/2022, 3:17 PM

Sorry, I meant the

DDL

of the table and especially that column.

janpio

04/04/2022, 3:17 PM

(That information is super interesting - maybe that is not actually correct any more for your database?)

🤔 1

janpio

04/04/2022, 3:18 PM

Can you maybe share the

CREATE TABLE

for that table?

Saulo Aguiar

04/04/2022, 3:37 PM

ahh got it. np!

Copy code

CREATE TABLE "entity" (
    "id" UUID NOT NULL,
    "name" VARCHAR NOT NULL,
    "code" VARCHAR NOT NULL,
    "scheduler_id" UUID NOT NULL,
    "tenant_id" UUID NOT NULL,
    "domain" VARCHAR,
    "website" TEXT,
    "province" TEXT,
    "time_zone" TEXT,
    "from_email" TEXT DEFAULT E'<mailto:no-reply@email.com|no-reply@email.com>',
    "status" VARCHAR DEFAULT E'ACTIVE',
    "report_token" TEXT DEFAULT E'',
    "privacy_policy" TEXT DEFAULT E'',
    "organization" TEXT DEFAULT E'',
    "banner" TEXT DEFAULT E'',
    "hide_shared_field" BOOLEAN DEFAULT false,
    "hide_personal_field" BOOLEAN DEFAULT false,

    CONSTRAINT "entity_id_pk" PRIMARY KEY ("id")
);

👍 1

janpio

04/04/2022, 3:56 PM

By default Prisma introspects this DDL this way:

Copy code

model entity {
  id                  String   @id(map: "entity_id_pk") @db.Uuid
  name                String   @db.VarChar
  code                String   @db.VarChar
  scheduler_id        String   @db.Uuid
  tenant_id           String   @db.Uuid
  domain              String?  @db.VarChar
  website             String?
  province            String?
  time_zone           String?
  from_email          String?  @default("<mailto:no-reply@email.com|no-reply@email.com>")
  status              String?  @default("ACTIVE") @db.VarChar
  report_token        String?  @default("")
  privacy_policy      String?  @default("")
  organization        String?  @default("")
  banner              String?  @default("")
  hide_shared_field   Boolean? @default(false)
  hide_personal_field Boolean? @default(false)
}

janpio

04/04/2022, 3:57 PM

Do you remember if you initially added

@default(uuid())

janpio

04/04/2022, 3:57 PM

(Was this a Prisma 1 project maybe before?)

Saulo Aguiar

04/04/2022, 4:06 PM

no, this wasn’t a prisma 1 project. so prisma didn’t add a default value when it generated the

prisma.schema

file because the database didn’t have a default value for this column. that’s when I added the

@default(uuid())

. There was a java application that managed this database and I’m migrating it to be a node js based. I also just confirmed that the java code base was generating the ids on its own and not relying on the database to do so. So my question now would be … is it possible to make this change through prisma? to make it to manage the uuid generation?

janpio

04/04/2022, 4:18 PM

Ok, got it. Let me look at this a bit.

janpio

04/04/2022, 4:23 PM

Something weird is going on there. The

uuid()

should and does definitely generate a 32 character UUID.

janpio

04/04/2022, 4:32 PM

Ha, actually, we just got bitten by a bad error message of PostgreSQL here:

Copy code

Inconsistent column data: Error creating UUID, invalid length: expected one of [36, 32], found 16

This is about

tenant_id: "will_we_use_this"

- which is 16 characters long.

Saulo Aguiar

04/04/2022, 4:35 PM

whoa! I see

Saulo Aguiar

04/04/2022, 4:36 PM

yeah this was quite odd… I have another table that using uuid is working fine. let me just confirm it here

janpio

04/04/2022, 4:36 PM

Took me a while as well. I expected Prisma to point that out to me...

janpio

04/04/2022, 4:36 PM

But the type validation is "only" on the scalar types (

String

), not on the native types (

@db.Uuid

) for now I think.

❗ 1

janpio

04/04/2022, 4:37 PM

Asked about it internally and will make it a feature request though. Especially with PostgreSQL hving such bad error messages, that would be really helpful.

✅ 1

Saulo Aguiar

04/04/2022, 4:40 PM

ok just confirmed here and your finding was spot on! tks for the patience @janpio!

janpio

04/04/2022, 4:47 PM

Awesome! Thanks for helping along here.

janpio

04/04/2022, 4:47 PM

Did you get to try my original recommendation about the function?

Saulo Aguiar

04/04/2022, 4:56 PM

just clarifying… you mean adding functions/sequences manually to the initial migration?

janpio

04/04/2022, 5:27 PM

Yes 👍

Saulo Aguiar

04/04/2022, 6:07 PM

I’m working on it! will keep you posted 👍

Saulo Aguiar

04/04/2022, 10:07 PM

confirming that adding the functions made things work! as listed in the docs it’s a little tricky because the order of operations in sql has to be respected but it worked out fine! tks again @janpio!

👍 1

prisma rainbow 1

janpio

04/04/2022, 10:14 PM

Awesome, thanks for confirming.

11 Views

Open in Slack

Previous Next