SeaORM SQLite types are now non lossy

[jondot]

See https://github.com/SeaQL/sea-query/pull/735/files

we can now attempt to have sqlite back as the primary “initial app” store, as it should now keep information about timestamp columns.

In the past the SQLite implementation lost type information as the migrations were applied and so when generating back entities we got compilation errors. This made us move to Postgres in development as a standard, and move SQLite into the shadows as “supported but..”.

We can now return SQLite to development configuration in favor of helping people get started more easily with less friction.

[simonsan]

This would be awesome!

[Borderliner]

@jondot I think SeaORM needs to reach v0.31.0 at least (v0.31.0-rc4 as of writing), the last release v0.30.7 doesn't include the aforementioned commit. Maybe there are other changes that loco needs to adapt to.

[jondot]

I've just finished testing this out, actually, the Sqlite variant works well, it required upgrading sea-orm-cli to rc3 as well, and types get discovered correctly.

Where I bumped into a wall is seamlessly moving between Postgres and Sqlite.

• Postgres generated DateTimeWithTimezone
• Sqlite generated DateTimeUtc

And since we have user-land code, these types could not co-exist. I played with everything a bit but had to take a guess, where even if I use sea-orm-cli it doesn't use the same bits the sea-orm core is using, which is why I was getting this difference. Since I am in no-mans-land (manually upgrading to rc versions all around), I'm OK with this, and I'll just park this issue and wait for sea-orm to advance a bit more with their versions before retrying.

/cc @billy1624

I'm optimistic this will eventually work and we'll return to co-driving both Sqlite and Postgres

Some references to capture time of writing:

https://github.com/SeaQL/sea-orm/blob/145bfa02a923722a12035c26a6195bc60580b351/src/driver/sqlx_sqlite.rs#L404
https://github.com/SeaQL/sea-orm/blob/145bfa02a923722a12035c26a6195bc60580b351/sea-orm-codegen/src/entity/column.rs#L62
https://github.com/SeaQL/sea-orm/blame/145bfa02a923722a12035c26a6195bc60580b351/src/driver/sqlx_postgres.rs#L694
#530

[billy1624]

Hey everyone, that's correct! SQLite types is now non-lossy

• https://github.com/SeaQL/sea-query/blob/71055a3ff53e3f673e902a8c53817866a945d800/src/backend/sqlite/table.rs#L163-L167
• Remap SQLite types SeaQL/sea-query#735

[schungx]

Postgres generated DateTimeWithTimezone

I'm pretty sure the CLI before generates Postgres columns as DateTimeWithoutTimezone with the time being automatically converted into UTC (which incidentally matches SQLite's DateTimeUtc).

Not sure if the latest CLI still generates this data type since I have ditched the CLI in favor of editing the migration files myself.