SQLite

SQLite database quick reference.

• Official Documentation

SQLite is in process database. It does not need any port for listening and accepting new connections.

Identify SQLite files

SQLite files have extension “.db”.

file <filename>.db

Databases.db: SQLite 3.x database

Install SQLite

apt-get -y install sqlite3

Help

sqlite3
.help
.quit

Backup Databases

DB_PATH=/home/sqlite/dbname.db
BACKUP_PATH=/usr/local/src/dbname.bak

sqlite3 $DB_PATH .dump > $BACKUP_PATH

Connect to instance

DB_PATH=/home/sqlite/dbname.db
sqlite3 $DB_PATH

Execute one query

sqlite3 $DB_PATH ".tables"

List database

.databases
.dbinfo
.dbinfo ?DB?

List tables

.tables

.tables moz%

SELECT name FROM sqlite_schema 
WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%'
ORDER BY 1;

List table and column structure

PRAGMA table_info(<table name>)

.fullschema

SELECT

select * from moz_places;

Write to file

.output sqltest.txt
select * from moz_places;
.exit
cat sqltest.txt

Run shell commands

.shell whoami

.system whoami

Configurations / Parameters

.dbconfig
.dbconfig ?op? ?val?
.show

Default Users / Passwords

SQLite has no concept of user accounts, and instead relies on the file system for all database permissions.