Idea: Make SQLite enforce varchar lengths via CHECK constraints

Django Internals ORM
1

I’ve just been bitten by issues migrating from sqlite to postgres. It turns out sqlite doesn’t enforce varchar constraints while postgres does. So now I have to repeat all the testing I’ve done for my app to see where it dies due to data being too long :frowning:

ChatGPT says a CHECK constraint can be added in SQLite to enforce varchar limits:

CREATE TABLE example_table (
    id INTEGER PRIMARY KEY,
    short_text VARCHAR(5) CHECK (LENGTH(short_text) <= 5),
    long_text VARCHAR(20) CHECK (LENGTH(long_text) <= 20)
);

In this table:

short_text is limited to 5 characters.
long_text is limited to 20 characters.

Perhaps the sqlite backend could be updated to add these constraints automatically to avoid such issues in future?

It seems I’m not the only one to experience this issue.

1 Like
2

It was discussed a while ago.

The additional inline constraints is an approach that we take with PositiveIntegerField and friends on backends that don’t support unsigned integer types.

A patch would be trivial

diff --git a/django/db/backends/sqlite3/base.py b/django/db/backends/sqlite3/base.py
index c7cf947800..eebd130220 100644
--- a/django/db/backends/sqlite3/base.py
+++ b/django/db/backends/sqlite3/base.py
@@ -91,6 +91,7 @@ class DatabaseWrapper(BaseDatabaseWrapper):
         "JSONField": '(JSON_VALID("%(column)s") OR "%(column)s" IS NULL)',
         "PositiveIntegerField": '"%(column)s" >= 0',
         "PositiveSmallIntegerField": '"%(column)s" >= 0',
+        "CharField": 'LENGTH("%(column)s") <= %(max_length)s'
     }
     data_types_suffix = {
         "AutoField": "AUTOINCREMENT",

But the main concerns here are backward compatiblity. SQLite tables that were created before this change and might have invalid data crept in and be prevented from being altered (most SQLite table alterations require a full table rebuild).

Given that SQLite support arbitrary length VARCHAR (they are just TEXT after all) and that we managed to enable foreign keys in past versions without causing too much trouble I think this change could be accepted.

1 Like
3

Great. Want me to create a feature request on trac?

4

The link Simon provided is the trac issue :+1:

If you like you can link Simon’s recommendation to that trac issue with a comment

5

I’ve commented, but that ticket is 11 years old and closed. It’s also a bug report not a feature request. Anyway, should I reopen it?

6

The process is usually to use the original ticket.

I’ve just posted a message on Discord to see if anyone has any concerns & wants to vote. Might be a good idea to wait a day or 2 before committing to any work? :thinking:

1 Like