The SQLite Handbook: A Start-to-Finish Resource for Learning and Using SQLite

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 database
• CREATE TABLE – Create a new table
• INSERT – Insert a new row into a table
• SELECT – Query data from a table
• UPDATE – Modify existing data in a table
• DELETE – Delete rows from a table
• DROP 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 KEY which uniquely identifies each row.
• The PRIMARY KEY column must have INTEGER type and allow NULL values.
• You can add additional constraints like NOT NULL, UNIQUE, DEFAULT etc. 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 .tables command 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 DATABASE loads the external file users.db into the connection.
• Tables in the attached database can be accessed using database.table format.
• When done, use DETACH DATABASE to 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 AS in 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.json

This 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 – sqlite3 module
• PHP – sqlite3 extension
• JavaScript – sqlite3 NPM package
• C/C++ – sqlite3.h header file
• Java – sqlite-jdbc JDBC driver
• Go – sqlite3 Go 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 sqlite3 package 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 KEY defines a column as foreign key.
• REFERENCES specifies 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 INDEX adds a new index on one or more columns.
• UNIQUE only 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 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!