[VAX-965] Modify generator to introspect PG DB

[electric-sql]

[VAX-965] Modify generator to introspect PG DB

[alco]

@js2702 Yes, we're adding support for date, time, timestamp, and timestamptz types to DAL. They will all be stored as TEXT in SQLite and converted to/from the JS Date object.

Example values in SQLite:

• date: 2023-08-24
• time: 17:48:51.122
• timestamp: 2023-08-24 17:48:52
• timestamptz: 2023-08-24 17:48.53.000009Z

[alco]

@js2702 In Postgres, timestamp is defined as represnting local time without any reference time zone attached to it. We're treating it as such in DAL too. So when DAL builds a new Date object from a timestamp value, it takes the timestamp and passes it verbatim to Date's constructor:

> d = new Date("2023-09-20 11:06:00")
Date Wed Sep 20 2023 11:06:00 GMT+0300 (Eastern European Summer Time)

> d.toString()
"Wed Sep 20 2023 11:06:00 GMT+0300 (Eastern European Summer Time)" 

That's the date in my time zone. If you initialize a Date object using the same string, you'll get the same time of day but it will be in your time zone.

Internally, Date stores the time in UTC, so if I explicitly request a UTC-based string from it, the time of day will be different:

> d.toUTCString()
"Wed, 20 Sep 2023 08:06:00 GMT" 

We believe this behaviour is consistent with how timestamp works in Postgres. For an objective point in time that can be correctly interpreted in any time zone one should use the timestamptz type.

[js2702]

I'm guessing one of the reasons is the timestamp data type in Postgresql. Are you planning to make the DAL client work with Date objects in Javascript? And convert them to text internally? We are having the same discussion for the Dart client. Ideally to prevent the user inserting a non date string into the sqlite.

[js2702]

So when storing that timestamp, without the Z for UTC, would dates be treated always as UTC in the DAL? Or would this case be treated as local time?

[tyrauber]

hey @kevin-dp, are you familiar with postgraphile? There is a lot of similarity between projects I think, specifically in regards to JWT and RLS usage in postgres. Postgraphile uses pg-introspection to do a sql introspection query that generates the graphql schema used in graphiql. Perhaps, electric-sql could do something similar?

[kevin-dp]

hey @kevin-dp, are you familiar with postgraphile? There is a lot of similarity between projects I think, specifically in regards to JWT and RLS usage in postgres. Postgraphile uses pg-introspection to do a sql introspection query that generates the graphql schema used in graphiql. Perhaps, electric-sql could do something similar?

Hi @tyrauber, just noticing your comment now. Interesting, will definitely flag it up for consideration. Thanks!