SQLite Reminders

This will come in handy...


sqlite3 some.db - open a db, or create one and open it if id doesn't exist

.tables - show all tables in the db

.exit - quit sqlite3

.help - show help

.show - lists various config settings


.headers ON - displays column names

.mode columns - puts results in columns

.width 22, 18 - sets column widths

Note: normally you see .headers ON and .mode columns used together


pragma table_info(some_table) - gets info about a table

select pragma user_version - gets the db version

pragma user_version = 2 - sets db version to 2


.dump sometable - lists all sql to recreate the table (create statements and inserts)

.output sometable.sql - dumps sometable to a file named sometable.sql

.read sometable.sql - dumps the file onto the screen


sqlite3 -html some.db - now all your select queries will be returned as html tables


There is nothing stopping you from using any data type for you columns. It could be 'blah' for all sqlite cares. If you do this, you might not get the results you expect in queries when using order by (and probably other stuff too). In one case we noticed that we couldn't sort our datetime column properly. Later we discovered that there was no datetime type in sqlite, and it treated the values as strings.