SQLite Commands

SQLite is an open-source, embedded relational database management system. It is a popular choice for local data storage in applications due to its lightweight nature, simplicity, and robustness.

SQLite uses SQL syntax and provides developers with powerful commands to create, query, and manage databases.

This comprehensive guide covers the most important SQLite commands for creating and managing database schemas, manipulating data, running queries, and handling connections.

We’ll explore the SQL standard commands supported by SQLite and special dot commands offered by the SQLite3 command line shell.

Also read: Create SQLite Database – Step-by-Step Guide for Beginners

SQLite Data Definition Language (DDL) Commands

Data Definition Language (DDL) commands allow you to create and modify database structures. The key DDL commands in SQLite are:

CREATE

The CREATE command creates new database objects like tables, indexes, views, and triggers.

For example, to create a new table:

CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE );

You can also use CREATE to define indexes, views, and triggers.

ALTER

The ALTER command modifies the structure of existing database objects. For example, to add a new column to a table:

ALTER TABLE users ADD COLUMN phone TEXT;

Other usages include renaming or dropping columns, adding table constraints, and renaming tables.

DROP

The DROP command removes objects from the database. For example, to remove a table:

DROP TABLE users;

You can also DROP indexes, views, and triggers.

SQLite Data Manipulation Language (DML) Commands

Data Manipulation Language (DML) commands allow inserting, modifying, and deleting data within schema objects like tables. Key commands include:

INSERT

INSERT adds new rows of data into a table. For example:

INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');

And you can insert multiple rows at once:

INSERT INTO users (name, email) VALUES ('Mary', '[email protected]'), ('Lee', '[email protected]');

UPDATE

UPDATE modifies existing data in a table. For example:

UPDATE users SET name = 'John Taylor' WHERE id = 123;

Updates can affect multiple table rows at once.

DELETE

DELETE removes rows from a table. For example:

DELETE FROM users WHERE id = 123;

Similar to UPDATE, DELETE can remove multiple rows in one statement.

REPLACE

The REPLACE command does an INSERT or UPDATE depending on whether the row already exists. From the SQLite documentation:

For each row proposed for insertion, if a row with the same primary key already exists in the database, delete it first and then insert the new row.

This provides UPSERT functionality in a single statement.

Also read: SQLite Operators: A Comprehensive Guide

SQLite Data Query Language (DQL) Commands

Data Query Language (DQL) commands allow you to query, search, and filter data stored in the database. The most important command is:

SELECT

SELECT retrieves data from one or more tables. For example:

SELECT id, name FROM users;

This query selects the id and name columns from the users table.

SELECT supports powerful filtering capabilities using WHERE clauses:

SELECT * FROM users WHERE age >= 18 AND city = 'Boston';

And advanced JOIN operations allow selecting data across multiple related tables.

SQLite supports nearly all standard SQL SELECT syntax like subqueries, UNIONs, GROUP BY, ORDER BY, LIMIT and much more. Familiarity with SELECT is a must for working effectively with SQLite.

Also read: SQLite Data Types: An In-Depth Guide

SQLite Transaction Control Commands

Transactions allow you to bundle multiple related operations into an atomic unit. The key commands are:

BEGIN TRANSACTION

This starts a new transaction block. All subsequent DML commands execute within the context of the transaction.

COMMIT

This commits (saves) the current transaction. All changes within the block become permanent.

ROLLBACK

This rolls back (undoes) the current transaction. All changes within the block are discarded.

Transactions provide consistency, atomicity, isolation, and durability. They are crucial for the correct operation of SQLite databases in concurrent environments.

SQLite Administration Commands

Administrative commands handle tasks like user management, backups, and database maintenance. Important commands include:

• CREATE USER: Creates a new user account for authentication. Users can be granted privileges and roles.
• ALTER USER: Modifies properties of an existing user, such as changing their password.
• DROP USER: Deletes a user account.
• PRAGMA: The PRAGMA command controls various configuration settings of the SQLite library. For example, PRAGMA journal_mode sets the journaling mode.
• VACUUM: The VACUUM command rebuilds and cleans the database file, reclaiming unused space. Recommended for occasional maintenance.
• BACKUP: Makes a backup copy of an SQLite database. Useful for tasks like archiving transaction logs. More details below.

SQLite Dot Commands for sqlite3

The sqlite3 command line shell provides special “dot commands” that facilitate common tasks. These commands are prefixed with a . and are not standard SQL. Some important sqlite3 dot commands include:

• .databases: Lists names and files of attached databases; allows viewing of database connections.
• .mode: Sets output modes for displaying query results (e.g., list, column, csv).
• .headers: Toggles column headers on or off for query output.
• .tables: Displays names of tables in the connected database; useful for exploring schemas.
• .schema: Outputs the CREATE statements for database schemas; accepts regex patterns.
• .indexes: Lists names of indexes for connected databases; optional filtering available.
• .dump: Converts the database or specific tables to an SQL text file; useful for backups.
• .output FILE: Redirects output to a specified file.
• .once FILE: Similar to .output, but applies only to one query before reverting to stdout.
• .open FILE: Closes current database and opens specified file; facilitates database switching.
• .backup: Creates a backup copy of the opened database.
• .exit: Exits the sqlite3 shell program.

