sqlite

Sqlite CLI

Start CLI:

sqlite3 _DBNAME_.sqlite

Exit CLI:

<CTRL><D>

Browser Viewer

(no longer seem to have this installed)

NOTE: Chrome has a viewer built in. Drag the *.sqlite file into an empty browser window and it will automatically open the viewer.

Queries specific to Sqlite

See also: SQL Queries

Count number of rows in each table

Execute the following in the terminal to get a count of row in the test database.

echo "SELECT 'SELECT count(*), \"' || name || '\" FROM ' || name || ';'  FROM sqlite_master WHERE type = 'table';" | sqlite3 -readonly "test.sqlite3" | sqlite3 -readonly "test.sqlite3"

Execute the following in the terminal to get a count of row in the application database.

echo "SELECT 'SELECT count(*), \"' || name || '\" FROM ' || name || ';'  FROM sqlite_master WHERE type = 'table';" | sqlite3 -readonly "development.sqlite3" | sqlite3 -readonly "development.sqlite3"

List Columns in a Table

PRAGMA table_info(<TABLE_NAME>);

example results

0|id|INTEGER|0||1
1|property_name|VARCHAR(4096)|1||0
2|executed_at|INT|1||0