diesel print-schema can't generate i64-compatible table definitions for STRICT sqlite tables

[legeana]

Setup

Versions

• Rust: 1.86.0
• Diesel: 2.2.10
• Database: libsqlite3-sys 0.33.0
• Operating System: Linux 6.14.9-arch1-1

Feature Flags

• diesel: returning_clauses_for_sqlite_3_35, sqlite

Problem Description

This issue is similar to #852 but applies to all INTEGER fields.

I have 2 STRICT tables:

CREATE TABLE IF NOT EXISTS updates (
    id INTEGER PRIMARY KEY AUTOINCREMENT
) STRICT;

CREATE TABLE IF NOT EXISTS files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- Skipped unrelated fields.
    update_id INTEGER
        DEFAULT (NULL)
        REFERENCES updates (id)
        ON DELETE CASCADE
) STRICT;

Due to STRICT table restrictions I can't use BIGINT as a data type, only INTEGER and INT are available.
In Sqlite INTEGER corresponds to an up to 64bit integer, but diesel print-schema maps it to i32.

What are you trying to accomplish?

• I would like to be able to generate a diesel schema via print-schema automatically.
• I would like to keep using STRICT. This helps me prevent bad database modifications.
• I would prefer to avoid --import-types workarounds.

What is the expected output?

$ diesel --database-url=foo.db print-schema --sqlite-integer-primary-key-is-bigint
// @generated automatically by Diesel CLI.

diesel::table! {
    files (id) {
        -- This is also implicitly NOT NULL with STRICT, but this is outside of this bug's scope.
        id -> BigInt,
        update_id -> Nullable<BigInt>,
    }
}

diesel::table! {
    updates (id) {
        -- This is also implicitly NOT NULL with STRICT, but this is outside of this bug's scope.
        id -> BigInt,
    }
}

diesel::joinable!(files -> updates (update_id));

diesel::allow_tables_to_appear_in_same_query!(
    files,
    updates,
);

What is the actual output?

$ diesel --database-url=foo.db print-schema --sqlite-integer-primary-key-is-bigint
// @generated automatically by Diesel CLI.

diesel::table! {
    files (id) {
        id -> Nullable<BigInt>,
        update_id -> Nullable<Integer>,
    }
}

diesel::table! {
    updates (id) {
        id -> Nullable<BigInt>,
    }
}

diesel::joinable!(files -> updates (update_id));

diesel::allow_tables_to_appear_in_same_query!(
    files,
    updates,
);

Are you seeing any additional errors?

No.

Steps to reproduce

# 1. Create tables.
$ sqlite3 foo.db
SQLite version 3.50.0 2025-05-29 14:26:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS updates (
    id INTEGER PRIMARY KEY AUTOINCREMENT
) STRICT;

CREATE TABLE IF NOT EXISTS files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    update_id INTEGER
        DEFAULT (NULL)
        REFERENCES updates (id)
        ON DELETE CASCADE
) STRICT;

# 2. Generate schema
$ diesel --database-url=foo.db print-schema --sqlite-integer-primary-key-is-bigint
// @generated automatically by Diesel CLI.

diesel::table! {
    files (id) {
        id -> Nullable<BigInt>,
        update_id -> Nullable<Integer>,
    }
}

diesel::table! {
    updates (id) {
        id -> Nullable<BigInt>,
    }
}

diesel::joinable!(files -> updates (update_id));

diesel::allow_tables_to_appear_in_same_query!(
    files,
    updates,
);

The generated schema is bad for the following reasons:

1. files::update_id is Integer, but updates::id is BigInt.
2. Without using --sqlite-integer-primary-key-is-bigint both fields will be Integer.
3. It is impossible to use BIGINT together with STRICT.

$ sqlite3 foo.db
SQLite version 3.50.0 2025-05-29 14:26:00
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS bigint_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- unrelated fields
    update_id BIGINT
        DEFAULT (NULL)
        REFERENCES updates (id)
        ON DELETE CASCADE
) STRICT;
Parse error: unknown datatype for bigint_files.update_id: "BIGINT"

