Unrestricted Length of SQLite3 Varchar

In one of my pet projects I have a SQLite3 database which contains a column defined as varchar(255), and is displayed as such when I execute the .schema command inside the sqlite3 console.

I wrote a unit test for my Rails model which inserts a 257 character string into that field, and expected it to trigger an error. I was therefore very surprised when the test failed due to the model both being valid and saving successfully. After poking around a bit I found this:

SQLite does not enforce the length of a VARCHAR. You can declare a VARCHAR(10) and SQLite will be happy to let you put 500 characters in it. And it will keep all 500 characters intact – it never truncates.

So if you want to enforce length restrictions on varchar fields in SQLite3, you must enforce them in your model code.

2 thoughts on “Unrestricted Length of SQLite3 Varchar

  1. Indeed. Just bumped into that one in a Rails project as well. I must say it is worth a WTF of the day!
    Thanks for sharing.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">