SQLite/en

SQLite is a database management tool used to build commonly called pocket databases because they offer all the features of relational databases without the client-server architecture and with the added advantage of the data residing on a single disk file which can simply be copied to another computer. Software written in a wide variety of common languages can read from and write to the database file with standard SQL queries using the language's API for database interactions.

Filesystem Best Practices and Concurrency

Like any other database, an SQLite database should not be used on a shared filesystem such as home, scratch, and project. Typically, you should copy your SQLite file to the local scratch $SLURM_TMPDIR space at the beginning of a job, and you can then use the database without any issues while also enjoying the best possible performance. Note that SQLite is not intended for use with multiple threads or processes writing concurrently to the database; for this, you should consider a client-server solution.

Using SQLite Directly

You can access an SQLite database directly using the native client:

sqlite3 foo.sqlite

If the file foo.sqlite does not already exist, SQLite will create it and the client will start in an empty database; otherwise, you will be connected to the existing database. You may then execute whichever queries you wish on the database, such as SELECT * FROM tablename; to print to the screen the entire contents of the table tablename.

Accessing SQLite from Software

The most common way to interact with an SQLite (or other) database is through function calls to open a connection to the database; execute queries that can read, insert, or update existing data; and close the connection to the SQLite database so that any changes are flushed to the SQLite file. In the simple example below, we suppose that the database has already been created with a table called employee that has two columns: the string name and the integer age.

Python Example

#!/usr/bin/env python3

# For Python we can use the module sqlite3, installed in a virtual environment, 
# to access an SQLite database
import sqlite3

age = 34

# Connect to the database...
dbase = sqlite3.connect("foo.sqlite")

dbase.execute("INSERT INTO employee(name,age) VALUES(\"John Smith\"," + str(age) + ");")

# Close the database connection
dbase.close()

R Example

# Using R, the first step is to install the RSQLite package in your R environment, 
# after which you can use code like the following to interact with the SQLite database
library(DBI)

age <- 34

# Connect to the database...
dbase <- dbConnect(RSQLite::SQLite(),"foo.sqlite")

# A parameterized query
query <- paste(c("INSERT INTO employee(name,age) VALUES(\"John Smith\",",toString(age),");"),collapse='')
dbExecute(dbase,query)

# Close the database connection
dbDisconnect(dbase)

C++ Example

#include <iostream>
#include <string>
#include <sqlite3.h>

int main(int argc,char** argv)
{
  int age = 34;
  std::string query;
  sqlite3* dbase;

  sqlite3_open("foo.sqlite",&dbase);

  query = "INSERT INTO employee(name,age) VALUES(\"John Smith\"," + std::to_string(age) + ");";
  sqlite3_exec(dbase,query.c_str(),nullptr,nullptr,nullptr);

  sqlite3_close(dbase);

  return 0;
}

Limitations

When to Consider a Client-Server Database

As its name suggests, SQLite is easy to use and intended for relatively simple databases which are neither excessively large (hundreds of gigabytes or more) nor too complicated in terms of their entity-relationship diagram. As your SQLite database grows in size and complexity, the performance could start to degrade, in which case the time may have come to consider the use of more sophisticated database software which uses a client-server model. The SQLite website includes an excellent page on Appropriate Uses For SQLite, including a checklist for choosing between SQLite and client-server databases.