sqlite3 - macOS

Overview

sqlite3 is a command-line tool used to manage and interact with SQLite databases on macOS. It provides a comprehensive interface for creating, querying, modifying, and managing databases.

Syntax

sqlite3 [options] <database-file> [<sql-statement>]

Options/Flags

| Flag | Description | Default |
| — | — | — |
| -batch | Execute SQL statements in batch mode | No |
| -cmd <command> | Run a single SQL command | No |
| -column | Show column names | No |
| -csv | Output data in CSV format | No |
| -echo | Print SQL statements before execution | No |
| -header | Print column names as the first line of output | No |
| -help | Display command help | No |
| -html | Output data as HTML | No |
| -interactive | Start an interactive SQLite shell | No |
| -json | Output data in JSON format | No |
| -line | Print each row on a separate line | No |
| -memory | Use an in-memory database | No |
| -nullvalue <value> | Define a custom null value string | NULL |
| -separator <char> | Set the field separator | |
| -stats | Show internal SQLite statistics | No |
| -table | Print a list of table names | No |
| -version | Display SQLite library version | No |
| -version | Display SQLite library version | No |

Examples

Creating a Database

sqlite3 my-database.db

Querying Data

sqlite3 my-database.db "SELECT * FROM users;"

Modifying Data

sqlite3 my-database.db "UPDATE users SET name='John Doe' WHERE id=1;"

Managing Database Structure

sqlite3 my-database.db "ALTER TABLE users ADD COLUMN email VARCHAR(255);"

Common Issues

  • Database file not found: Ensure that the specified database file exists and is accessible.
  • Syntax errors: Carefully check SQL statements for any syntax mistakes or missing delimiters.
  • Permission denied: Verify that you have the necessary permissions to access, create, or modify the database.

Integration

sqlite3 can be integrated with other commands using pipes or shell redirection. For example, to filter data from a table and save it to a text file:

sqlite3 my-database.db "SELECT * FROM users;" | tee users.txt
  • sqlite – Official SQLite command-line tool
  • sqlcmd – Microsoft SQL Server command-line tool
  • psql – PostgreSQL command-line tool