path: root/SQL/SQLite Commands.md

Ray Ferrell

September 9, 2023

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](https://sqldocs.org/sqlite/sqlite-create-database/)_**

1. [SQLite Data Definition Language (DDL) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-definition-language-ddl-commands "SQLite Data Definition Language (DDL) Commands")
    1. [CREATE](https://sqldocs.org/sqlite/sqlite-commands/#create "CREATE")
    2. [ALTER](https://sqldocs.org/sqlite/sqlite-commands/#alter "ALTER")
    3. [DROP](https://sqldocs.org/sqlite/sqlite-commands/#drop "DROP")
2. [SQLite Data Manipulation Language (DML) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-manipulation-language-dml-commands "SQLite Data Manipulation Language (DML) Commands")
    1. [INSERT](https://sqldocs.org/sqlite/sqlite-commands/#insert "INSERT")
    2. [UPDATE](https://sqldocs.org/sqlite/sqlite-commands/#update "UPDATE")
    3. [DELETE](https://sqldocs.org/sqlite/sqlite-commands/#delete "DELETE")
    4. [REPLACE](https://sqldocs.org/sqlite/sqlite-commands/#replace "REPLACE")
3. [SQLite Data Query Language (DQL) Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-data-query-language-dql-commands "SQLite Data Query Language (DQL) Commands")
    1. [SELECT](https://sqldocs.org/sqlite/sqlite-commands/#select "SELECT")
4. [SQLite Transaction Control Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-transaction-control-commands "SQLite Transaction Control Commands")
    1. [BEGIN TRANSACTION](https://sqldocs.org/sqlite/sqlite-commands/#begin-transaction "BEGIN TRANSACTION")
    2. [COMMIT](https://sqldocs.org/sqlite/sqlite-commands/#commit "COMMIT")
    3. [ROLLBACK](https://sqldocs.org/sqlite/sqlite-commands/#rollback "ROLLBACK")
5. [SQLite Administration Commands](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-administration-commands "SQLite Administration Commands")
6. [SQLite Dot Commands for sqlite3](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-dot-commands-for-sqlite3 "SQLite Dot Commands for sqlite3")
7. [Using Transactions in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#using-transactions-in-sqlite "Using Transactions in SQLite")
8. [Making SQLite Backups](https://sqldocs.org/sqlite/sqlite-commands/#making-sqlite-backups "Making SQLite Backups")
9. [Importing and Exporting Data](https://sqldocs.org/sqlite/sqlite-commands/#importing-and-exporting-data "Importing and Exporting Data")
    1. [Importing Data](https://sqldocs.org/sqlite/sqlite-commands/#importing-data "Importing Data") 
    2. [Exporting Data](https://sqldocs.org/sqlite/sqlite-commands/#exporting-data "Exporting Data")
10. [Full-text Search in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#full-text-search-in-sqlite "Full-text Search in SQLite")
11. [User Authentication Methods in SQLite](https://sqldocs.org/sqlite/sqlite-commands/#user-authentication-methods-in-sqlite "User Authentication Methods in SQLite")
12. [Statistical and Diagnostic Functions](https://sqldocs.org/sqlite/sqlite-commands/#statistical-and-diagnostic-functions "Statistical and Diagnostic Functions")
    1. [Statistical Aggregates](https://sqldocs.org/sqlite/sqlite-commands/#statistical-aggregates "Statistical Aggregates")
13. [Advanced Topics](https://sqldocs.org/sqlite/sqlite-commands/#advanced-topics "Advanced Topics")
    1. [SQLite Extensions](https://sqldocs.org/sqlite/sqlite-commands/#sqlite-extensions "SQLite Extensions")
    2. [JSON Support](https://sqldocs.org/sqlite/sqlite-commands/#json-support "JSON Support")
    3. [Database Encryption](https://sqldocs.org/sqlite/sqlite-commands/#database-encryption "Database Encryption")
    4. [Multi-Threading](https://sqldocs.org/sqlite/sqlite-commands/#multi-threading "Multi-Threading")
    5. [And More](https://sqldocs.org/sqlite/sqlite-commands/#and-more "And More")

## 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](https://sqldocs.org/sqlite/sqlite-create-table/):

```sql
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:

```sql
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:

```sql
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:

```sql
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
```

And you can insert multiple rows at once:

```sql
INSERT INTO users (name, email) VALUES ('Mary', 'mary@example.com'), ('Lee', 'lee@example.com');
```

### UPDATE

UPDATE modifies existing data in a table. For example:

```sql
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:

```sql
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](https://sqldocs.org/sqlite/sqlite-operators/)_**

## 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:

```sql
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:

```sql
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](https://sqldocs.org/sqlite/sqlite-select-statement/) syntax like subqueries, UNIONs, GROUP BY, ORDER BY, [LIMIT](https://sqldocs.org/sqlite/sqlite-limit/) and much more. Familiarity with SELECT is a must for working effectively with SQLite.

**_Also read: [SQLite Data Types: An In-Depth Guide](https://sqldocs.org/sqlite/sqlite-data-types/)_**

## SQLite Transaction Control Commands

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

![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")

### 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.

## ![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")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:

```sql
-- 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
-- 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.

![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")

### 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.

![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")

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.

![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")

![Ezoic](https://go.ezodn.com/utilcave_com/ezoicbwa.png "ezoic")

Ray Ferrell

Ray is a data nerd and a technical content creator for multiple websites. He enjoys writing on topics related to SQL databases, data science, machine learning, and more. When not working, you can find him spending quality time with his family and friends.

**Previous:** [SQLite Create Table](https://sqldocs.org/sqlite/sqlite-create-table/)

**Read next:** [SQLite Attach Database](https://sqldocs.org/sqlite/sqlite-attach-database/)

**Important Links**

[Terms of Use](https://sqldocs.org/terms/)

[Privacy Policy](https://sqldocs.org/privacy)

[Advertise](https://sqldocs.org/advertise/)

**Company**

[Home](https://sqldocs.org/)

[About Us](https://sqldocs.org/about/)

[Write for Us](https://sqldocs.org/write-for-us)

[Careers](https://www.linkedin.com/company/sql-docs/jobs/)

**Resources**

[SQLite](https://sqldocs.org/sqlite/)

[SQLite vs MySQL](https://sqldocs.org/sqlite/sqlite-vs-mysql/)

[SQLite vs. PostgrSQL](https://sqldocs.org/sqlite/sqlite-vs-postgresql/)

**Follow SQLDocs**

[Twitter (X)](https://x.com/SQLDocsOrg)

[LinkedIn](https://www.linkedin.com/company/sql-docs/)

Copyright © 2022 – [SQL Documentation](https://sqldocs.org/)