sqlite3: The SQLite Shell

Get fluent in the sqlite3 shell — open a database file, run queries, format and export results with dot-commands, read schema and data from files, read a query plan, and make the client your own.

SQLite is the database you already have — a single file, no server, shipped inside phones, browsers and countless apps. The way you meet it on a box is its shell, sqlite3: a REPL much like psql, but with its own personality. We start from an empty prompt and build real fluency one small thing at a time: opening a database file, switching from its ugly default output to clean Unicode tables, loading schema and data from files, querying and summarising, reading a query plan to see when an index is missing, importing and exporting CSV, and finally shaping the client with a startup file. Writing a query and running it are taught as separate steps, so each idea lands on its own.

What you'll build

• Open a SQLite database file and tell SQL statements from dot-commands
• Format results as Unicode tables and turn on headers
• Load schema and data from .sql files
• Read, order and summarise query results
• Read a query plan and add an index that changes it
• Import and export CSV, and customise the shell with a .sqliterc

Contents

1. Open a database
2. Run your first query
3. Finish a statement you started
4. Leave the shell
5. Make the output readable
6. List the tables
7. Write a schema file
8. Run a file from the prompt
9. See the schema
10. Add a second table
11. Apply the updated schema
12. See both tables
13. Turn on foreign keys
14. Write a seed file
15. Load the data
16. Read a result
17. Summarise with aggregates
18. Write a reusable query
19. Run the saved query
20. See how a query runs
21. Write an index
22. Apply the index and re-plan
23. Time a query
24. Write a CSV to import
25. Import the CSV
26. Export the next result
27. Check the exported file
28. Write a startup file
29. See it take effect