Temporal Data / SYSTEM VERSIONED tables

[saraveti]

Problem

npx primsa introspect is not recognizing the 'SYSTEM VERSIONED' tables.

Suggested solution

Prisma should support versioned tables as it is a key feature Maria DB to do the audit of record changes over a period of time.

Alternatives

I tried enabling version WITH SYSTEM VERSIONING at both table level and column level as par Maria DB doc, still introspect did not generate the prisma.schema with those table modal.

Additional context

This feature is called 'Temporal Data Tables'. Here is the link to Maria DB documentation.

[janpio]

Some research questions:

• Does this exist only in Maria DB or also other database system? If so, same behavior?
• Do these tables possibly already work if you just create a model that treats the table a a normal table?

If yes, TODOs on the technical investigation side for minimal support would be:

• Extract all possible variations of these tables from documentation
• Create schema at https://github.com/prisma/database-schema-examples in a new mariadb folder
• Import tables into Maria DB database
• See what Introspection does with it right now and what is blocking it from showing them as normal models
• Fix

[semopz]

@saraveti curious how you got around this issue. Are you still using Prisma?

Some research questions:

• Does this exist only in Maria DB or also other database system? If so, same behavior?
  ...

Was introduced to SQL Server 2016 and is part of the SQL:2011 spec. Pages 36 to 51 give a quick overview and nice starting point I think. When this is implemented, it would be nice if the Prisma documentation provides examples of JOIN queries between system-versioned tables with an AS OF clause (as opposed to examples of how to query just a single table)

[saraveti]

@semopz

I am continuing the Prisma use in my project with MariaDB. We are in the initial phase.

There are 2 variants in Temporal Data Tables.- 1. System Version Tables and 2. Application Time Periods.

Currently for phase -1, I am using Application Time Periods feature, Prisma is able to introspect them, I am writing the custom resolvers to do the specific syntax with RawSql support.

For next phase of my project I would like to use System Version Tables as well. Waiting to here from Prisma on this support.

[tobimax]

I would also like this feature supported as most database systems have temporal table support, MySQL, SQL Server, Postgres, Oracle etc. & I have a project at the moment where I could really use them.

[cmdaniel]

Temporal Tables are a must have resource now a days, due to the fact that you can have complete auditable changes by design, with minimal programming effort. I have projects on Healthcare and Telecom sectors and both use this features intensely.
I am in a almost "dead end" right now, because selected Prisma2 for a new project - because Prisma2 is awesome - but right now this lack of support might force me to back to dotnet core.

[janpio]

On PostgreSQL this seems to be possible via a bunch of different extensions. Multiple of them are linked in this HN thread: https://news.ycombinator.com/item?id=26748096

https://pgxn.org/dist/temporal_tables/#:~:text=A%20temporal%20table%20is%20a,known%20as%20transaction%2Dtime).
https://github.com/arkhipov/temporal_tables
https://github.com/nearform/temporal_tables
https://github.com/scalegenius/pg_bitemporal

Might also be coming as a built in feature with Pg15: https://blog.dbi-services.com/temporal-tables-for-postgresql-15/

[janpio]

Is there anything besides introspecting these tables and being able to represent them in Prisma schema correctly (so they can also be migrated) that would be part of this feature?

[cmdaniel]

On my specific Use Cases I just need to be able to do DML operations as usual. On Sql Server after changing the table to Temporal Table, the insert stopped working (a message related with GENERATED columns).

When this feature is activated, it will automatically timestamp and preserve the history of all changes in a separated table.
Usually I will just try to SELECT the auto generated history table on reports and audits, usually directly on Sql Server.

So, I not even need to represent them on Prisma, and I think this is a very common scenario. If introspecting the whole feature is a long effort, because of different databases, just being able to activate the feature without completely disrupt the basic operation is a very good progress.

For testing, you can use the following script to enable a system-versioned temporal table on SQL SERVER.

-- ACTIVATE TEMPORAL TABLE ON SQL SERVER FOR TABLENAME

ALTER TABLE dbo.TABLENAME
ADD startTime DATETIME2

 
ALTER TABLE dbo.TABLENAME
ADD endTime DATETIME2


UPDATE dbo.TABLENAME SET startTime = '19000101 00:00:00.0000000', endTime = '99991231 23:59:59.9999999'
GO

ALTER TABLE dbo.TABLENAME
ALTER COLUMN startTime DATETIME2 NOT NULL
GO
 
ALTER TABLE dbo.TABLENAME
ALTER COLUMN endTime DATETIME2 NOT NULL
GO


ALTER TABLE dbo.TABLENAME
ADD PERIOD FOR SYSTEM_TIME (startTime, endTime)

	
ALTER TABLE dbo.TABLENAME
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TABLENAME_History, DATA_CONSISTENCY_CHECK = ON))


SELECT *
FROM [dbo].[TABLENAME]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011'



