Declaring data types in SQLite

Question

I'm familiar with how type affinity works in SQLite: You can declare column types as anything you want, and all that matters is whether the type name contains "INT", "CHAR", "FLOA", etc. But is there a commonly-used convention on what type names to use?

For example, if you have an integer column, is it better to distinguish between TINYINT, SMALLINT, MEDIUMINT, and BIGINT, or just declare everything as INTEGER?

So far, I've been using the following:

• INTEGER
• REAL
• CHAR(n) -- for strings with a known fixed with
• VARCHAR(n) -- for strings with a known maximum width
• TEXT -- for all other strings
• BLOB
• BOOLEAN
• DATE -- string in "YYYY-MM-DD" format
• TIME -- string in "HH:MM:SS" format
• TIMESTAMP -- string in "YYYY-MM-DD HH:MM:SS" format

(Note that the last three are contrary to the type affinity.)

Answer 1

Since SQLite is typeless, use whatever types make it easier for you to see what the schema looks like. Or you can match the types to your codebase.

Answer 2

I'm going to go with Kevin on this one. In short, knock yourself out. Make up brand new areas of mathematics if it suits your schema. Use the classnames of your ORM. Or name every type (except the PRIMARY KEY INTEGER ones) for ex-girlfriends. In the end SQLite is more about how you access and use the data.

Answer 3

I would recommend not using self-defined types. I have observed in version 3.5.6 that types not already defined could sometimes cause an INSERT command to be refused. Maybe 1 out of 1000. I don't know if this was addressed since.

In any case, there is no sizing advantage in typing a column TINYINT or SMALLINT. The only advantage would be outside SQLite, for either parsing your column types with another program or to satisfy your personal need for tidiness. So I strongly recommend using the base types defined by SQLite and sticking to those.