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.

[mphill]

This branch contains a possible solution to this: https://github.com/mphill/drizzle-orm/tree/strict-support

The proposal is that this be part of the base config, so every table created is subject to this setting, including the migration tables. It would prevent this from being forgotten if it's handled at this level and not a SQLite table creation option.

The downside is that the SQLite implementation has now leaked into the Config type.

drizzle.config.ts

import type { Config } from 'drizzle-kit';
export default {
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'sqlite',
  driver: 'expo', // <--- very important
  strict: true // <--- 🚀 strict table creation
} satisfies Config;

[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.