How To Install and Use SQLite on Ubuntu

SQLite is a free, serverless, cross-platform SQL database engine. For developers who need a lightweight, zero-configuration way to work with SQL, SQLite is the right starting point: you install the CLI, create a file, and run queries without setting up a database server.

In this tutorial you will install SQLite on Ubuntu, create a database, define tables, insert and query data, update and delete rows, join tables, and back up your database. You will also see when to use SQLite versus server-based systems like MySQL or PostgreSQL, and how to use common SQLite dot-commands.

• SQLite is a serverless, file-based SQL database that requires no separate server process or configuration.
• You install the sqlite3 package on Ubuntu with apt; the same command works across Ubuntu LTS releases.
• You create a database by running sqlite3 filename.db; this command opens the database file in create mode and will create the .db file immediately, even before you run any SQL statements or dot-commands.
• Tables are defined with CREATE TABLE; use NOT NULL on columns that must always have a value so the database enforces data integrity.
• You read data with SELECT, change it with UPDATE and DELETE, and combine tables with INNER JOIN (or other joins) when rows in both tables must match.
• You can back up a database using .backup, .dump, or by copying the .db file while no other process has it open for writing.
• SQLite fits embedded apps, local and edge tooling, development and testing, and single-user desktop or mobile apps; it is not ideal for high-concurrency write workloads.

• A machine or server running Ubuntu 20.04 or later (Ubuntu 22.04 or 24.04 recommended for new deployments). For a cloud server with a non-root sudo user and firewall, you can create a DigitalOcean Droplet with Ubuntu and follow the Initial Server Setup Guide.

Installing the SQLite command-line tool on Ubuntu uses the standard package manager. Updating the package list first ensures you get the current sqlite3 package from the Ubuntu repositories.

Update the package list:

sudo apt update

Install the SQLite CLI. This command is valid across Ubuntu LTS releases (20.04, 22.04, 24.04, and later):

sudo apt install sqlite3

Verify the installation by checking the version:

sqlite3 --version

With SQLite installed, you can create a new database.

Creating a database in SQLite means opening or creating a single file. Running sqlite3 with a filename creates that file if it does not exist, or opens it if it does. You will use a database named sharks.db for the rest of the tutorial.

From your terminal, run:

sqlite3 sharks.db

If sharks.db does not exist, SQLite creates it when you first run a statement that touches the database. If it already exists, SQLite opens a connection to it.

You should see something like:

SQLite version 3.x.x
Enter ".help" for usage hints.

Your prompt will show the sqlite> prefix:

You will create a table and add data next.

To store data in SQLite, you define a table with CREATE TABLE, specifying each column’s name, type, and constraints. The type tells SQLite what kind of data to expect; the constraints enforce what is required. Using NOT NULL on a column means every row must supply a value for it: the database rejects any INSERT that omits that column.

This tutorial uses a convention of uppercase SQL keywords and lowercase names. Every SQL statement must end with a semicolon.

Create a table with columns for ID, name, shark type, and average length in centimeters:

CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

After a successful CREATE TABLE, the prompt returns with no message. Insert three rows:

INSERT INTO sharks VALUES (1, 'Sammy', 'Greenland Shark', 427);
INSERT INTO sharks VALUES (2, 'Alyoshka', 'Great White Shark', 600);
INSERT INTO sharks VALUES (3, 'Himari', 'Megalodon', 1800);

Because every column is NOT NULL, you must supply a value for each. Omitting one causes an error. For example:

INSERT INTO sharks VALUES (4, 'Faiza', 'Hammerhead Shark');

Expected output:

Error: table sharks has 4 columns but 3 values were supplied

You have created a table and inserted data. Next you will read from it.

Reading data is done with SELECT. Using * returns all columns; WHERE restricts rows. You will list all sharks and then the row where id is 1.

List all rows in sharks:

SELECT * FROM sharks;

Expected output:

1|Sammy|Greenland Shark|427
2|Alyoshka|Great White Shark|600
3|Himari|Megalodon|1800

Return only the row whose id is 1. Using WHERE id = 1 limits the result to that row:

SELECT * FROM sharks WHERE id = 1;

Expected output:

1|Sammy|Greenland Shark|427

To return specific columns instead of all columns, list them by name after SELECT. This is more efficient than SELECT * when you only need part of a row:

SELECT name, length FROM sharks;

Expected output:

Sammy|427
Alyoshka|600
Himari|1800

Next you will change the table structure and update values.

You can add columns with ALTER TABLE and change values with UPDATE. Adding an age column and then setting it per row keeps the schema and data in sync.

Add a column for age in years:

ALTER TABLE sharks ADD COLUMN age integer;

Set age for each shark by matching on id. Using WHERE id = n ensures only that row is updated:

UPDATE sharks SET age = 272 WHERE id=1;
UPDATE sharks SET age = 70 WHERE id=2;
UPDATE sharks SET age = 40 WHERE id=3;

Verify with:

SELECT * FROM sharks;

Expected output:

1|Sammy|Greenland Shark|427|272
2|Alyoshka|Great White Shark|600|70
3|Himari|Megalodon|1800|40

Next you will delete rows based on a condition.

DELETE FROM removes rows that match a WHERE clause. You will delete all sharks whose age is 200 or less, then confirm with SELECT.

Run:

DELETE FROM sharks WHERE age <= 200;

That removes Alyoshka and Himari. Confirm with:

SELECT * FROM sharks;

Expected output:

1|Sammy|Greenland Shark|427|272

Only Sammy remains. Next you will add a second table and join it with INNER JOIN.

When related data lives in two tables (for example, sharks and their conservation status), you combine them with a join. INNER JOIN returns only rows where both tables have a matching key — in this case, only sharks that have a corresponding entry in the endangered table. If you wanted all sharks regardless of whether they have a status, you would use LEFT JOIN, which returns every row from the left table and NULL for columns from the right table where no match exists.

Create an endangered table that references sharks by id and holds a status:

CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
INSERT INTO endangered VALUES (1, 'near threatened');

Join the two tables on sharks.id = endangered.id:

SELECT * FROM sharks INNER JOIN endangered ON sharks.id = endangered.id;

Expected output:

1|Sammy|Greenland Shark|427|272|1|near threatened

To avoid duplicate id columns in the result, list the columns you want:

SELECT sharks.id, sharks.name, sharks.sharktype, sharks.length, sharks.age, endangered.status FROM sharks INNER JOIN endangered ON sharks.id = endangered.id;

Expected output:

1|Sammy|Greenland Shark|427|272|near threatened

To see the difference, try the same join with LEFT JOIN. Because only Sammy has an entry in endangered, the other sharks would show NULL for status if they existed:

SELECT sharks.id, sharks.name, endangered.status FROM sharks LEFT JOIN endangered ON sharks.id = endangered.id;

Expected output:

1|Sammy|near threatened

Since you deleted Alyoshka and Himari in Step 6, only Sammy appears. In a table with all three sharks, LEFT JOIN would return all three rows, with NULL in the status column for any shark not in endangered.

Next you will back up and export the database.

You can back up a SQLite database in three main ways: the .backup dot-command (recommended for a full copy), .dump (SQL text you can replay or archive), or copying the .db file when nothing is writing to it. This step uses the same sharks.db you have been editing.

First exit the SQLite prompt so you can run backup commands from the shell. Type:

.exit

Option 1: .backup (full binary copy)

From the shell, run SQLite and attach the current database, then run .backup to write a copy to a new file. This preserves the database format and is the preferred way to clone a database:

sqlite3 sharks.db ".backup sharks_backup.db"

List the file to confirm:

ls -la sharks_backup.db

Expected output (size and date will vary):

-rw-r--r-- 1 user group 12288 Mar 10 12:00 sharks_backup.db

Option 2: .dump (SQL export)

Dumping produces a text file of SQL statements that recreate the schema and data. Use it for version control, migration, or importing into another SQL database:

sqlite3 sharks.db ".dump" > sharks_dump.sql

Inspect the start of the dump:

head -20 sharks_dump.sql

