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 sqlite. 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 sqlite command to create a database: sqlite 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 sqlite by pressing Ctrl+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: sqlite my_first.db

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