Prisma1: [>=1.28.3 Server version] Unknown SQL type timestamptz

[marticrespi]

Describe the bug
If you use last stable version or any beta 1.29 version, it doesn't work with a timestampz field. I had 1.26 prisma server version until this morning, and with this version works perfectly.

To Reproduce
Steps to reproduce the behavior:

1. Compose a prisma server version with >= 1.28.3 version.
2. Create a sample table with a timestampz field on postgresql.
3. Try to deploy the server.

Error

java.lang.RuntimeException: Encountered unknown SQL type timestamptz with column createdAt. IntrospectedColumn(createdAt,timestamptz,null,false) at scala.sys.package$.error(package.scala:26) at com.prisma.deploy.connector.jdbc.DatabaseInspectorBase.$anonfun$getTable$6(DatabaseInspectorBase.scala:57) at scala.Option.getOrElse(Option.scala:121) at com.prisma.deploy.connector.jdbc.DatabaseInspectorBase.$anonfun$getTable$5(DatabaseInspectorBase.scala:57) at scala.collection.TraversableLike.$anonfun$map$1(TraversableLike.scala:233) at scala.collection.Iterator.foreach(Iterator.scala:937) at scala.collection.Iterator.foreach$(Iterator.scala:937) at scala.collection.AbstractIterator.foreach(Iterator.scala:1425) at scala.collection.IterableLike.foreach(IterableLike.scala:70) at scala.collection.IterableLike.foreach$(IterableLike.scala:69) at scala.collection.AbstractIterable.foreach(Iterable.scala:54) at scala.collection.TraversableLike.map(TraversableLike.scala:233) at scala.collection.TraversableLike.map$(TraversableLike.scala:226) at scala.collection.AbstractTraversable.map(Traversable.scala:104) at com.prisma.deploy.connector.jdbc.DatabaseInspectorBase.$anonfun$getTable$4(DatabaseInspectorBase.scala:54) at slick.dbio.DBIOAction.$anonfun$map$1(DBIOAction.scala:43) at slick.basic.BasicBackend$DatabaseDef.$anonfun$runInContextInline$1(BasicBackend.scala:171) at scala.concurrent.Future.$anonfun$flatMap$1(Future.scala:303) at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:37) at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:60) at akka.dispatch.BatchingExecutor$AbstractBatch.processBatch(BatchingExecutor.scala:55) at akka.dispatch.BatchingExecutor$BlockableBatch.$anonfun$run$1(BatchingExecutor.scala:91) at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12) at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:81) at akka.dispatch.BatchingExecutor$BlockableBatch.run(BatchingExecutor.scala:91) at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:40) at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:44) at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339) at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)

Expected behavior
It should be able to deploy the server without any problem.

Versions (please complete the following information):

• Connector:Postgres
• Prisma Server: >=1.28.3
• prisma CLI: prisma/1.29.0-beta.7 (windows-x64) node-v8.9.4
• OS: Windows 10

[endlessdev]

I found this same issue. but we should use 1.26 version for a while. 😢

[marticrespi]

@pantharshit00 do you know if it's fixed in some alpha or beta server version? Tried with prisma:1.31-alpha-13 and it doesn't still work.

[pantharshit00]

@marticrespi Can you please try this again as the new datamodel has been released now.

[redaikidoka]

In addition, if any tables in a psql11 database prisma is connecting to are of these types, the server crashes:

• date
• time
• double precision
• float
• float4
• float8
• json
• bigint
• timestamp

[alexsergivan]

I faced similar problem with 1.31 version
Encountered unknown SQL type longblob with column data. IntrospectedColumn(data,longblob,null,true)

[ongerit]

In addition, if any tables in a psql11 database prisma is connecting to are of these types, the server crashers:

• date
• time
• double precision
• float
• float4
• float8
• json
• bigint
• timestamp

How were you able to resolve this issue?

[redaikidoka]

