What is the difference between related SQLite data-types like INT, INTEGER, SMALLINT and TINYINT?

SQLite, technically, has no data types, there are storage classes in a manifest typing system, and yeah, it’s confusing if you’re used to traditional RDBMSes. Everything, internally, is stored as text. Data types are coerced/converted into various storage locations based on affinities (ala data types assigned to columns).

The best thing that I’d recommend you do is to :

  1. Temporarily forget everything you used to know about standalone database datatypes

  2. Read the above link from the SQLite site.

  3. Take the types based off of your old schema, and see what they’d map to in SQLite

  4. Migrate all the data to the SQLite database.

Note: The datatype limitations can be cumbersome, especially if you add time durations, or dates, or things of that nature in SQL. SQLite has very few built-in functions for that sort of thing. However, SQLite does provide an easy way for you to make your own built-in functions for adding time durations and things of that nature, through the sqlite3_create_function library function. You would use that facility in place of traditional stored procedures.

Leave a Comment