Possible solutions

sqlite_integer_is_bigint (and possibly sqlite_implicit_not_null_primary_keys)

This is similar to #3940, allows the most flexibility.

The downside is discoverability and the need to set up all these compatibility flags. Now will be 3, maybe other people will find more.

sqlite_strict_tables flag

Instead of creating a multitude of flags per behaviour, just assume if a user uses STRICT tables, all of their tables are STRICT. And apply the changes accordingly:

• Use diesel::BigInt for INTEGER.
• Avoid Nullable for primary keys.
• Add other behaviours I missed here later.

Figure out a way to detect STRICT tables

I understand your concern about implementing an SQL parser, I don't have a good suggestion how to detect STRICT reliably. pragma table_info isn't helpful.

sqlite> pragma table_info('files')
   ...> ;
0|id|INTEGER|0||1
1|update_id|INTEGER|0|NULL|0

Summary

• I can't use BIGINT in the Sqlite schema because of STRICT.
• The generated schema contains BigInt for primary keys, but Integer for foreign keys.
• I can't think of a possible combination of a STRICT schema and print-schema flags that would generate a good schema.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate

[weiznich]

I converted this to a discussion as this is a feature request and not a bug report. This is just how the feature is currently supposed to work. Any additional configuration to change this would be a new feature.

As for your suggestions:

sqlite_integer_is_bigint (and possibly sqlite_implicit_not_null_primary_keys)

For the reasons you already described I would rather try to avoid adding more such specific config flags.

Use diesel::BigInt for INTEGER.

It's not clear that this is the right solution for everyone. There are use-cases that might want to use 32-bit integers for whatever reason.

Avoid Nullable for primary keys.

Does SQLite actually guarantee that for STRICT tables?

Figure out a way to detect STRICT tables

If you have suggestions that don't involve re implementing a SQL parser we can certainly talk about this. That said this still wouldn't mean that using the INTEGER -> i64 mapping is the correct one for everyone that uses a strict table. This still might depend on their use-cases.

I believe the right way to address this is to make the type mapping much more configurable via the diesel.toml file as described here: #4113 (comment) That's noting I personally have the capacity to design or implement on my own in the near future, but I would accept contributions for such a feature.

[legeana]

as this is a feature request and not a bug report

Yea sorry, I debated whether this is a bug or a FR, and decided it is a bug because of how wrong the output is for STRICT tables. I don't mind if you treat this as a FR.

For the reasons you already described I would rather try to avoid adding more such specific config flags.

I agree, the reason I brought this up was I couldn't think of anything else.

It's not clear that this is the right solution for everyone. There are use-cases that might want to use 32-bit integers for whatever reason.

And the only way to achieve that is to use per-field overrides.

Does SQLite actually guarantee that for STRICT tables?

From https://sqlite.org/stricttables.html

Columns that are part of the PRIMARY KEY are implicitly NOT NULL. However, even though the PRIMARY KEY has an implicit NOT NULL constraint, when a NULL value is inserted into an INTEGER PRIMARY KEY column, the NULL is automatically converted into a unique integer, using the same rules for INTEGER PRIMARY KEY on ordinary, non-strict tables.

sqlite> SELECT * FROM updates;
sqlite> INSERT INTO updates (id) VALUES(NULL);
sqlite> SELECT * FROM updates;
1
sqlite> INSERT INTO updates (id) VALUES(NULL);
sqlite> INSERT INTO updates (id) VALUES(NULL);
sqlite> SELECT * FROM updates;
1
2
3

You can insert a NULL, but you can't get a NULL in a query. So kinda?

to make the type mapping much more configurable via the diesel.toml

This would address the issue quite well. If we could have a flexible per-field patchlist then one could regenerate a schema with new fields and the old fields would continue to work as is.

I can't commit to developing this myself yet as I just started experimenting with diesel for my personal project and I would need to get a better feel for diesel first. But I do like this suggestion.