70.801
The SQLite Database

Introduction

In this lesson, you will learn how to install the local SQLite management console, inspect SQLite databases using an online tool, and finally how to create and access a SQLite database from R and Java.

About SQLite

SQLite is a server-less, file-based relational database (although it might not be a database management system). It is purposefully lightweight in terms of installation, administration, and required computational resources. It runs on embedded computers, mobile systems (iOS and Android, to name two), and on all modern operating systems including Linux, MacOS, and Windows.

SQLite has the following key features:

• self-contained
• serverless
• zero-configuration
• transactional

Serverless means that it does not run in a separate process, rather all database processing (the “engine”) is compiled into the application. This is unlike server-based database management systems such as MySQL, Oracle, SQL Server, PostgreSQL, etc., which require a separate server process to run and be connected to by the application via a TCP/IP socket.

SQLite is among the most commonly used and most popular databases as it is often embedded within iOS and Android applications. It is ideal for learning and small database applications that do not require concurrent multi-user access to the database.

Client/Server Databases

In server-based databases, applications that need to access the database connect first via a TCP/IP port and then use that port to send database requests in the form of SQL statements and receive results in the form of tables. This type of architecture is generally called a two-tier client/server architecture.

The diagram below illustrates a typical two-teir client/server database architecture:

Client/Server Database Architecture

The client running a program containing embedded SQL or running an ad hoc query tool sends a SQL query (SELECT) to the database management system (typically running on a remote server). The DBMS interprets the query, formulates a query plan, and then accesses the stored data. The result of the query is returned to the client program as a result set in the form of a table. The client program then processes the data further, stores it locally, visualizes the data, or generates a report containing the data. Updates to the database are done similarly.

The key is that the database “engine” and all database processing occurs in a separate process that is most likely on a shared server computer. This makes concurrent access to the data from multiple applications simple. The database becomes a shared resource.

Architecture of SQLite

The architecture of SQLite is decidedly different. The database is all contained within a single file located on the client application’s storage system. The database is single-user and not shared. Processing of SQL statements all occur within the application’s SQLite library that is “compiled into” the application. There is no separate server. In fact, one does not even have to install SQLite as a separate program to access a SQLite database from a programming language such as R, Python, Java, C++, Racket, or others. In this regard, SQLite is very similar to Microsoft Access, another common server-less database.

The diagram below illustrates the file-based nature of SQLite.

Serverless Architecture of SQLite

The key is that all SQL processing actually happens within the process and memory space of the application that connected to SQLite. There is no separate server process. Of course, this makes shared access quite difficult – it is possible but requires file-locking which makes concurrent access very slow.

Managing SQLite Databases

SQLite databases can be managed in several ways:

1. sqlite3: The sqlite3 utility allows access to database files and administration using the command line. Download the utilities from http://sqlite.org.

2. DB Browser: A graphical user interface to managing a SQLite database, including table creation, ad hoc queries, schema management, and data inserts, updates, and deletes. Download the program from https://sqlitebrowser.org.

3. Programs: Using database management functions from programs, e.g., the functions from the RSQLite package in R, including dbListTables().

Connect to SQLite from R

This is not a complete tutorial on how to create and work with SQLite databases from R, rather this section seeks to show how to connect to a SQLite database from R.

To connect to or access a SQLite database from R requires two steps:

1. load the RSQLite package¹
2. call the function dbConnect()

library(RSQLite)
dbcon <- dbConnect(RSQLite::SQLite(), "myDB.db")

If the database file does not already exist, a new database is created.

Once the connection to the database has been made, you can access the database from R using functions from the RSQLite package, such as dbExecute(), dbGetQuery(), and dbSendStatement(). Other tutorials explain how to work with SQLite databases from R in more detail.

create table lessons (
  lname text not null,
  llength integer not null
);

Let’s add a few rows of data to the table so we can see how SQLite assigns a row id.

insert into lessons values
  ('Intro C++', 90),
  ('Intro Java', 90),
  ('SQL Joins', 60)

Now, finally, let’s run a query that returns data.

select * from lessons;

Table 1: 3 records

lname	llength
Intro C++	90
Intro Java	90
SQL Joins	60

So, now you have seen how to create, connect to, and work with a SQLite database in R.

Conclusion

SQLite is an important and popular relational database, albeit one that is file rather than server-based. However, it seamlessly integrates with R, Python, and many other programming language and supports standard SQL.

---

Files & Resources

All Files for Lesson 70.801

---

References

• SQLite Tutorial

Errata

Let us know.

---

1. Naturally, you must first install the RSQLite package. Check Lesson 6.101 First Steps in R for more information on how to work with packages.↩︎