Support for native database composite types (CREATE TYPE)

[ghost]

Problem

I found out I needed to implement a composite type (an integer type and a text type all as a single type called block) in my postgresql database for a project I am working on. I had implemented Prisma throughout most of my backend only to add this type in the db, introspect, and then see that field commented our in my schema.prisma file saying that the type is not currently supported. I looked through the docs and sure enough, no support for composite types. Now it looks I have to completely rip Prisma out of my project and find something else(although not sure what, cuz there isn't jack s*%$ for decent ORM's with node, if anyone knows something else good feel free to let me know).

There have have been other issues that I have dealt with (like not being able to delete entities with relations using the .delete() function, which I mean come on, how is that still an issue... that should be a basic feature) but at least they had work arounds, like just using executeRaw with some raw SQL to perform the delete. But this issue with no support for composite types does not seem to have any work around (outside of changing my db structure).

Suggested solution

Add support for composite types. (And fix the delete issue.)

Alternatives

Some sort of work around for those who already have composite types in use in their db and need to be able to use them with Prisma.

[esemeniuc]

I agree, would love to see it!

[janpio]

Can one of you share a SQL schema of such a type and possibly how you would want to see this represented in your Prisma Schema?

[rmobis]

@janpio I've crafted these schemas based on the content of this post where the author hacked composite types into TypeORM. It implements a Price type which stores both the currency and the amount of "money".

This is mostly an initial suggestion from someone who has not been using Prisma yet, but been evaluating a migration from TypeORM and would like to see this feature get into Prisma, but I believe it's a good starting point.

In this reddit thread there's some further discussion into whether composite types are an appropriate solution to this issue and how, unfortunately, lack of support on client side is usually a hindrance.

SQL Schema

CREATE TYPE currency AS ENUM ('USD');

CREATE TYPE price AS (
    currency currency,
    amount INTEGER
);

CREATE TABLE plan (
    id SERIAL PRIMARY KEY,
    price price NOT NULL
);

Possible Prisma Schema

enum Currency {
    USD
}

type Price {
    currency    Currency
    amount      Int
}

model Plan {
    id        Int      @id @default(autoincrement())
    price     Price
}

[Demonstrandum]

I assume this would mean working on the prisma-engines dataformat parser, and implementing appropriate translation to supported targets, but I'm too unfamiliar with the project structure to try implementing something like this myself. Is there any documentation on the whole pipeline, where all the corresponding source is, and how someone could go about extending PSL?

[SerenModz21]

i hope this gets added because having already typed json objects would be so much nicer than creating types/interfaces that implement the json type, to then cast the custom type everytime you want to use data from that json

[lelabo-m]

I am in need of the same thing: either through composite types or typed JSON.
Correct me if I am wrong, but right now, none seems to be accessible.

[alielkhateeb]

Any ideas here? this is obviously needed, and it is definitely supported in PostgreSQL

[kpamaran]

Would love support for composite types. postgres does provide type structure of directly-user-defined composite types via pg_class, catalog the same place for from-table composite types, this should be feasible.

[janpio]

Just to make sure: This issue is about https://www.postgresql.org/docs/current/sql-createtype.html for PostgreSQL, correct? Do other databases also support similar constructs?

If you are looking for type JSON columns, please 👍 this issue: #3219

[ig4e]

Just to make sure: This issue is about https://www.postgresql.org/docs/current/sql-createtype.html for PostgreSQL, correct? Do other databases also support similar constructs?

If you are looking for type JSON columns, please 👍 this issue: #3219

Please just implement it.
I have been waiting for a VERY long time (almost 1.5 years) and still no progress this should be a basic feature for postgresql NOT mongoDB only

[Demonstrandum]

yeah, the issue is about at least being able to model this:
https://www.postgresql.org/docs/current/rowtypes.html#:~:text=A%20composite%20type%20represents%20the,be%20of%20a%20composite%20type.
in Prisma.

[jonschlinkert]

I'd love to see the ability to create composite types for Json fields.

[cobbvanth]

This would be an awesome addition

[entrptaher]

CREATE TYPE "TransactionMessage" AS (
    header "TransactionMessageHeader",
    account_keys BYTEA[],
    recent_blockhash BYTEA,
    instructions "CompiledInstruction"[]
);

It failed to generate anything from 'db pull',

model transaction {
  slot              BigInt
  signature         Bytes
  is_vote           Boolean
  message_type      Int?                                  @db.SmallInt
  legacy_message    Unsupported("TransactionMessage")?
  v0_loaded_message Unsupported("LoadedMessageV0")?
  signatures        Bytes[]
  message_hash      Bytes?
  meta              Unsupported("TransactionStatusMeta")?
  write_version     BigInt?
  updated_on        DateTime                              @db.Timestamp(6)

  @@id([slot, signature], map: "transaction_pk")
}

Got stuck here and the best thing is to query the whole thing raw,

   const result = await prisma.$queryRaw`select
    to_jsonb(signature) as signature,
    to_jsonb(signatures) as signatures,
    to_jsonb(legacy_message) as legacy_message,
    to_jsonb(v0_loaded_message)  as v0_loaded_message,
    to_jsonb(meta)  as meta
  from
    public."transaction"
  where
    slot = 1204`;

  console.log(JSON.stringify({ result }, null, 2));

[amolpatel]

Really could use this feature

[ig4e]

Any progress? This is very useful for migrating existing schemas to prsima/using the full potential of postgresql (does cockroach support composite types like postgresql since it's compatible with postgresql?)

[yaacov-commenda]

Without this feature, I'm running into a problem where I have to create a new table for any complex object that a table might have more than one of, even if we never need to query that object directly. That results in deeply nested database schemas and inefficient reads and writes.

Has anyone found a good workaround for this?

[joonatanvanhala]

Any updates on composite types on postgres? Would like to see that feature implemented.

[RatebSeirawan]

+1

[matiasmarcodelpont]

Storngly need this

[tykind]

This will be useful.

[Arlen22]

Without this feature, I'm running into a problem where I have to create a new table for any complex object that a table might have more than one of, even if we never need to query that object directly. That results in deeply nested database schemas and inefficient reads and writes.

I'm assuming postgres should have optimizations for this kind of structure, since it is largely parrallel, but I don't know much about postgres yet.

[Sim1onE]

Any progress? Or a temporary solution to implement this using the new $extends added in Prisma 5?

[Nmans01]

Without this feature, I'm running into a problem where I have to create a new table for any complex object that a table might have more than one of, even if we never need to query that object directly. That results in deeply nested database schemas and inefficient reads and writes.

I am running into this same issue, and I now have to resort to uncoupling every table that references my "data structure" tables because this approach is scaling horribly in my project. I'd really love to see some progress with this.

[JasonColeyNZ]

+1 for me also, seems odd this is available for mongo and not postgres? But doesn't look like this will ever be a thing, as there seems to be nothing from the team on this one? :(

[janpio]

That the issue is not closed is an indication that we consider this a relevant and valid feature request.
Right now it is now on our roadmap though, so please leave comments with your use cases so we can learn more why this should be prioritized.

[PabloMartinez-Beemeral]

I would love to see this implemented. Currently working on a rather big structure and having custom data types would help greatly, making the code, schemas and tables so much cleaner and possibly more optimized. Not having this option is lacking one of the best Postgres' features.

[FrancoRATOVOSON]

Hope it's in progress 🙏🏾🙏🏾

[joshbedo]

would love to see this for mysql surprised it doesnt support composite types. I'm not really sure how to do nested objects that are type safe otherwise.