Problem

TimescaleDB is an incredible postgreSQL extension that allows you to work with time-series data in native SQL. It's amazing for IOT heavy projects and there's plenty of other use cases.
It's possible to use TimescaleDB and prisma together with some manual work and using raw queries.

• This was already possible with prisma1 as stated here : TimeScaleDB connector prisma1#1672 (comment)

• This has been made easier with the implementation of composite keys : Referencing primary composite keys #1389 🎉

However it is still needed to manually install the extension, then manually convert tables into hypertables using raw sql, and some TimescaleDB features are implemented using Postgres Functions.

I haven't tested it personally, but going by the docs prisma should work out of the box once the hypertable is properly created when it comes to writing data.
For reading data, falling back to raw sql is needed for some of the features implemented with functions (docs here).

Suggested solution

It would be nice if the prisma schema supported the following :

• Ability to add the extension from the prisma schema directly
• Ability to define a hypertable directly, so that prisma migrate would handle them without having to fallback to raw sql after running a migration

Regarding the prisma client, it would be great if it supported the use of functions.
I don't know what the syntax would look like, but in raw sql it's pretty straightforward:

SELECT location, last(temperature, time)
  FROM conditions
  GROUP BY location;

Function support might need it's own issue thread, but I'm curious to see what a potential client API would look like.
Not that in this case, prisma doesn't support groupBy yet (prisma/prisma-client-js#1)

It might be worth mentioning than the schema could also support function creation as well, however this would be out of the scope of the TimescaleDB support, as the function are created when the extension is installed.

Alternatives

It's still possible to use raw sql and manually handle the hypertable creation/functions call

Additional context

It would be really nice to have a more generic approach to add some of the most popular extensions directly though the prisma schema, postGIS comes to mind. Right now if you want to use prisma + postgis + timescale together, there's a lot of manual wiring to do.

Relevant :

• Postgis support : PostGIS/GIS support #2789
• Native DataTypes : EPIC: Support for native database types #446

I do not know if something needs to happen on the timescaleDB end, as it is just a regular postgreSQL extension (as far as I know). However @akulkarni from the timescaleDB team said he was willing to help.