SQLite is a popular, lightweight database that is used in many applications and web platforms. With its simplicity and ease of use, SQLite is a great choice for basic database needs on Linux systems like Ubuntu. In this comprehensive guide, we will walk through the entire process of installing SQLite on an Ubuntu machine from scratch.
Whether you’re a developer looking to test an application locally or a power user who wants to start leveraging the power of SQL without committing to a complex database server, this tutorial has got you covered. By the end, you’ll have SQLite up and running on Ubuntu and know exactly how to interact with it. So let’s get started!
Step 1 – Installing SQLite on Ubuntu using Package Manager
Installing SQLite on Ubuntu is very easy thanks to its available packages. No need to compile from the source!
To install SQLite, just use this apt command:
sudo apt install sqlite3
As this pulls the packages from the main Ubuntu repositories, the entire process is straightforward. You may be asked to confirm to use disk space – just accept to continue.
Within a minute or two, SQLite will finish installing on your Ubuntu machine with default settings.
To confirm, you can check the version that got installed:
sqlite3 --version
Which should print out something like:
3.22.0 2018-01-22 18:45:57 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f
And that’s it! The latest SQLite version is now installed and ready to use on Ubuntu.
Step 2 – Creating Your First SQLite Database
Next up, we’ll create our first database to start using SQLite for storage.
SQLite databases are stored as simple disk files – there are no servers to run. This makes it very easy to start working.
To create a database, simply provide the filename when running sqlite3:
sqlite3 testdb.db
This will create the file if it doesn’t exist, and open the interactive SQLite shell connected to it.
From here you can run .help to see all the available commands, or exit back out with .quit
Let’s create a simple table called posts to see SQLite in action:
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
title TEXT,
content TEXT
);
SQLite automatically indexes the primary key column defined here.
Now insert some rows:
INSERT INTO posts VALUES (1, 'Post 1', 'Content for post 1');
INSERT INTO posts VALUES (2, 'Post 2', 'Content for post 2');
And query it:
SELECT * FROM posts;
Which returns:
1|Post 1|Content for post 1
2|Post 2|Content for post 2
There we go – our first SQLite database with an example table! Feel free to add more SQL statements to insert, query, join etc to get familiar with SQLite.
In the next sections we’ll look at more details like connecting other programming languages and tools.
Step 3 – Using SQLite Databases in Python
Python and SQLite work extremely well together as they share a focus on simplicity and minimalism. Here’s a quick example of interacting with a SQLite database from Python using the built-in sqlite3 module.
We will use our posts table from before in Python:
First connect:
import sqlite3
conn = sqlite3.connect('testdb.db')
Then you can start querying and getting result data into Python structures like dicts:
cur = conn.cursor()
cur.execute('SELECT * FROM posts')
print(cur.fetchone())
# (1, 'Post 1', 'Content for post 1')
print(cur.fetchone()[2])
# Content for post 2
for row in cur.execute('SELECT * FROM posts'):
print(dict(zip([ 'id', 'title', 'content'], row)))
# {
# 'id': 1,
# 'title': 'Post 1',
# 'content': 'Content for post 1'
# }
# {
# 'id': 2,
# 'title': 'Post 2',
# 'content': 'Content for post 2'
# }
This connects Python and SQLite – allowing for scripted interactions along with analyzed data.
The Python sqlite3 module makes it very straightforward with the standard DB-API integration. There are also other Python ORM options like SQLAlchemy available too.
Step 4 – Installing and Using SQLite Browser
While the SQLite command line shell works for testing, it can quickly become tedious for day-to-day data management. This is where graphical desktop tools come in handy.
The official SQLite Browser application provides a convenient interface for operating SQLite databases, with features like:
- Browsing data and schema visually
- Writing SQL queries with auto-complete prompts
- Importing from CSV files
- Exporting table data to other formats
To install SQLite Browser on Ubuntu:
sudo apt install sqlitebrowser
Now you can launch it with:
sqlitebrowser
This will give you a GUI window where you can open up existing database files, browse their tables, write SQL etc.
For example, use the File > Open DB menu to select the testdb.db file we created before.
This then shows the list of tables on the left, click on posts to browse the data. You can edit values here with the UI as well.
The Execute SQL tab lets you write and run queries on the open database:
SQLite Browser provides a more convenient interface compared to the raw command line, while still giving you access to full SQL capabilities. Consider integrating it into your workflow if you’ll be regularly working with SQLite databases.
Next, we’ll look at using SQLite for server-style access across a network.
Step 5 – Accessing SQLite from Network Clients
A key feature of SQLite’s lightweight design is that by default databases are embedded in individual application processes that interact with them directly.
But in some cases, you may want to access a SQLite database from other networked machines and processes. SQLite has options to allow this architecture too.
The main piece needed is the sqlite3 daemon process, which can take a database file and expose it over a TCP socket for client connections. This runs a standalone SQLite server for remote access.
On Ubuntu, this is available as an additional package called sqlite3-iface.
To install it:
sudo apt install sqlite3-iface
Now launch it for any database file:
sqlite3-iface testdb.db
This will output:
Ready for requests on Unix socket /tmp/sqlite3-iface-default
Ready for requests on TCP socket localhost:8080
With this running, other processes can connect to port 8080 to issue SQL queries and get results over a client/server-style connection.
For example, with the server running – we can connect remotely with the command line shell:
sqlite3 -interface tcp:localhost:8080 "SELECT * FROM posts"
Which retrieves the data over the network!
The same approach works for any other programming language able to use the SQLite client APIs like Python by specifying localhost instead of a filesystem path for the database.
So by launching sqlite3-iface, you get networked access capabilities without needing the full complexity of setting up a typical database server.
When to Use SQLite vs a Full RDBMS
SQLite is meant for simpler local data storage use cases instead of as a full enterprise database system. Specifically where transactional integrity features become critical, a server-based RDBMS like MySQL or Postgres is usually more appropriate.
Here is a quick comparison between SQLite and using a heavier database server to help decide which approach to take:
| Feature | SQLite | Full RDBMS |
|---|---|---|
| Administration and setup | None needed, very easy | Challenging to properly setup and secure |
| Concurrency and user access | Designed for single process | Robust multi-user capabilities |
| Transactions | Only Serializable isolation | Full ACID compliance |
| Analytics performance | Good for lighter workloads | Advanced query planner and optimizations |
| Data integrity | Some basic checks | Advanced consistency features |
| High availability | No built-in redundancy | Replication, failover support |
| Storage capacity and objects | Local file system limits | Massive scalability |
SQLite works great for getting started with SQL capabilities quickly within an application, basic personal data tasks, or prototyping ideas where you don’t need ACID guarantees. But for business-critical data infrastructures, a full RDBMS like Postgres is more reliable under concurrent load.
Also read: The SQLite Handbook: A Start-to-Finish Resource for Learning and Using SQLite
Evaluate these factors against what your specific project needs to choose the right direction upfront.
Summary
Hopefully these tips have provided enough to get going with SQLite on Ubuntu for your projects. The serverless database design allows for quick prototyping, frictionless testing setups, and lean local data requirements – so consider the use cases for SQLite versus a full-fledged RDBMS.
But for many lighter database use cases, SQLite delivers exactly what you need to start integrating persistent data components today. Let me know in the comments if you have any other questions!