I can't resolve the issue. I can't use prisma. :(

On Wed, May 1, 2019 at 4:51 AM Thomas Ongeri [e-mail]
wrote:

In addition, if any tables in a psql11 database prisma is connecting to
are of these types, the server crashers:

• date
• time
• double precision
• float
• float4
• float8
• json
• bigint
• timestamp

How were you able to resolve this issue?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/prisma/prisma/issues/4190#issuecomment-488264420, or mute
the thread
https://github.com/notifications/unsubscribe-auth/ABE6KQOG6GLJ4UYCONDXC33PTF733ANCNFSM4G6Q6PKQ
.

--

-=-=-==-=-=-=-==-=-=-=-=-=-=-=-=-=-
Pol Stafford
Storyteller, Writer, Geek, Sensei

[marticrespi]

@marticrespi Can you please try this again as the new datamodel has been released now.

@pantharshit00 It's still not working. I have tried to update my cli and server version to 1.32.2 and it throws the same timestampz error with the new datamodel, I have to downgrade to 1.26 again 😢

[Vaneste]

Same here with prisma 1.33 and a Postgres db V 9.6 :(

[PedramMarandi]

I have the same problem, hopefully, there will be help from the team soon. I tried with 1.33, 1.28, 1.26 versions and none of them worked.

However, I have a database made by Prisma and Prisma is not introspecting a previously made database in my case.

I don't have this problem when I'm creating the database for the first time, but once I change the database data model, I can't deploy it again.

[marticrespi]

Hi @pantharshit00 , I was on another project, today I have updated my prisma server and client from 1.32.2 to 1.34 and it's still not working, really? Same error again ... 😞 Any news about?

[brafdlog]

I have the exact same problem. I maintain by DB schema and prisma's graphql definitions myself so I don't even need the introspection.

[lastmjs]

I'm running into similar issues, I just updated from 1.21.1 to 1.34.0

[lastmjs]

jsonb is also not supported

[lastmjs]

Also, it doesn't matter If I remove the types from my datamodel.prisma file, just the fact that there are columns with "unsupported" types is enough to cause the deploy to fail. Very undesirable.

[lastmjs]

I guess I'm just going to go back to 1.21.1 until these issues can be resolved

[montanaflynn]

I also just ran into this, going to hold off on prisma until it works as I can't change my existing database schema.

[aaahrens]

to be honest, it's frigging insane this is present in production.

[Vaneste]

Same problem here :
Encountered unknown SQL type timestamptz
Prisma 1.34

[Vaneste]

Bug confirmed also with float4 & float8 Postgres types.
This makes Prisma actually useless with Postgres :(

[marticrespi]

@pantharshit00 ¿Is someone trying to solve this issue right now? Many thanks for your work!

[pantharshit00]

@do4gr is looking into Prisma 1 issues. We had a few busy weeks(cause of Prisma 2) so that's why it was left behind.

[skanygin]

Got same with introspected models on prisma:1.32 and prisma:1.34.

Encountered unknown SQL type timestamptz with column lastactivity. IntrospectedColumn(lastactivity,timestamptz,null,true)

This line is trying to handle @do4gr attention :)

[verner-lall]

Also, it doesn't matter If I remove the types from my datamodel.prisma file, just the fact that there are columns with "unsupported" types is enough to cause the deploy to fail. Very undesirable.

Anyone found a workaround for this? Having the same problem.

[fortydegrees]

Also have the same problem with ENUM and JSON types for my MySQL db. completely unable to use prisma.

[marticrespi]

@pantharshit00 @do4gr Have we any progress to be able to update our database changes? This bug is opened for almost 4 months and I think is very important be able to deploy the server at least, you should solve asap because it does prisma useless.

Many thanks for your work!

[redaikidoka]

I think it's pretty clear Prisma is not for SQL databases...

On Tue, Jul 30, 2019 at 11:51 PM Martí Crespí [e-mail]
wrote:

@pantharshit00 https://github.com/pantharshit00 @do4gr
https://github.com/do4gr Have we any progress to be able to update our
database changes? This bug is opened for almost 4 months and I think is
very important be able to deploy the server at least, you should solve asap
because it does prisma useless.

Many thanks for your work!

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/prisma/prisma/issues/4190?email_source=notifications&email_token=ABE6KQOYJGILIHLLXBBVJITQCFOCLA5CNFSM4G6Q6PK2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3GXE3Y#issuecomment-516780655,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABE6KQKNZGEMQGDICYPMSPTQCFOCLANCNFSM4G6Q6PKQ
.

--

-=-=-==-=-=-=-==-=-=-=-=-=-=-=-=-=-
Pol Stafford
Storyteller, Writer, Geek, Sensei

[PedramMarandi]

I've started using some other alternatives for my project and been always hoping to use Prisma instead :(

Team give us some update, please.

[redaikidoka]

I recommend postGraphile

On Wed, Jul 31, 2019 at 2:08 PM Pedram marandi [e-mail]
wrote:

I've started using some other alternatives for my project and I was really
hoping to be able to use Prisma :(

Team give us some update, please.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/prisma/prisma/issues/4190?email_source=notifications&email_token=ABE6KQMZQT4FYJLYCD3J6Z3QCISQNA5CNFSM4G6Q6PK2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOD3I5F7A#issuecomment-517067516,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABE6KQKHSMJQQDY6HKLMIWTQCISQNANCNFSM4G6Q6PKQ
.

--

-=-=-==-=-=-=-==-=-=-=-=-=-=-=-=-=-
Pol Stafford
Storyteller, Writer, Geek, Sensei

[marticrespi]

No other tool as prisma, for sure. Prisma team will be solve it soon!

[pantharshit00]

https://github.com/prisma/prisma/pull/4790

This PR will fix this issue 🙂

[do4gr]

First of all, sorry that this took so long. This issue seems to have fallen through the cracks. Pinging was a good idea, but most of us get a ton of GitHub notifications and therefore did not see the pings. We'll try to do better!

There are two problems that are being reported here, the first being that some data types are not supported by Prisma, and the second, more serious one, that Prisma fails when it encounters a table that includes columns with data types it cannot handle.

This fixes mainly the second issue, Prisma will now just ignore fields it does not know how to handle. I also added mappings for some data types Prisma should have been able to handle like float4 for example, but this does not introduce support for fundamentally new data types.

This should let you access all the columns Prisma knows how to handle. Please report in a new issue if you are still encountering problems.

[thongly]

@do4gr - Thanks for your hard work on this. Same with @pantharshit00!

I'm actually on the beta (Prisma server version: 1.35.0-beta) but also get the exception for a postgresql jsonb column.

java.lang.RuntimeException: Encountered unknown SQL type jsonb

"Encountered unknown SQL type jsonb with column comments. IntrospectedColumn(comments,jsonb,'[{}]'::jsonb,true)"}}

I have actually already removed the comments field from my datamodels altogether too.

Is this expected for 1.35.0-beta?

[pantharshit00]

I don't think this release was backported so please use version 1.34.5 or higher