--- REMOVE TEMPORAL TABLE
ALTER TABLE [dbo].[TABLENAME] SET (SYSTEM_VERSIONING = OFF);
/*Optionally, DROP PERIOD if you want to revert temporal table to a non-temporal*/
ALTER TABLE [dbo].[TABLENAME]
DROP PERIOD FOR SYSTEM_TIME;
```

[cmdaniel]

Additionally, I would like to express an opinion: Temporal Table is a really powerful and easy to use feature, that has been available for a while and on many different databases, but many developer are not aware of this feature.

If it is fully integrated on Prisma and, by simple data annotating the schema you activate/deactivate a complete history tracking without any additional effort, it will looks like magic!

[jonsandg]

For whomever it might help, I'll document the steps/workarounds I had to use to get temporal tables working on SQL server.

Given prisma schema:

// schema.prisma
datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id         String  @id @default(uuid())
  name       String
}

Edit the resulting migration so it looks something like so:

-- migration.sql

BEGIN TRY

BEGIN TRAN;

-- Create schema for temporal tables
IF NOT EXISTS ( SELECT  *
                FROM    sys.schemas
                WHERE   name = N'his' )
    EXEC('CREATE SCHEMA [his]');

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [User_pkey] PRIMARY KEY CLUSTERED ([id]),

    -- This is new
    [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START, 
    [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END, 
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = his.UserHistory));


COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

The history tables are placed on another schema (his) to make them invisible to prisma.
I updated the prisma model to mirror what the database will look like:

// schema.prisma
model User {
  id         String  @id @default(uuid())
  name       String
  ValidFrom  DateTime @ignore
  ValidTo    DateTime @ignore
}

@ignore makes prisma ignore the fields when generating the client (to some extent), as otherwise you would be required to provide ValidFrom and ValidTo when creating a user. Unfortunately it seems prisma does not generate the functions for creating and updating the model when you @ignore a required (non-nullable) field (#13467 ). If you make the field nullable with ValidTo DateTime? @ignore the client generation works but then prisma will try to make the field nullable when you create a new migration. (ValidFrom and ValidTo needs to be non-nullable for temporal tables to work)

To get around this I had to create a separate schema for client generation, that looks like the original schema.

// client_schema.prisma
model User {
  id         String  @id @default(uuid())
  name       String
}

When using temporal tables prisma migrate reset stops working because you can't drop tables that have SYSTEM_VERSIONING turned on. So I had to create a separate SQL script to run before resetting the database:

-- reset.sql

IF EXISTS (
        SELECT 1
        FROM sys.tables
        WHERE name = 'User'
            AND temporal_type = 2
        )
        EXEC('ALTER TABLE [dbo].[User] SET ( SYSTEM_VERSIONING = OFF)');
DROP TABLE IF EXISTS [dbo].[User]
DROP TABLE IF EXISTS [his].[UserHistory]

Relevant package.json scripts

"database:reset": "prisma db execute --schema ./prisma/schema.prisma --file ./prisma/reset.sql && yarn prisma:generate && prisma migrate reset --skip-generate",
"database:push": "prisma db push --skip-generate && yarn prisma:generate && prisma db seed",
"prisma:generate": "prisma generate --schema ./prisma/client_schema.prisma",

Hope this helps someone. If anyone has ideas on how to make this less cumbersome I'd love to hear it :)

[salcio]

@jonsandg Do you use migrations ? I'm getting

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: Drop table operation failed on table 'table' because it is not a supported operation on system-versioned temporal tables.

Probably because migrate dev applies migration and then tries to do reset but that cannot drop tables as you mentioned.

Any way to run reset.sql as part of migrate dev ?

[jonsandg]

@salcio I'm guessing it's because you have changes in your database from using db push? If so, manually resetting with the database:reset script above and then running migrate dev solves it. For me at least :)

[salcio]

@jonsandg hmm I'm not using db push.

When migrate dev runs I think it applies migrations on shadow db and then resets it (in my setup as I'm using Azure).

But this only happens after there is at least one system_verssioned table published/created - it happens when I try to generate new migration.

[jonsandg]

@salcio Ah are you using a manual shadow database? I'm not doing that and don't really know how they work, but I think they maybe are reset by dropping tables (like normal migrate reset) instead of the entire database? (Like I'm guessing non-cloud shadow databases are) Maybe you can point that reset.sql script towards the shadow database before running the migrate command?

[salcio]

@jonsandg yeah I was thinking the same but I think it's the actual migrate dev command that does the migration application and then reset. So I would need to be able to hook up to migrate dev command and run the script before it tries to reset.

The shadow db is empty just before i run migrate dev and it fails within this commend.

I've switch to local db (automatic shadow dbs) just to test it and it seems to run fine - at least the migration creation process.

[janpio]

The non automatic shadow database in connection to the non supported temporal tables might cause problems here - if you ahve a simple reproduction of this @salcio, we would appreciate a new issue. (Link to this issue here so we know what this is about)

[salcio]

@janpio I'll try to create a simple reproduction for this.
As a quick description the issue is that migrate dev runs the reset of shadow database - and that fails because temporal tables cannot be removed/dropped as long as they are system_versioned.

I guess similar problem will happen every time there is an issue with dropping the tables during the reset. It would be nice if there was a way to hook into shadow database reset process and run custom scripts - something like down migration or something.

[kusigit]

Some weeks ago I started a pull request for TypeORM. May some lines of code could help you to integrate this feature in Prisma.
typeorm/typeorm#10152