Discussed in https://github.com/prisma/prisma/discussions/6963

<div type='discussions-op-text'>

^{Originally posted by pfried May 6, 2021} This is a question on how to setup timescale with prisma. Thanks to the post from @janpio I tried to create a similar setup. So this is related to #https://github.com/prisma/prisma/issues/3228

My goal is to ingest IoT data into timescaledb with the help of prisma since the whole raw sql stuff is just bad (I know some stuff will stay raw sql stuff for now, especially timescaledb functionality).

My desired setup

1. Timescale 2.2.0 running on Timescale Forge
2. Prisma / Prisma Client 2.22.0 used by AWS Lambda (will do a lambda layer / package as suggested by the docs).

Following the steps

The first step would be to create a migration which will install the timescale extension since the shadow database will run the migrations too (it is obiously already installed on Laravel forge). So the migration.sql file will look like:

CREATE EXTENSION IF NOT EXISTS timescaledb VERSION "2.2.0";

Case 1: Forge Database without shadow database

Try to run the npx prisma migrate dev command on the timescale forge database will result in an error since the command tries to create a table:

Error: Database error
Error querying the database: db error: ERROR: tsdb_admin: database prisma_migrate_shadow_db_ab8fe7fa-8d81-4493-bafe-bf5645e6d2cc is not an allowed database name
HINT: Contact your administrator to configure the "tsdb_admin.allowed_databases"

Following the suggestions in #4571 and here: https://www.prisma.io/docs/concepts/components/prisma-migrate#shadow-database the guess would be that it is not possible to use the timescale forge service for the shadow db. Which leads to:

Case 2: Forge database with shadow database on localhost

My localhost is a postgres 13 instance on windows with the timescale extension available.

Adding a shadow database in the environment and running the npx prisma migrate dev will result in an error telling that the timescaledb extension is already loaded (both instances run 2.2.0):

Error: P3006

Migration `20210505152706_` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: FEHLER: extension "timescaledb" has already been loaded with another version
DETAIL: The loaded version is "2.2.0".

If the timescale forge database has already has data a reset is suggested and if executed will result in:

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
Database error
Error querying the database: db error: ERROR: must be owner of view pg_stat_statements
   0: sql_migration_connector::best_effort_reset

Case 3 Local Timescale database

If no shadow database is explicitly given (meaning the migrate command will create one) everything works on the local machine (different to Case 1).

Case 4 Local Timescale database with explicit shadow database on the same server

If I explicitly set a shadow database on my local machine I will end up in the same error as Case 2:

Error: P3006

Migration `20210505152706_` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: FEHLER: extension "timescaledb" has already been loaded with another version
DETAIL: The loaded version is "2.2.0".

</div>