SQLite

The database browser

Tap a database in the file tree and it opens in the database browser instead of the editor. Pythilo recognises a SQLite file by its contents, so db.sqlite3, app.db or a database with any other name all open there. At the top, pick a table (each shows its row count) and one of three views: Data, Schema and SQL. The Database actions menu has Reload, Open in the terminal (which runs sqlite3 on the file) and Share file.

A new Django project has no tables until you migrate. The browser says so: create one in the SQL tab, or run python manage.py migrate.

Data: rows, filter and editing

• Rows show in a grid, 100 at a time; Show … more loads the next ones.
• Filter rows keeps the rows where any column contains what you type.
• Tap a row to edit it: change values, set a column to NULL with its switch, then Save, or Delete row.
• Add row opens the same form for a new row. Primary-key columns start as NULL, so SQLite fills them in.

Edits are written to the file straight away. Commit the database with Git first if you may want to undo. Views, and tables created WITHOUT ROWID, are shown read-only.

Schema

Each column with its type, a key for the primary key, and NOT NULL where it applies, followed by the table's CREATE statement.

SQL

Write any SQL and tap Run. A query shows its rows (up to the first 1000) and how many there were; any other statement says how many rows it changed.

sqlite3 in the terminal

The sqlite3 command works like the SQLite shell on a computer, built on Python's sqlite3 module:

sqlite3 db.sqlite3                      # interactive shell; .quit leaves
sqlite3 db.sqlite3 "select count(*) from notes_note"
sqlite3 -box db.sqlite3 "select * from notes_note limit 5"
sqlite3 db.sqlite3 < schema.sql         # run a script

Options: -header, -noheader, the output modes -csv, -json, -line, -list, -column, -box, -table, -markdown and -tabs, plus -separator, -nullvalue, -readonly, -cmd, -bail and -version. It works in pipes and with redirects, like any other command.

Dot commands

From Python

Python's own sqlite3 module works in every project, and Django uses SQLite for its database out of the box. python manage.py dbshell is not available, because it starts an external program; use sqlite3 db.sqlite3 instead.