SQLite New Features

If you haven't used SQLite in a while, there are a few great new features that have been added in the last year or so.

• STRICT tables
• PRAGMA table_list
• RIGHT and FULL OUTER JOIN
• Built-in JSON support

More about each of these below.

STRICT Tables

One of SQLite’s most unusual capabilities is that it does not care about what data goes into a column. Although you could specify a type for a column, it was really more of a suggestion as other types of data could be put into the column. This behavior is different than most other relational databases and can sometimes be a source of confusion.

Now you can create your tables using the STRICT keyword to force them to require column types and to force the column types to always be checked when putting data into them.

You are still limited to INT, INTEGER, REAL, TEXT and BLOB. That means there is still no DATE or DATETIME type like you might find in other databases. Instead use TEXT with YYYY-MM-DD format.

However, an ANY type was added which essentially allows you to clearly state that the column can contain anything. This allows you to have a mixture of specific types and generic types in your STRICT tables.

Note that the STRICT keyword goes at the end of the CREATE TABLE command:

CREATE TABLE Team (ID INTEGER, Name TEXT, Coach TEXT, City TEXT, PRIMARY KEY(ID)) STRICT;

The SQLite docs have more information about the new STRICT table feature.

PRAGMA table_list

Previously if you wanted to get the list of table using SQL you had to directly query the sqlite_master table.

Now there is a simple PRAGMA that can do the same thing:

PRAGMA table_list

It returns a list of tables and some other details about the table (which may change over time according to the SQLite docs).

RIGHT and FULL OUTER JOIN

Joining tables is a common task with SQL. The most common type of join is an INNER JOIN where only the rows common to both tables are included in the result. Other less common types of joins include LEFT OUTER, RIGHT OUTER and FULL OUTER (sometimes OUTER is omitted when referring to these types of joins).

SQLite has had support for LEFT OUTER joins for a long time, but support for RIGHT OUTER and FULL OUTER were missing. But now they are here, giving your more complicated queries better compatibly with the “big name” databases.

Learn more about these types of joins at W3 schools.

JSON Support

I’ve saved the big one for last: your SQL databases can now work with JSON data within columns.

Here is a sample table to work with with some JSON data that is stored in the players column:

CREATE TABLE team(id INTEGER PRIMARY KEY, Name TEXT, players TEXT);
INSERT INTO TEAM VALUES (NULL, 'Seagulls', '[ {"Name":"Bob","position":"1B"}, {"Name":"Tom","position":"2B"} ]')
INSERT INTO TEAM VALUES (NULL, 'Pigeons', '[ {"Name":"Bill","position":"1B"}, {"Name":"Tim","position":"2B"} ]')
INSERT INTO TEAM VALUES (NULL, 'Crows', '[ {"Name":"Betty","position":"1B"}, {"Name":"Tina","position":"2B"} ]')

Let’s say you want to get the first player on each team. Without SQLite JSON support you would have to pull out the JSON column data and parse it out separately. But now you can do it with this SQL like this:

SELECT players -> 0 FROM team

The above SQL says: for each row fetch the first array element from the JSON data in players.

This is how you would list all the players on all the teams:

SELECT team.Name, json_each.value -> 'Name' FROM team, json_each(team.players)

And if you want to get the actual value without the quotes, you can use the ->> operator (and also rename the result):

SELECT team.Name, json_each.value ->> 'Name' As PlayerName FROM team, json_each(team.players)

The SQLite JSON support can do much, much more which you can read about on the SQLite JSON doc page.