There are many more handy dot commands and .help will show you a full list. The dot commands provide useful functionality beyond plain SQL.

Using Transactions in SQLite

As introduced earlier, transactions are key to managing consistency in SQLite. Let’s look at a simple example:

-- Start a transaction BEGIN TRANSACTION; -- Insert transfer amount in accounts UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Commit changes COMMIT;

This safely transfers $100 from account 1 to account 2 as a single atomic operation. If either UPDATE fails, the whole transaction will roll back and protect data integrity.

Transactions have huge benefits for performance, safety, and correctness in concurrent systems. Use them liberally when multiple operations need to act as one.

Making SQLite Backups

It’s important to routinely backup critical SQLite databases in case of corruption, hardware failure, or human error. The .backup and BACKUP commands help automate this.

For example, from within the sqlite3 shell:

sqlite> .backup main main.bak

This creates a copy of the currently open “main” database to a file named main.bak.

You can also backup within a live application using the BACKUP command:

BACKUP main TO 'main.bak';

This copies the “main” database to the named backup file.

Make sure to periodically backup SQLite databases using either of these approaches. The database files are portable and easily restored.

Importing and Exporting Data

SQLite provides handy ways to move data in and out of databases.

Importing Data

The .import command loads data into a table from a file. For example:

sqlite> .mode csv sqlite> .import users.csv users

This imports the CSV file into the “users” table.

Within an application, you can also import data using the sqlite3_load_extension() function to load a custom import module.

Exporting Data

To export data, switch to the desired output mode like CSV and run a query:

sqlite> .headers on sqlite> .mode csv sqlite> .once dump.csv sqlite> SELECT * FROM users;

This exports the users table to a CSV file.

The .once dot command sends the output to a file instead of stdout.

You can also implement custom export handlers by calling sqlite3_export_extensions() and registering callback functions.

Full-text Search in SQLite

SQLite’s FTS modules enable powerful full-text searches on text data. For example:

-- Create virtual FTS table CREATE VIRTUAL TABLE docs USING fts4(title, content); -- Populate the FTS table INSERT INTO docs(title, content) VALUES ('SQLite Guide', 'Intro to using SQLite commands...'), ('SQLite Tips', 'Optimizing performance of SQLite queries...'); -- Full text search SELECT * FROM docs WHERE docs MATCH 'guide tips';

This performs a full-text search across the title and content columns, making it easy to build search functionality within applications.

SQLite FTS provides fast and flexible textual searches. Refer to the documentation for the available FTS versions and customization options.

User Authentication Methods in SQLite

SQLite provides ways to implement user authentication when opening database connections. For example:

• Using the sqlite3_user_authenticate() interface within a custom authentication handler. This allows username/password checking.
• Attaching a user-defined authorization function using sqlite3_set_authorizer(). Can authorize on a per-command basis.
• Encrypting the database file with SQLite Encryption Extension (SEE). Decryption requires user-supplied passphrase, providing authentication.

Depending on your specific needs, one of these approaches can be used to restrict and authenticate database access properly.

Statistical and Diagnostic Functions

SQLite offers several functions to support statistical analysis and database diagnostics:

Statistical Aggregates

Aggregates like STATS, STAT4 provide median, variance, mode calculations. Useful for analysis.

• rowid: Special hidden column returning 64-bit signed row ID integer; useful for diagnostics.
• last_insert_rowid(): Function that returns last inserted row ID; captures autogenerated IDs.
• total_changes(): Shows the number of row changes from the most recent SQL statement; aids in auditing.
• integrity_check: PRAGMA command for database integrity check; detects corruption.
• And more: Includes additional statistical, diagnostic, and introspection capabilities.

Advanced Topics

There are a few more advanced topics worth mentioning:

SQLite Extensions

SQLite allows developers to create custom SQL functions, aggregates, collating sequences, and virtual table implementations. These “extensions” can be compiled into shared libraries and loaded at runtime to expand SQLite’s capabilities.

JSON Support

SQLite has native support for JavaScript Object Notation (JSON) with functions like json_extract(), json_array(), and json_object(). This allows efficient storage and querying of JSON documents within an SQLite database.

Database Encryption

SQLite Encryption Extension (SEE) can encrypt database files to protect sensitive data. Makes full-disk encryption easy.

Multi-Threading

SQLite supports multi-threaded access with proper locking and isolation. This allows concurrent use by multiple processes and threads.

And More

Spatialite support, alternative storage engines like LMDB, external content tables, C/C++ usage guides… the list goes on! SQLite is a versatile database.

This covers the most essential SQLite commands and features, but there is always more to learn. SQLite’s documentation is excellent and fills in many additional details. With this foundation, you should feel comfortable using SQLite in your own projects.

The key is to start simple, learn by example, and gradually work up to more advanced capabilities over time. Mastering both the SQL standard and sqlite3 dot commands will make you a pro at working with SQLite databases.