Getting to know SQLite3

I’m finding SQLite3 super useful lately. Its great for any kind of experimentation and quick and painless way to persist data. There are just a few things I needed to wrap my head around to start to feel commfortable with it.

As with most things on Debian based systems, installing is really easy:
sudo apt-get install sqlite3 libsqlite3-dev

My first real question was about datatypes. What does SQLite support? It was a bit mysterious to read that SQLite has 5 datatypes (null, integer, real(float), text, blob) but then see a MySQL style create table statement like this work:

create table people(
  id integer primary key autoincrement,
  name varchar(30),
  age integer,
  awesomeness decimal(5,2)

How are varchar and decimal able to work? Worse still, why does something like this work:

create table people(
  id integer primary key autoincrement,
  name foo(30),
  age bar(100000000),
  awesomeness baz

As it happens SQLite maps certain terms to its internal datatypes:

If the declared type contains the string “INT” then it is assigned INTEGER affinity.

If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity. Notice that the type VARCHAR contains the string “CHAR” and is thus assigned TEXT affinity.

If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity NONE.

If the declared type for a column contains any of the strings “REAL”, “FLOA”, or “DOUB” then the column has REAL affinity.

Otherwise, the affinity is NUMERIC.

So the foo, bar and baz columns above, being unrecognized, would have received an affinity of numeric, and would try to convert whatever was inserted into them into a numeric format. You can read more about the in’s and outs of type affinities in the docs, but the main thing to grasp up front is that syntax-wise you can usually write whatever you are comfortable with and it will probably work, just keep in mind that affinities are being set and you will know where to look when you see something strange happening. For the most part this system of affinities does a good job of not violating your expectations regardless of what database you are used to using.

The other thing to get is that SQLite determines the datatype from the values themselves. Anything in quotes is assumed to be a string, unquoted digits are integers, or if they have a decimal, a “real” while a blob is a string of hex digits prefixed with an x: x’00ff’.

So the safest/easiest thing might just be to leave the column definitions out altogether so they will all have an affinity of none and let the values speak for themselves.

The rest of my learning about SQLite is really a grab bag of little goodies:

Getting meta info about tables, indexes or the database itself is done with a pragma statement.
For example, if I want information about the table data:

sqlite> pragma table_info(people);

You can get that same list of info within Ruby like so (after running “gem install sqlite3”):

require 'sqlite3'
@db ="cats.db")
table_name = "cats"

A complete list of pragma statements can be found in the docs.

To open or create a database simply run sqlite3 with the name of the file:

mike@sleepycat:~☺ sqlite3 cats.db

And finally if you have a file with sql statements you would like to run on a database:

mike@sleepycat:~☺ sqlite3 cats.db < insert_all_the_cats.sql

Its been good to get to know SQLite3 a little better. Before this I had only really come in contact with it through my Rails development work and knew it only as the in-memory test database or the one I would use when I couldn’t be bothered to set up a “real” database. The more I look at it the more its seems like a really powerful and useful tool.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s