SQLite Tutorial: Installation, Usage, and Best Practices

SQLite is an open-source, lightweight, fast, standalone, and highly reliable SQL database engine written in C. It provides a fully functional database solution. SQLite runs on almost all smartphones and computers, and it's embedded in countless applications that people use every day.

In addition, SQLite is known for its stable file format, cross-platform capabilities, and backward compatibility. The developers of SQLite have committed to maintaining the file format at least until 2050.

This article will introduce the basics of SQLite and how to use it.

Installing SQLite

Download the appropriate package for your target system from the SQLite official page.

After downloading and extracting the package, you will get a sqlite3 command-line tool, whether on Windows, Linux, or macOS.

Here is an example of the command-line tool extracted on macOS:

➜  sqlite-tools-osx-x64-3450100 ls -l
total 14952
-rwxr-xr-x@ 1 darcy  staff  1907136  1 31 00:27 sqldiff
-rwxr-xr-x@ 1 darcy  staff  2263792  1 31 00:25 sqlite3
-rwxr-xr-x@ 1 darcy  staff  3478872  1 31 00:27 sqlite3_analyzer

Use Cases for SQLite

SQLite differs from client-server SQL database engines (such as MySQL, Oracle, PostgreSQL, or SQL Server) in that they solve different problems.

Server-side SQL database engines are designed for enterprise-level data sharing and emphasize scalability, concurrency, centralization, and control. In contrast, SQLite is often used for local data storage for individual applications and devices, emphasizing cost-effectiveness, efficiency, reliability, independence, and simplicity.

Use Cases for SQLite:

1. Embedded Devices and IoT

SQLite does not require additional management or service startup, making it ideal for smartphones, TVs, set-top boxes, game consoles, cameras, watches, and other smart devices.

1. Websites

Most low-traffic websites can use SQLite as their database. According to the official SQLite website, websites with fewer than 100,000 visits per day can run SQLite smoothly. The SQLite website itself uses SQLite as its database engine and handles about 500,000 HTTP requests daily, with 15-20% involving database queries.

1. Data Analysis

The SQLite3 command-line tool can easily interact with CSV and Excel files, making it suitable for analyzing large datasets. Additionally, many programming languages (like Python) have built-in SQLite support, allowing for easy script-based data manipulation.

1. Caching

SQLite can be used as a cache for application services, reducing the load on centralized databases.

1. In-Memory or Temporary Databases

Thanks to SQLite's simplicity and speed, it is often used for program demonstrations or daily testing.

When SQLite Is Not Suitable:

1. Accessing the Database Over the Network. SQLite is a local file database and does not provide remote access functionality.
2. When High Availability and Scalability Are Required. SQLite is simple to use but not scalable.
3. When Dealing with Huge Data Volumes. Although the SQLite database size limit is up to 281 TB, all data must reside on a single disk.
4. High Concurrency for Write Operations. SQLite only allows one write operation at a time, with others queued up.

Using SQLite3 Commands

SQLite provides the sqlite3 (or sqlite3.exe on Windows) command-line tool to perform SQLite database operations and execute SQL statements.

Run ./sqlite3 in the command prompt to start the sqlite3 program, then type .help to view the help guide or type .help <keyword> for specific help.

Here’s a list of some commands:

sqlite> .help
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode. Default: auto
.help ?-all? ?PATTERN?   Show help text for PATTERN
.hex-rekey OLD NEW NEW   Change the encryption key using hexadecimal
.indexes ?TABLE?         Show names of indexes
.mode MODE ?OPTIONS?     Set output mode
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.quit                    Exit this program
.read FILE               Read input from FILE or command output
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.show                    Show the current values for various settings
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.......

In SQLite3, SQL statements must end with a semicolon (;) to execute. Special commands like .help and .tables do not need a semicolon.

Creating a New SQLite Database

Execute sqlite3 filename to open or create an SQLite database. If the file doesn’t exist, SQLite will create it automatically.

Example: Open or create a database named my_sqlite.db.

$ sqlite3 my_sqlite.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

You can also create an empty file first and then use the sqlite3 command to open it, followed by creating a table with the CREATE TABLE command. Use .tables to view existing tables, and .exit to exit the sqlite3 tool.

$ touch test.db
$ sqlite3 test.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite> create table user(name text,age int);
sqlite> .tables
user
sqlite>

Viewing the Current Database

Use the .databases command to view the currently open databases.

sqlite> .databases
main: /Users/darcy/develop/sqlite-tools-osx-x86-3420000/my_sqlite.db r/w
sqlite>

Performing CRUD Operations

SQLite is nearly fully compatible with common SQL syntax, allowing you to directly write and execute standard SQL statements.

Create a table:

sqlite> create table user(name text,age int);
sqlite>

Insert data:

sqlite> insert into user values('aLang',20);
sqlite> insert into user values('Darcy',30);
sqlite> insert into user values('Jess',40);

Query data:

sqlite> select * from user;
aLang|20
Darcy|30
Jess|40

Add an index, creating an index named user_name on the name column of the user table:

sqlite> create index user_name on user(name);

Changing Output Format

By default, SQLite uses | to separate columns in queries, which may not be convenient for reading. The sqlite3 tool supports various output formats, with list being the default.

Available formats: ascii, box, csv, column, html, insert, json, line, list, markdown, quote, table.

You can change the output format using the .mode command.

Box format:

sqlite> .mode box
sqlite> select * from user;
┌──────────┬─────┐
│   name   │ age │
├──────────┼─────┤
│ aLang    │ 20  │
│ Darcy    │ 30  │
│ Jess│ 40  │
└──────────┴─────┘

JSON format:

sqlite> .mode json
sqlite> select * from user;
[{"name":"aLang","age":20},
{"name":"Darcy","age":30},
{"name":"Jess","age":40}]

