Support SQLite STRICT tables

[i-g-k]

Issue Creation Checklist

• [x] I understand that my issue will be automatically closed if I don't fill in the requested information
• [x] I have read the contribution guidelines

Feature Description

Describe the feature you'd like to see implemented

Support SQLite STRICT tables

Currently - { "sequelize": "^6.29.0", "sqlite3": "^5.1.4" } - if I define a model with an attribute, and create an instance of that model using a value whose type is incompatible with the type of the attribute, the operation succeeds. SQLite documents this as a feature, but provides the STRICT table-option to disable it. I request that Sequelize explicitly support STRICT tables in SQLite - in particular, provide a mechanism by which to create them.

The following code defines two models, Fruit and Vegetable. It allows Sequelize to generate the schema for Fruit, but provides its own for Vegetable - including STRICT - before sync().

import sqlite3 from "sqlite3";
import { Sequelize, DataTypes } from "sequelize";

const sze = new Sequelize({
  dialect: "sqlite",
  storage: "main.db",
  dialectOptions: {
    mode: sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE | sqlite3.OPEN_FULLMUTEX
  }
});

const Fruit = sze.define('Fruit', {
  name: DataTypes.STRING,
});

const Vegetable = sze.define('Vegetable', {
  name: DataTypes.STRING,
});

const [res, met] = await sze.query(
  "CREATE TABLE `Vegetables` (" +
  "`id` INTEGER PRIMARY KEY AUTOINCREMENT, " +
  "`name` TEXT, " +
  "`createdAt` TEXT NOT NULL, " +
  "`updatedAt` TEXT NOT NULL" +
  ") STRICT;"
);

await sze.sync();

console.log("Synced");


console.log("Fruit str");
console.log(!!await Fruit.create({ name: "lychee" }));

console.log("Fruit buf");
console.log(!!await Fruit.create({ name: Buffer.from([100]) }));


console.log("Vege str");
console.log(!!await Vegetable.create({ name: "potato" }));

console.log("Vege buf");
console.log(!!await Vegetable.create({ name: Buffer.from([101]) }));

It demonstrates the behavior of each table; both Fruit are inserted, and the one called <Buffer 64>, which is a value that cannot be losslessly converted to TEXT is inserted with a BLOB value in the name column. The second vegetable insert fails, because STRICT prevents this mixture.

Executing (default): CREATE TABLE `Vegetables` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` TEXT, `createdAt` TEXT NOT NULL, `updatedAt` TEXT NOT NULL) STRICT;
Executing (default): SELECT name FROM sqlite_master WHERE type='table' AND name='Fruits';
Executing (default): CREATE TABLE IF NOT EXISTS `Fruits` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `name` VARCHAR(255), `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL);
Executing (default): PRAGMA INDEX_LIST(`Fruits`)
Executing (default): SELECT name FROM sqlite_master WHERE type='table' AND name='Vegetables';
Executing (default): PRAGMA INDEX_LIST(`Vegetables`)
Synced
Fruit str
Executing (default): INSERT INTO `Fruits` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);
true
Fruit buf
Executing (default): INSERT INTO `Fruits` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);
true
Vege str
Executing (default): INSERT INTO `Vegetables` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);
true
Vege buf
Executing (default): INSERT INTO `Vegetables` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);
node:internal/process/esm_loader:97
    internalBinding('errors').triggerUncaughtException(
Error
  [stack trace removed]
  name: 'SequelizeUniqueConstraintError',
  errors: [],
  parent: [Error: SQLITE_CONSTRAINT: cannot store BLOB value in TEXT column Vegetables.name] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT',
    sql: 'INSERT INTO `Vegetables` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);'
  },
  original: [Error: SQLITE_CONSTRAINT: cannot store BLOB value in TEXT column Vegetables.name] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT',
    sql: 'INSERT INTO `Vegetables` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);'
  },
  fields: [],
  sql: 'INSERT INTO `Vegetables` (`id`,`name`,`createdAt`,`updatedAt`) VALUES (NULL,$1,$2,$3);'
}

It should be possible to create the table Vegetables without query(sqlText).

Describe why you would like this feature to be added to Sequelize

Implementation of this feature requires modifications, not just extensions. For example, the SQLite query generation function for table creation needs to return a different string.

Is this feature dialect-specific?

• [ ] No. This feature is relevant to Sequelize as a whole.
• [x] Yes. This feature only applies to the following dialect(s):
  • sqlite

Would you be willing to resolve this issue by submitting a Pull Request?

• [ ] Yes, I have the time and I know how to start.
• [x] Yes, I have the time but I will need guidance.
• [ ] No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• [ ] No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in implementing my feature.

---

Indicate your interest in the addition of this feature by adding the 👍 reaction. Comments such as "+1" will be removed.

[ephys]

Sequelize 7's datatypes are strict mode compliant so all we're missing is an sqlite-specific "strict" option

[reorx]

Is this feature about to implement? Sometimes I found type validation on the db side was still useful even the ORM has it's own validation mechanism, for example when calling .update(properties, whereCondition), it won't check the type of properties by the table schema.

[WikiRik]

This one is open for the community to take up. It should be a relatively easy one, so I'l mark this as 'good first issue'

[kojish2018]

I’m interested in helping out with this. Is this issue still open for contributions?

[WikiRik]

I’m interested in helping out with this. Is this issue still open for contributions?

@kojish2018 yes to this and the other issues you've sent the same reply to