Using SQLite in Matlab

MathWorks invests a huge amount of effort in recent years on supporting large distributed databases. The business case for this focus is entirely understandable, but many Matlab users have much simpler needs, which are often served by the light-weight open-source SQLite database (which claims to be the most widely-used database worldwide). Although SQLite is very widely used, and despite the fact that built-in support for SQLite is included in Matlab (for its internal use), MathWorks has chosen not to expose any functionality or wrapper function that would enable end-users to access it. In any case, I recently came across a need to do just that, when a consulting client asked me to create an interactive data-browser for their SQLite database that would integrate with their Matlab program:

sqlite4java

sqlite4java is a Java package by ALM Works that is bundled with Matlab for the past several years (in the %matlabroot%/java/jarext/sqlite4java/ folder). This is a lightweight open-source package that provides a minimalist and fast (although not very convenient) interface to SQLite. You can either use the package that comes with your Matlab installation, or download and use the latest version from the project repository, where you can also find documentation.
Mark Mikofski exposed this hidden nugget back in 2015, and you are welcome to view his post for additional details. Here’s a sample usage:

% Open the DB data file
db = com.almworks.sqlite4java.SQLiteConnection(java.io.File('C:\Yair\Data\IGdb 2017-11-13.sqlite'));
db.open;
% Prepare an SQL query statement
stmt = db.prepare(['select * from data_table where ' conditionStr]);
% Step through the result set rows
row = 1;
while stmt.step
   numericValues(row) = stmt.columnInt(0);    % column #0
   stringValues{row}  = stmt.columnString(1); % column #1
end
% Cleanup
stmt.dispose
db.dispose

Note that since sqlite4java uses a proprietary interface (similar, but not identical, to JDBC), it can take a bit of time to get used to it. I am generally a big fan of preferring built-in components over externally-installed ones, but in this particular case I prefer other alternatives.

JDBC

JDBC (Java Database Connectivity) is the industry standard for connectivity to databases. Practically all databases nowadays have at least one JDBC connector, and many DBs have multiple JDBC drivers created by different groups. As long as they all adhere to the JDBC interface standard, these drivers are all equivalent and you can choose between them based on availability, cost, support, license, performance and other similar factors. SQLite is no exception to this rule, and has several JDBC driver implementations, including xerial’s sqlite-jdbc (also discussed by Mark Mikofski) and sqlitejdbc. If you ask me,
sqlite-jdbc is better as it is being maintained with new versions released periodically.
The example above would look something like this with sqlite-jdbc:

% Add the downloaded JAR library file to the dynamic Java classpath
javaaddpath('C:\path\to\sqlite\sqlite-jdbc-3.21.0.jar')
% Open the DB file
jdbc = org.sqlite.JDBC;
props = java.util.Properties;
conn = jdbc.createConnection('jdbc:sqlite:C:\Yair\Data\IGdb 2017-11-13.sqlite',props);  % org.sqlite.SQLiteConnection object
% Prepare and run an SQL query statement
sqlStr = ['select * from data_table where ' conditionStr];
stmt = conn.createStatement;     % org.sqlite.jdbc4.JDBC4Statement object
rs = stmt.executeQuery(sqlStr);  % org.sqlite.jdbc4.JDBC4ResultSet object
% Step through the result set rows
rows = 1;
while rs.next
   numericValues(row) = rs.getLong('ID');
   stringValues{row}  = rs.getString('Name');
end
% Cleanup
rs.close
stmt.close
conn.close

Database toolbox

In addition to all the above, MathWorks sells the Database Toolbox which has an integral SQLite connector, in two flavors – native and JDBC (the JDBC connector is simply sqlite-jdbc that I mentioned above, see a short discussion here).
I assume that the availability of this feature in the DB toolbox is the reason why MathWorks has never created a documented wrapper function for the bundled sqlite4java. I could certainly understand this from a business perspective. Still, with so many free alternatives available as discussed in this post, I see not reason to purchase the toolbox merely for its SQLite connector. Then again, if you need to connect to several different database types, not just SQLite, then getting the toolbox might make sense.

mksqlite

My personal favorite is actually none of these Java-based connectors (surprise, surprise), but rather the open-source mksqlite connector by Martin Kortmann and Andreas Martin. This is a native (Mex-file) connector that acts as a direct Matlab function. The syntax is pretty straight-forward and supports SQL queries. IMHO, its usage is a much simpler than with any of the other alternatives:

% Open the DB file
mksqlite('open', 'C:\Yair\Data\IGdb 2017-11-13.sqlite');
% Query the database
results = mksqlite(['select * from data_table where ' conditionStr]);
numericValues = [results.ID];
stringValues  = {results.Name};
% Cleanup
mksqlite('close');

Can it be any simpler than this!?
However, the main benefit of mksqlite over the other connectors is not its simplicity but the connector’s speed. This speed is due to the fact that the query is vectorized and we do not need to loop over all the separate data rows and fields. With the other connectors, it is actually not the loop that takes so long in Matlab, but rather the overheads and inefficiencies of numerous library calls to fetch one single value at a time from the result-set – this is avoided in mksqlite where there is only a single call. This results in lightning speed: A couple of years ago I consulted to a client who used a JDBC connector to an SQLite database; by switching from a JDBC connector to mksqlite, I reduced the execution time from 7 secs to 70 msecs – a 100x speedup! In that specific case, this made the difference between an unusable program and a highly interactive/responsive one.

Other alternatives

In addition to all the above, we can also use a .NET-based connector or a Python one – I leave these as an exercise for the reader…
Have I forgotten some important alternative? Or perhaps you have have some related tip you’d like to share? If so, then please leave a comment below.
Happy New Year everybody!