Column format:

sqlite> .mode column
sqlite> select * from user;
name      age
--------  ---
aLang     20
Darcy     30
Jess40

Table format:

sqlite> .mode table
sqlite> select * from user;
+----------+-----+
|   name   | age |
+----------+-----+
| aLang    | 20  |
| Darcy    | 30  |
| Jess| 40  |
+----------+-----+
sqlite>

Querying Schema

The sqlite3 tool provides several convenient commands to view the schema of a database. These commands serve as shortcuts for querying.

For example, the .table command shows all tables in the database:

sqlite> .table
user

The .table command is equivalent to the following SQL query:

sqlite> SELECT name FROM sqlite_schema
   ...> WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%';
   ...> ;
user

sqlite_master is a special table in SQLite that contains schema information about the database. You can query this table to get the creation statements for tables and indexes.

sqlite> .mode table
sqlite> select * from sqlite_schema;
+-------+-----------+----------+----------+--------------------------------------+
| type  |   name    | tbl_name | rootpage |                 sql                  |
+-------+-----------+----------+----------+--------------------------------------+
| table | user      | user     | 2        | CREATE TABLE user(name text,age int) |
| index | user_name | user     | 3        | CREATE INDEX user_name on user(name) |
+-------+-----------+----------+----------+--------------------------------------+

Use .indexes to view indexes, and .schema to view detailed schema information:

sqlite> .indexes
user_name

sqlite> .schema
CREATE TABLE user(name text, age int);
CREATE INDEX user_name on user(name);

Output Results to a File

You can use the .output filename command to write query results to a specified file.

For example, you can change the output format to JSON using .mode json, then write the results to sql_result.json:

sqlite> .output sql_result.json
sqlite> .mode json
sqlite> select * from user;
sqlite> .exit

$ cat sql_result.json
[{"name":"aLang","age":20},
{"name":"Darcy","age":30},
{"name":"Jess","age":40}]

Output to EXCEL

Using the .excel command will output the next query results to an Excel file.

sqlite> .excel
sqlite> select * from sqlite_schema;

Reading and Running SQL Scripts

The .read command allows you to read and execute SQL statements from a file, which is useful for running bulk SQL scripts.

Creating an SQL file:

$ echo "select * from user" > sql_query.sql
$ cat sql_query.sql
select * from user

Running the SQL script:

$ ./sqlite3 my_sqlite.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.

sqlite> .mode table
sqlite> .read sql_query.sql
+----------+-----+
|   name   | age |
+----------+-----+
| aLang    | 20  |
| Darcy    | 30  |
| Jess| 40  |
+----------+-----+
sqlite>

SQLite Backup and Restore

Backup and restore are crucial steps to prevent data loss and ensure data persistence. SQLite offers simple methods for backing up and restoring databases.

To back up a database, you can export the entire database to a SQL script using the .dump command:

$ ./sqlite3 my_sqlite.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .output backup.sql
sqlite> .dump
sqlite> .exit

$ cat backup.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE user(name text,age int);
INSERT INTO user VALUES('aLang',20);
INSERT INTO user VALUES('Darcy',30);
INSERT INTO user VALUES('Jess',40);
CREATE INDEX user_name on user(name);
COMMIT;

This will export the my_sqlite.db database to a backup.sql file. This SQL file contains all the SQL statements necessary to recreate the database. To restore the database, simply run this script in sqlite3.

Example: Restoring data into a new database (my_sqlite_2.db):

$ ./sqlite3 my_sqlite_2.db
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .read backup.sql
sqlite> select * from user;
aLang|20
Darcy|30
Jess|40

This will execute all SQL statements in the backup.sql file, rebuilding the database. With these backup and restore methods, you can ensure your SQLite database is securely protected and can be quickly restored when needed.

SQLite Visualization Tools

While command-line operations are efficient, they may not always be intuitive. If you prefer a graphical interface for managing SQLite databases, you can use Chat2DB, an AI-powered database management tool that supports SQLite. Chat2DB allows you to run queries, visualize data, and manage your database with a user-friendly interface.

Download Chat2DB: https://chat2db.ai

Alternatively, you can also use SQLite Database Browser for graphical database management.

Download page: https://sqlitebrowser.org/dl/

Appendix

Here is a list of commonly used SQLite functions. The function names are self-explanatory, so no further explanation is needed:

Function 1	Function 2	Function 3	Function 4
abs(X)	changes()	char(X1,X2,...,XN)	coalesce(X,Y,...)
concat(X,...)	concat_ws(SEP,X,...)	format(FORMAT,...)	glob(X,Y)
hex(X)	ifnull(X,Y)	iif(X,Y,Z)	instr(X,Y)
last_insert_rowid()	length(X)	like(X,Y)	like(X,Y,Z)
likelihood(X,Y)	likely(X)	load_extension(X)	load_extension(X,Y)
lower(X)	ltrim(X)	ltrim(X,Y)	max(X,Y,...)
min(X,Y,...)	nullif(X,Y)	octet_length(X)	printf(FORMAT,...)
quote(X)	random()	randomblob(N)	replace(X,Y,Z)
round(X)	round(X,Y)	rtrim(X)	rtrim(X,Y)
sign(X)	soundex(X)	sqlite_compileoption_get(N)	sqlite_compileoption_used(X)
sqlite_offset(X)	sqlite_source_id()	sqlite_version()	substr(X,Y)
substr(X,Y,Z)	substring(X,Y)	substring(X,Y,Z)	total_changes()
trim(X)	trim(X,Y)

---

Community

Go to Chat2DB website
🙋 Join the Chat2DB Community
🐦 Follow us on X
📝 Find us on Discord