There are 2 ways to use sqlite3 in terminal.

• interactive: run sqlite3 and input command (start with dot, like .dump etc) or SQL command end with semicolon;
• non-interactive: run sqlite3 <dbname> <command>, for example sqlite3 mydb.sqlite3 .dump or sqlite3 mydb.sqlite3 'select * from tab1. Options can be added before . List them with sqlite3 -help.

Dump sqlite3 database to sql (text) files: sqlite3 mydb.sqlite3 .dump > mydb.sql.

Dump a table as CSV: sqlite3 -header -csv mydb.sqlite3 'select * from tab1' > tab1.csv

Written in 2017.2.7:

Create a new sqlite database:

git clone git@github.com:leetschau/zap-ring.git
cd zap-ring
sqlite3 -init resources/data/schema.sql zap.db .quit

GUI client

With Squirrel SQL as Sqlite Client, first download sqlite jdbc driver sqlite-jdbc-3.16.1.jar from Maven Repo to ~/warez folder.

Add a driver in Squirrel SQL Client: Name: Sqlite Example URL: jdbc:sqlite:/home/leo/work/mydatabase.db Website URL: http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc

Choose Extra Class Path tab, add ~/warez/sqlite-jdbc-3.16.1.jar. Click List Drivers, Class Name will be filled with org.sqlite.JDBC automatically.

Add a new alias in Squirrel SQL Client with URL, say jdbc:sqlite:/home/leo/workspace/zap-ring/zap.db.

Now you can browse and modify this local sqlite database.

CLI client

On Ubuntu 14.04 sqlite3 is installed by default.

sqlite3
.help
.open zap.db
# or in one step: `sqlite3 zap.db`
.tables
select * from status;

Note:

Official SQLite JDBC repository.

If the official maven repository can't be connected, you can use aliyun maven repo instead. But their urls have not strict mapping relations. For example:

sqlite jdbc driver: http://mvnrepository.com/artifact/org.xerial/sqlite-jdbc http://maven.aliyun.com/nexus/content/groups/public/org/xerial/sqlite-jdbc

Mongo jdbc driver: http://mvnrepository.com/artifact/org.mongodb/mongo-java-driver http://maven.aliyun.com/nexus/content/groups/public/org/mongodb/mongo-java-driver