Skip to content

SQLite

Let's now look at a very simple, yet powerful (and very popular/commonly used), database engine.

The SQLite database engine is entirely based on the idea of storing your data inside of a single, flat file. Other more complicated, network-connected engines like MySQL and Server SQL store their data in files as well, but in a manner that supports multiple connections over a network, scaling, replication, and more.

SQLite keeps it simple and is therefore suitable for smaller use cases. In our case, we're going to use it to learn SQL.

Use Cases

An SQLite database is a single file, so it's only suitable for certain workloads. Examples of those workloads include:

  • Mobile applications
  • Integrated applications inside of appliances
  • Non-critical systems
  • Websites with a simple data storage need
  • Caching
  • Internet of Things (IoT)
  • Data transfers

And more.

The "mobile applications" use case is interesting, because it turns out SQLite is used a lot by many mobile apps on both iOS and Android. Unlike MySQL and other bigger database engines, SQLite is just a code library - there's no server running to receive network requests. Instead, SQLite is a library you use to interact with the database file on disk.

SQLite's design makes it ideal for simple, small, scoped projects and other simple use cases.

Installation

We're going to install SQLite on our Ubuntu Server so that we can learn SQL. This is super easy to do: sudo apt install sqlite3. This will download and install SQLite3.

If the package cannot be found, make sure you've done an sudo apt update first. If you experience any other issues, please visit the Discord community and share the issues you're having.

Basic Usage

Now let's use the sqlite3 command to create a database: sqlite3 my_first.db

1
2
3
4
$ sqlite my_first.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>

Congratulations, you just (maybe?) created your first database using SQLite. Exit sqlite3 by pressing ++control+d++ and use ls -l to view your new, shiny database:

1
2
3
$ ls -l
total 0
-rw-r--r-- 1 ubuntu ubuntu 0 Apr  5 01:07 my_first.db

It's not overly impressive, but it is a database. Let's load it back up again: sqlite3 my_first.db

1
2
3
4
$ sqlite my_first.db
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>

Now let's see what tables we have:

1
2
sqlite> .tables
sqlite>

Of course the response is empty because we have no tables. Let's create one (don't worry about the SQL for now):

1
2
3
4
5
sqlite> CREATE TABLE hello_world(message string);
sqlite> .tables
hello_world
sqlite> .schema hello_world
CREATE TABLE hello_world(message string);

That's a bit better. If we use .schema then we can see how the table is constructed.

Now it's time to learn SQL so you can take this database for a spin!