# Idea: Make SQLite enforce varchar lengths via CHECK constraints

**URL:** <https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101>\
**Category:** ORM\
**Created:** [July 19, 2024, 9:13am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101 "2024-07-19T09:13:36Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![billyt](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/billyt/32/22715_2.png) [@billyt](https://forum.djangoproject.com/u/billyt)\
**Post date:** [July 19, 2024, 9:13am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/1 "2024-07-19T09:13:36Z")

</div>

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 ☹

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

```auto
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](https://forum.djangoproject.com/t/cant-transfer-data-from-sqlite3-to-postgres/18810) to experience this issue.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [July 20, 2024, 12:23am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/2 "2024-07-20T00:23:46Z")

</div>

It was discussed [a while ago](https://code.djangoproject.com/ticket/21471).

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

---

<div class="post-metadata">

**Author:** ![billyt](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/billyt/32/22715_2.png) [@billyt](https://forum.djangoproject.com/u/billyt)\
**Post date:** [July 22, 2024, 5:10am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/3 "2024-07-22T05:10:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [July 22, 2024, 6:20am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/4 "2024-07-22T06:20:55Z")

</div>

The link Simon provided is the trac issue 👍

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

---

<div class="post-metadata">

**Author:** ![billyt](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/billyt/32/22715_2.png) [@billyt](https://forum.djangoproject.com/u/billyt)\
**Post date:** [July 22, 2024, 7:05am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/5 "2024-07-22T07:05:25Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![shangxiao](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/shangxiao/32/10313_2.png) [@shangxiao](https://forum.djangoproject.com/u/shangxiao)\
**Post date:** [July 22, 2024, 7:36am UTC](https://forum.djangoproject.com/t/idea-make-sqlite-enforce-varchar-lengths-via-check-constraints/33101/6 "2024-07-22T07:36:21Z")

</div>

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? 🤔