SQLite is a popular open-source relational database management system (RDBMS) that is self-contained, serverless, zero-configuration and transactional. SQLite is embedded into millions of mobile apps and other software programs to store data locally.
In this comprehensive guide, we will provide an overview of SQLite and its key features, along with tutorials on how to install, set up and use SQLite for basic database operations.
What is SQLite?
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is free for use for any purpose, commercial or private. SQLite is the most widely deployed database in the world – it is used in most smartphones, web browsers, operating systems and many other applications.
Some key features of SQLite include:
- Serverless – No separate server process needed, reads and writes directly to disk files
- Zero Configuration – No setup or administration needed
- Transactional – Atomic, consistent, isolated, durable (ACID) transactions
- Self-Contained – Contains entire database in a single disk file
- Cross-Platform – Available on all major operating systems
- Small Footprint – Library size of less than 300 KB, database files in kilobytes to megabytes
- Reliable – Used in high-reliability applications like smartphones, browsers etc.
- Full-featured – Supports SQL queries, triggers, views, transactions etc.
SQLite is a great choice as an embedded database for local/client storage in software programs and mobile apps. It is not designed to be a client–server database like MySQL, Oracle etc.
SQLite Installation
SQLite only requires its library to be integrated into an application. There are no separate server processes to install or configure. This section covers ways to add SQLite library to your projects:
- Precompiled Binaries – For languages like C/C++, download the precompiled SQLite binary or source code and link it to your program.
- Package Managers – Languages like Python, Ruby, PHP etc. can install SQLite via package managers like pip, gem, composer etc.
- Native Support – Some languages like Python, JavaScript, Java, Go etc. have built-in modules to support SQLite databases.
- IDE Extensions – Installing SQLite extensions on IDEs like Visual Studio, Xcode etc. allows creating SQLite databases.
- Docker Image – SQLite Docker image can be used to integrate SQLite into your Docker projects.
Overall, integrating SQLite is straightforward for most programming languages and platforms. After adding the library, you can start building applications with SQLite databases.
Basic SQLite Commands
Once SQLite is integrated into your application, interacting with the database is done using SQL statements like any other database. Here are some of the most common SQLite commands:
.tables– List names of tables in the databaseCREATE TABLE– Create a new tableINSERT– Insert a new row into a tableSELECT– Query data from a tableUPDATE– Modify existing data in a tableDELETE– Delete rows from a tableDROP TABLE– Delete an entire table from the database
SQLite provides a .shell command to interactively run SQL statements if you are using the SQLite CLI shell.
Other important SQLite commands include:
.databases– List names of attached databases.mode– Set output mode for queries.headers– Toggle display of column headers.nullvalue– Set text to display for NULL values
These allow configuring the CLI shell to suit your requirements. Overall, the SQL language itself is identical to other databases like MySQL, PostgreSQL etc.
SQLite Data Types
SQLite supports common data types for storing data in tables:
- INTEGER – Signed integers
- REAL – Floating point values
- TEXT – Character strings
- BLOB – Binary data
- NUMERIC – Decimal numbers
- BOOLEAN – True/False values
- DATE – Date in YYYY-MM-DD format
- DATETIME – Date and time
For example:
CREATE TABLE user (
id INTEGER PRIMARY KEY,
name TEXT,
height REAL,
birthday DATE
);SQLite does not have separate Boolean storage class – BOOLEAN values are stored as integers 0 (false) and 1 (true).
SQLite does not enforce data type constraints (apart from PRIMARY KEY column). Any type of data can be inserted into any column.
Creating a New SQLite Database
To create a new SQLite database, specify the database file name after the .open command when launching the sqlite3 CLI shell:
$ sqlite3 tutorial.db
SQLite version 3.7.15.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>This will create a new database file tutorial.db in the current directory. You can verify that the file exists.
Within the CLI shell, you can then run commands like CREATE TABLE, INSERT etc. to populate and work with the database.
By default, SQLite creates data files in the current directory. You can specify a full path like /var/sqlite/myapp.db to choose where the database file is created.
Creating Tables in SQLite
Use the CREATE TABLE statement to create new tables in an SQLite database:
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
quantity INTEGER,
price REAL
);- Specify the name of each column and its data type (TEXT, INTEGER, REAL etc).
- Choose one column as the
PRIMARY KEYwhich uniquely identifies each row. - The
PRIMARY KEYcolumn must haveINTEGERtype and allowNULLvalues. - You can add additional constraints like
NOT NULL,UNIQUE,DEFAULTetc. to columns.
Example with constraints:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
country TEXT DEFAULT 'US'
);- Use the
.tablescommand to verify that the new table was created successfully. - You can create multiple tables and relations between them as required.
Inserting Data into SQLite Tables
The INSERT statement is used to populate a table with rows:
INSERT INTO products VALUES (1, 'Keyboard', 15, 19.99);
INSERT INTO products (name, quantity, price) VALUES ('Mouse', 25, 9.99);
INSERT INTO users (name, email, country)
VALUES ('John', '[email protected]', 'UK');- The first INSERT statement specifies values for all columns in order.
- Second statement lists specific columns to insert data into.
- Use multiple INSERT statements one by one to populate the table with data.
- Fetch the rows using SELECT to verify the inserted data.
Querying Data in SQLite using SELECT
The SELECT statement queries data from one or more tables:
SELECT * FROM products;
SELECT name, price FROM products;
SELECT * FROM products WHERE price > 10;
SELECT * FROM users JOIN products ON users.id = products.user_id;SELECT *queries all columns. Specify specific columns after SELECT to fetch only needed data.- The WHERE clause filters rows based on conditions.
- JOINs combine rows from multiple tables using relations.
Other clauses like ORDER BY, LIMIT, GROUP BY etc. are also supported. Explore the linked SQLite SELECT article for details.
Updating Data in SQLite Tables
Use the UPDATE statement to modify existing data in a table:
UPDATE products SET price = price * 1.1;
UPDATE users SET country = 'CA' WHERE name LIKE 'A%';- SET clause specifies the columns to update and their new values.
- The optional WHERE clause picks which rows to update based on a condition.
- UPDATE statements can modify data in single or multiple rows at a time.
- Fetch and verify the updated rows using SELECT statement.
Make sure to add a WHERE clause to UPDATE/DELETE unless you wish to modify all rows in the table.
Deleting Data in SQLite Tables
To delete data from SQLite tables, use the DELETE statement:
DELETE FROM products WHERE quantity = 0;
DELETE FROM users WHERE id IN (5, 18, 22);- DELETE removes entire rows (all columns) from the table.
- Add a WHERE clause to pick which rows to delete.
- DELETE without WHERE will empty the entire table!
Use SELECT to verify that the rows have been deleted as expected.
Dropping Tables in SQLite
If you no longer need a table, it can be permanently deleted from the database using the DROP TABLE command:
DROP TABLE products;- All data in the table will be deleted!
- Make sure to backup the actual data first to avoid losing it.
- Once dropped, the table schema and data are gone.
- You can re-create the table with CREATE TABLE.
Use DROP TABLE cautiously when no longer needed. Deleting a table cannot be undone.
Attaching External Databases in SQLite
SQLite provides the ATTACH DATABASE statement to access external database files in the same connection:
ATTACH DATABASE 'users.db' AS usersdb;
CREATE TABLE usersdb.users(id INT, name TEXT);
INSERT INTO usersdb.users VALUES (1, 'John');
SELECT * FROM usersdb.users;ATTACH DATABASEloads the external file users.db into the connection.- Tables in the attached database can be accessed using
database.tableformat. - When done, use
DETACH DATABASEto disconnect the attached database.
This allows working with multiple independent SQLite databases through one disk file handle.
Using SQLite Operators
SQLite supports standard SQL operators like arithmetic operators (+, -, *, /), comparison operators (=, !=, >, < etc.), logical operators (AND, OR, NOT) and more.
Examples:
SELECT * FROM products WHERE price > 20 AND quantity < 10;
SELECT name, price * quantity AS total FROM products;
SELECT * FROM products WHERE NOT discontinued;- Use comparison and logical operators in the WHERE clause to filter rows.
- Perform arithmetic operations on columns using
+,-,*,/operators. - Alias column names using
ASin SELECT queries.
In addition, you can also create SQLite expressions using multiple functions like SUM(), LOWER(), LENGTH() etc.
Using SQLite Functions and Aggregates
SQLite comes equipped with a useful set of inbuilt functions that perform operations on data:
- Date functions –
date(),time(),datetime(),julianday()etc. - Text functions –
lower(),upper(),trim(),replace()etc. - Numeric functions –
abs(),random(),round(),printf()etc. - Aggregate functions –
max(),min(),avg(),count()etc.
Some examples:
SELECT LOWER(name) FROM users;
SELECT price * 0.9 AS discount FROM products;
SELECT MAX(price), AVG(price) FROM products;SQLite functions help write complex data transformations and business logic using SQL queries without application code.
You can check the documentation for the full list of SQLite functions.
Using SQLite Views
SQLite views allow saving commonly used or complex queries by giving them a name. For example:
CREATE VIEW top_products AS
SELECT * FROM products
WHERE price > 50
ORDER BY price DESC;
SELECT * FROM top_products;- The view query is not executed until you reference the view.
- You can query a view like a regular table in SELECT, INSERT, UPDATE etc.
- Views help simplify and reuse complex SQL queries in your application logic.
- Views can join data from multiple tables or contain specialized logic.
Overall, views are helpful for simplifying access to commonly used derived data.
Using SQLite Triggers
Triggers allow executing custom logic automatically when data modification events occur in the database. Some examples:
CREATE TRIGGER delete_user AFTER DELETE ON users
BEGIN
-- Custom logic here
END;
CREATE TRIGGER update_product AFTER UPDATE OF price ON products
BEGIN
-- Custom logic here
END;- Triggers run SQL code on INSERT, UPDATE, DELETE events.
- BEFORE vs AFTER determines when the trigger runs.
- ON clause picks which table the trigger monitors.
- OF option further filters to specific columns updated.
Triggers help enforce business logic, validation, cascading actions etc. when data changes.
Using SQLite Transactions
SQLite transactions allow executing multiple SQL statements in an atomic manner where either all succeed or everything is rolled back.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;- START TRANSACTION begins a new transaction block.
- COMMIT writes changes to database.
- ROLLBACK discards all changes since last commit.
- Helps maintain consistency and handles errors/crashes.
Transactions ensure bulk database updates either completely succeed or fail cleanly without side effects.
Importing and Exporting Data in SQLite
SQLite provides ways to import data from and export data to external file formats:
.import FILE TABLE– Import data from CSV, JSON etc. into a table.export TABLE FILE– Export data from table into various formats
For example:
.import users.csv users
.export users json users.jsonThis provides an easy way to populate SQLite tables from existing data as well as migrate data to other systems.
Using SQLite from Programming Languages
SQLite can be used from various programming languages using language-specific libraries or modules:
- Python –
sqlite3module - PHP –
sqlite3extension - JavaScript –
sqlite3NPM package - C/C++ –
sqlite3.hheader file - Java –
sqlite-jdbcJDBC driver - Go –
sqlite3Go package
These provide APIs for connecting to SQLite databases, executing SQL statements and processing results returned.
The SQL syntax itself remains identical – the libraries handle interfacing with SQLite databases.
Using SQLite with Node.js
Here is an example of using SQLite with Node.js by installing the sqlite3 package:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./data.db');
db.run('CREATE TABLE IF NOT EXISTS users (id INT, name TEXT)');
db.run('INSERT INTO users VALUES (?, ?)', [1, 'John'], error => {
if (error) throw error;
console.log('User created!');
});
db.all('SELECT * FROM users', (error, rows) => {
if (error) throw error;
console.log(rows);
});
db.close();- Require
sqlite3package and open database connection - Execute queries using
db.run(), pass bindings as second arg - Use
db.all()to retrieve query results - Close database when done
The Node.js sqlite3 API provides a straightforward way to work with SQLite databases.
Using SQLite Transactions with Python
Here is an example of using transactions in SQLite with Python’s sqlite3 module:
import sqlite3
conn = sqlite3.connect('data.db')
conn.execute('CREATE TABLE IF NOT EXISTS users (id INT, name TEXT)')
conn.execute('INSERT INTO users VALUES (1, "John")')
conn.commit()
conn.execute('INSERT INTO users VALUES (2, "Jane")')
conn.execute('INSERT INTO users VALUES (3, "Joe")')
conn.commit()
print('Users inserted!')
conn.close()- Open connection using
sqlite3.connect() - Create tables and insert data
- Changes are committed when
conn.commit()is called - Transactions ensure multiple inserts are atomic
The Python sqlite3 module supports transactions and other SQLite features.
Using SQLite with Java JDBC
Here is an example of using SQLite databases in a Java JDBC program:
import java.sql.*;
public class Main {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stmt = conn.createStatement();
stmt.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT)");
stmt.execute("INSERT INTO users VALUES (1, 'John')");
System.out.println("User inserted!");
conn.close();
}
}- Load JDBC driver class
- Open SQLite connection using connection string
- Create statements to execute SQL queries
- Execute INSERT statement to add data
- Close connection when done
Advantages of Using SQLite
Here are some of the advantages of using SQLite databases:
- Lightweight – Single compact library, small database files.
- Serverless – No complex server setup required.
- Portable – Database contained in single cross-platform file.
SQLite Schema Design and Normalization
Proper database schema design is important for building SQLite databases that are structured efficiently and avoid data inconsistencies. Here are some key principles for SQLite schema design:
- Atomic columns – Each column should contain values about a single attribute.
- Unique identifiers – Use INTEGER PRIMARY KEY columns as row identifiers.
- Normalize to avoid duplication – Split data into multiple tables connected by foreign keys to avoid duplicate data.
- Use relations – Connect related tables using foreign key constraints.
- Choose appropriate data types – Use TEXT, INTEGER, REAL, BLOB etc. correctly based on data being stored.
In general, SQLite databases should be normalized to at least the third normal form (3NF) to eliminate data redundancy and inconsistencies.
Normalizing a SQLite Database to 3NF
Let’s take an example of normalizing a database that stores book information.
1NF – Atomic Columns
First normal form (1NF) means each cell contains a single value from the attribute domain.
Table: books
ISBN Title, Author, Year, Genre
012345 ...Split into multiple atomic columns:
Table: books
ISBN Title Author Year Genre
012345 ...2NF – Unique Rows
Second normal form (2NF) requires having a primary key so each row can be uniquely identified.
Table: books
ISBN (PK) Title Author Year Genre
012345 ...3NF – No Transitive Dependencies
Third normal form (3NF) means no transitive dependencies – Split based on dependencies:
Table: books
ISBN (PK) Title
012345 ...
Table: book_authors
ISBN (FK) Author
012345 ...
Table: book_info
ISBN (FK) Year Genre
012345 ...The original table is split to avoid duplicate author info for each book. The ISBN primary key connects the related rows.
Using Foreign Key Constraints in SQLite
To properly connect related tables and enforce referential integrity, foreign key constraints should be used in SQLite database schema design.
For example:
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);FOREIGN KEYdefines a column as foreign key.REFERENCESspecifies which column in another table it refers to.
This ensures deleting or updating a customer also deletes associated orders. Cascading options are also available.
Foreign keys help maintain consistency across related tables as data changes.
Using SQLite Indexes for Performance
Adding indexes on columns improves query performance by allowing fast lookups of rows without scanning the entire table.
CREATE INDEX idx_product_name ON products(name);
CREATE UNIQUE INDEX idx_product_code ON products(product_code);CREATE INDEXadds a new index on one or more columns.UNIQUEonly allows unique values to be indexed.- Indexes speed up WHERE, ORDER BY, JOIN queries on indexed columns.
- Add indexes on columns frequently filtered or sorted on.
- UNIQUE indexes also enforce uniqueness constraints.
Proper use of indexes is crucial for good performance as the dataset grows.
Tuning Queries for SQLite Performance
Some ways to optimize SQLite queries for best performance include:
- Use EXPLAIN – Add EXPLAIN keyword to view query execution plan
- Enable Write-Ahead Logging – Improves concurrency and crash recovery
- Rewrite slow queries – Avoid expensive operations like OR clauses
- Create indexes – Speed up queries filtering on a column
- Limit caching – PRAGMA cache_size limits query cache memory
- Use transactions – Commits data in batches instead of row-by-row
- Read/write separation – Separate disk files for reads vs writes
Properly structured schemas, queries and indexes result in huge performance gains for read and write operations.
Integrating SQLite with iOS Apps
SQLite is built into iOS providing a lightweight persistent data store. Here is an example using SQLite APIs in Swift:
import SQLite
let db = try Connection("path/to/db.sqlite3")
try db.run(users.create(ifNotExists: true))
try db.run(
users.insert(
name <- "John",
age <- 30
)
)
for user in try db.prepare(users) {
print(user)
}- Create/open SQLite database connection
- Run CREATE TABLE and INSERT queries
- Use a prepared statement to loop through results
The built-in iOS SQLite integration makes it easy to add offline data storage to apps.
Using SQLite Database Browsers
SQLite database browsers provide a GUI to view, query, create and manage SQLite database files:
- DB Browser for SQLite – Cross-platform GUI tool for SQLite
- SQLite Studio – Windows SQLite browser with visual schema designer
- SQLite Expanse – Mac OS browser with query builder and profiler
- SQLite Administrator – Browser for SQLite on Windows
- RazorSQL – Supports SQLite management along with other databases
These tools are helpful for tasks like visualizing schemas, building queries, exporting data and optimizing performance.
Integrating SQLite with Django Python
The Django Python framework has excellent integration with SQLite allowing you to easily incorporate it as the database for your apps.
With Django, you specify SQLite in the DATABASES setting:
# settings.py
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
}
}You can then define models which get automatically mapped to database tables. Django handles creating the schema, queries, migrations etc. making SQLite integration seamless.
Using SQLite Full-Text Search
SQLite includes support for full-text indexing and search of text content stored in columns.
For example:
CREATE VIRTUAL TABLE books USING FTS4(title, content);
INSERT INTO books (title, content) VALUES
('SQL Guide', 'Teaches basics of SQL.'),
('Learn Python', 'Introduces Python programming.');
SELECT * FROM books WHERE content MATCH 'programming';- FTS4 virtual table enables full-text search for table.
- MATCH operator searches for matches in full-text indexed columns.
- Helps build powerful search features into applications.
SQLite full-text search provides basic but fast text search capabilities.
Using Geospatial Extensions in SQLite
SQLite includes geospatial extensions that add support for location data. Key capabilities:
- Store points, lines, polygons in GeoJSON format.
- Use SQL functions like ST_Intersects(), ST_Distance() in queries.
- Build location-based queries using geospatial indexes.
- Integrate mapping visualization using tile servers.
For example:
SELECT name, AsGeoJSON(location) FROM places;
SELECT * FROM places
WHERE ST_Intersects(location, ST_Polygon(...));This allows building location-aware database applications with SQLite.
Using SQLite via REST APIs
SQLite lacks a networking layer or client-server capabilities. However, SQLite databases can be exposed over REST APIs using various frameworks:
- SQLite+REST – Provides auto-generated REST APIs for SQLite tables
- Datasette – Creates JSON REST API for any SQLite database
- Next.js API Routes – Wraps SQLite operations in API routes
- FastAPI – SQLite integration with Python FastAPI web framework
For example, Datasette exposes a /articles endpoint to fetch rows from a table.
This allows SQLite data to be consumed across the network by multiple clients.
Alternatives to SQLite
While SQLite works great as an embedded lightweight serverless database, there are limitations in terms of database size, concurrent access, reporting etc. Some popular alternatives include:
- PostgreSQL – Advanced open-source RDBMS with full client-server capabilities
- MySQL – Popular relational database with extensive features
- Microsoft SQL Server – Full-featured enterprise database from Microsoft
- MongoDB – Leading NoSQL document-oriented database
- Redis – In-memory data store used for caching, queues, pub-sub
For data-intensive apps needing scalability, transactions, access control etc. a client-server database like Postgres is a
better choice.
SQLite is more suitable as an internal data store integrated into desktop/mobile apps.
Key Differences Between SQLite and MySQL
MySQL and SQLite both are relational databases, but with key differences:
| SQLite | MySQL |
|---|---|
| Embedded, serverless library | Client-server RDBMS |
| Single-file, max TBs storage | Multiple storage engines |
| Local disk I/O access | Network access |
| Limited concurrency due to locking | High concurrency with transactions |
| No user accounts, access control | Advanced security features |
| No backup/recovery tools | Backup, replication, failover capabilities |
| Limited reporting features | Advanced analytics, reporting possible |
SQLite excels at offline portable data storage while MySQL is better suited for multi-user networked applications needing scalability and security.
Summary
SQLite is a simple yet powerful embedded SQL database engine that provides key data storage and querying capabilities for desktop and mobile applications. Its self-contained, serverless, zero-configuration nature makes SQLite the perfect choice for building offline data support into apps. I hope this guide provided a comprehensive overview of SQLite and helps you get started with using SQLite in your own projects!