Back to Commands

sqlite3

databaseLinux/Unix
The sqlite3 command is one of the most frequently used commands in Linux/Unix-like operating systems. sqlite3 Command-line interface for SQLite 3 databases
See ExamplesView Syntax

Quick Reference

Command Name:

sqlite3

Category:

database

Platform:

Linux/Unix

Basic Usage:

sqlite3 [options] [arguments]

Common Use Cases

    Syntax

    sqlite3 [options] [database] [SQL statements]

    Options

    Option Description
    -append Append to output file instead of overwriting
    -ascii Set output mode to 'ascii'
    -batch Force batch I/O
    -column Set output mode to 'column'
    -cmd COMMAND Run COMMAND before reading stdin
    -csv Set output mode to 'csv'
    -echo Echo commands before execution
    -header Turn on column headers
    -help Show help message
    -html Set output mode to HTML
    -init FILENAME Read/process named file
    -interactive Force interactive I/O
    -json Set output mode to 'json'
    -line Set output mode to 'line'
    -list Set output mode to 'list'
    -markdown Set output mode to 'markdown'
    -newline SEP Set output row separator (default: \n)
    -noheader Turn off column headers
    -nullvalue TEXT Set text string for NULL values
    -separator SEP Set output column separator (default: |)
    -stats Print memory stats before each finalize
    -table Set output mode to 'table'
    -version Show SQLite version
    Dot Commands Description
    .backup ?DB? FILE Backup database to FILE
    .databases List names and files of attached databases
    .dump ?TABLE? Dump database as SQL text
    .exit Exit this program
    .headers on|off Turn display of headers on or off
    .import FILE TABLE Import data from FILE into TABLE
    .indices ?TABLE? Show names of indexes
    .mode MODE Set output mode (csv, column, html, insert, line, list, tabs, tcl)
    .open ?FILENAME? Close existing database and open FILENAME
    .output FILENAME Send output to FILENAME
    .quit Exit this program
    .read FILENAME Execute SQL in FILENAME
    .restore ?DB? FILE Restore content of DB from FILE
    .schema ?TABLE? Show the CREATE statements
    .separator STRING Change separator for CSV and column modes
    .tables ?PATTERN? List names of tables matching PATTERN

    Examples

    How to Use These Examples

    The examples below show common ways to use the sqlite3 command. Try them in your terminal to see the results. You can copy any example by clicking on the code block.

    # Basic Examples Basic
    sqlite3 database.db
    Open or create a SQLite database file in interactive mode.
    sqlite3 database.db "SELECT * FROM users"
    Execute a SQL query directly from the command line.
    sqlite3 database.db < commands.sql
    Execute SQL commands from a file. # Advanced Examples Advanced # Create a new database with tables sqlite3 new_database.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT); INSERT INTO users VALUES (1, 'John Doe', 'john@example.com');" # Export query results to CSV sqlite3 -csv database.db "SELECT * FROM users" > users.csv # Pretty-print table output with headers sqlite3 -header -column database.db "SELECT * FROM users" # Create database from stdin 
    cat schema.sql | sqlite3 database.db
    # Backup a database sqlite3 database.db ".backup backup_file.db" # Restore from backup sqlite3 database.db ".restore backup_file.db" # Execute SQL and format output as JSON sqlite3 -json database.db "SELECT * FROM users" # Load CSV data into a table sqlite3 database.db ".mode csv" ".import data.csv tablename" # Execute commands from interactive mode sqlite3 database.db sqlite> .tables sqlite> .schema users sqlite> SELECT * FROM users; sqlite> .quit # Export entire database as SQL statements sqlite3 database.db ".dump" > database_dump.sql # Show database stats sqlite3 database.db ".dbinfo" # Run a complex query with multiple statements sqlite3 database.db < SELECT u.name, COUNT(o.id) as order_count FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id; EOF # Use pragmas to set database options sqlite3 database.db "PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL;"

    Try It Yourself

    Practice makes perfect! The best way to learn is by trying these examples on your own system with real files.

    Understanding Syntax

    Pay attention to the syntax coloring: commands, options, and file paths are highlighted differently.

    Notes

    The `sqlite3` command is a powerful command-line interface (CLI) tool for working with SQLite databases. SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine that is the most widely deployed database in the world, found in countless applications from mobile apps to desktop software. Unlike client-server database systems, SQLite doesn't require a separate server process. The entire database is stored in a single cross-platform file, making it extremely portable and ideal for embedded systems, prototyping, testing, or situations where simplicity is preferred over advanced features of larger database systems. The `sqlite3` command provides direct access to create, query, and manage SQLite databases from the terminal. It can be used in two primary modes: 1. Interactive Mode: When invoked with just a database filename, it opens a prompt where users can type SQL commands and special dot-commands (prefixed with a period) that control the SQLite environment. 2. Non-interactive Mode: When SQL statements are provided directly on the command line or piped from other commands, `sqlite3` executes them and returns the results without entering interactive mode. Key features of the `sqlite3` command include: - Database Creation and Management: Easily create new databases, modify schemas, and manage data. - Flexible Output Formats: Results can be formatted as plain text, CSV, HTML, JSON, and other formats for easy integration with other tools. - Import/Export Capabilities: Data can be imported from or exported to various formats, including SQL dumps for backup and restoration. - Scripting Support: Commands can be read from files, making it suitable for automated database operations. - Dot Commands: Special non-SQL commands prefixed with a period (.) provide meta-functionality like showing schema information, setting output formats, and managing database files. - Transaction Support: Full ACID compliance ensures data integrity even when operations are interrupted. The `sqlite3` command is particularly useful for: - Database development and prototyping - Scriptable database operations in shell scripts - Data extraction and transformation tasks - Database maintenance and troubleshooting - Educational purposes when learning SQL - Quick data analysis without setting up complex database systems Despite its simplicity, SQLite and the `sqlite3` command support most of the SQL standard, including complex queries, triggers, views, and transactions. This makes it a versatile tool for both simple and moderately complex database needs.

    Related Commands

    These commands are frequently used alongside sqlite3 or serve similar purposes:

    mysql
    psql
    mongosh
    redis-cli
    vacuum
    pragma
    vacuum
    Explore All Commands

    Use Cases

    Learn By Doing

    The best way to learn Linux commands is by practicing. Try out these examples in your terminal to build muscle memory and understand how the sqlite3 command works in different scenarios.

    See ExamplesTry Lab Exercises
    $ sqlite3
    View All Commands