Expected output (excerpt):

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL, age integer NOT NULL);
INSERT INTO sharks VALUES(1,'Sammy','Greenland Shark',427,272);
CREATE TABLE endangered (id integer NOT NULL, status text NOT NULL);
INSERT INTO endangered VALUES(1,'near threatened');
COMMIT;

Option 3: File copy

You can copy the .db file with cp when no other process has the database open for writing. If an application or another sqlite3 session is writing, copy during a quiet period or use .backup from a read-only connection to avoid corruption.

cp sharks.db sharks_copy.db

You now have three ways to back up or export your database. The next section explains when to choose SQLite over server-based databases.

SQLite is a single-file, serverless SQL engine. It fits cases where you want embedded or local data, minimal setup, or a single writer. It is not a drop-in replacement for a shared, multi-writer server database.

Good fits for SQLite

• Embedded and edge applications: Devices, IoT, or apps that ship with a database in a single file and no separate server.
• Local and edge tooling: CLI tools, scripts, and desktop apps that need a small, portable store.
• Development and testing: Quick local databases, fixtures, and tests without installing or running MySQL or PostgreSQL.
• Mobile and single-user desktop apps: One database file per app or user, with the engine running in-process.
• Read-heavy, low-concurrency workloads: Caching, config, or small catalogs where one writer is enough.

When to use a server database instead

• Multiple writers: MySQL and PostgreSQL handle many concurrent connections and writes; SQLite normally allows one writer at a time (with brief lock windows).
• Remote access and centralization: When many clients must connect to one shared database over the network, use a server RDBMS.
• Very large datasets or high availability: Server databases offer tuning, replication, and HA; SQLite is a single file and has no built-in replication.

Comparison: SQLite vs MySQL vs PostgreSQL

For a deeper comparison, see SQLite vs MySQL vs PostgreSQL: A Comparison of Relational Database Management Systems.

Dot-commands are SQLite CLI helpers. They start with a . and do not end with a semicolon. Run them at the sqlite> prompt.

Example: turn on headers and column mode, then run a query:

.headers on
.mode column
SELECT * FROM sharks;

Expected output:

id  name   sharktype        length  age
--  -----  ---------------  ------  ---
1   Sammy  Greenland Shark  427     272

What is SQLite used for?

SQLite is used as an embedded or file-based SQL database. Typical uses include local and edge applications, development and testing databases, mobile and desktop apps, CLI tools, and read-heavy caches or config stores where a single file and no server are desirable.

How do I install SQLite on Ubuntu?

Run sudo apt update, then sudo apt install sqlite3. Verify with sqlite3 --version. The same steps work across Ubuntu LTS releases.

Does SQLite require a server?

No. SQLite is serverless. The engine runs inside your process or the sqlite3 CLI; the database is a single file on disk. There is no separate database server to install or configure.

Where are SQLite databases stored?

A SQLite database is stored wherever you create it. When you run sqlite3 path/to/file.db, that path is the storage location. There is no default data directory; you choose the path and filename.

Is SQLite suitable for production use?

Yes, for the right workload. SQLite is widely used in production for embedded systems, edge devices, single-user or low-concurrency apps, and read-heavy use cases. It is not suitable for high-concurrency, multi-writer server workloads; for those, use MySQL, PostgreSQL, or another server RDBMS.

How do I create a database in SQLite?

Run sqlite3 filename.db. If the file does not exist, SQLite creates it when you run at least one SQL statement or dot-command. Then use CREATE TABLE and INSERT to define and populate tables.

What is the difference between SQLite and MySQL?

SQLite is serverless and file-based: one file per database, no separate server. MySQL is a client/server RDBMS: you run a server process and clients connect over the network. SQLite fits embedded and single-writer use; MySQL fits multi-user, multi-writer web and app back ends. See SQLite vs MySQL vs PostgreSQL for a full comparison.

You have installed SQLite on Ubuntu, created a database and tables, inserted and queried data, updated and deleted rows, joined two tables with INNER JOIN, and backed up the database with .backup, .dump, and file copy. You also saw when SQLite is a good fit and how it compares to MySQL and PostgreSQL.