Support STRICT TABLE in SQLite

[dankochetov]

[pspeter3]

Supporting strict and without rowid would be great options for creating SQLite tables.

[413n]

Is there a workaround for adding STRICT to tables right now?

[moritzruth]

Adding STRICT manually to every CREATE TABLE statement in migration files seems to work for me:

 CREATE TABLE `users` (
  `id` bigint primary key auto_increment,
  `full_name` varchar(256)
-);
+) STRICT;

[PuruVJ]

Please implement this, it's very much needed

[pekeler]

By default, SQLite doesn't care too much about column types. While Drizzle, being a TypeScript library, cares very much about strict types. So this combination feels like a semantic mismatch. For this reason, I'd argue that tables should be created strict by default, maybe with an option for sqliteTable to turn strict off.

The suggested workaround by @moritzruth is not great because this manual step can be forgotten. Since it's not possible to make a non-strict table strict via an alter table, one would have to recreate the table and migrate the data which would be a lot of work.

[joseph-lozano]

without rowid would be a great option as well, and when a non-integer primary key is defined, is probably a good default.

[413n]

When this Issue was moved/copied to Discussion, the issue got closed and the task in the roadmap was marked as Done. Is this feature already implemented and can be activated in the tables schema (meaning that would work with push without manually changing it)? Or this was moved to lower priority? or am I missing something?

[mphill]

I can try to add this as an option for a PR. I started working through the code, but there is an abstraction layer that won't work super well for adding STRICT mode as an option.

@pekeler, I agree. It should be on by default for the reasons you stated so well. Supporting STRICT is a must for production apps.

It would catch a lot of bugs, too. I just started playing with Drizzle, and I've already found a few bugs.

I just submitted this PR: #2970

STRICT mode would have caught this.

Drizzle is also generating integer bool types wrong:

 CREATE TABLE IF NOT EXISTS bool_test (
		bool_test integer DEFAULT false NOT NULL
 );

DEFAULT should be 0, not false.

Adding STRICT would also catch this.

[pspeter3]

Why do you choose to make it part of the config? It is a per table configuration.

[mphill]

@pspeter3 I stated the reason above. I don’t think there is a use case to have it in for some tables but not others, but I could be wrong.

If it was to be per table, I think it should be done as a new 4th parameter for sqliteTable.

[pspeter3]

I feel like it should be a table option since the SQL syntax is per table. We could also add support for disabling Row ID there too. See #408

[mstibbard]

How about default STRICT with opt-out at table level?

[pspeter3]

You could achieve that with a constant table options that you pass in or a factory function for defining tables that sets the default. My rationale is that table settings should exist in the table factory while database settings should exist in the database factory.