1. Introduction

SQLite is an open source, embedded relational database which implements a self-contained, serverless, zero-configuration,transactional SQL database engine. SQLite has a well-deserved reputation for being highly portable, easy to use, compact, efficient, and reliable. Unlike client–server database management systems, installing and running of SQLite database is pretty straightforward in most cases — just make sure the SQLite binaries file exists anywhere you want and start to create, connect, and using the database. If you are looking for an embedded database for your projects or solutions, SQLite is definitely worth considering.

2. Installation

SQLite on Windows

  1. Navigate to SQLite download page at http://www.sqlite.org/download.html
  2. Download the following Precompiled Binaries For Windows:
    • sqlite-shell-win32-x86-<build#>.zip
    • sqlite-dll-win32-x86-<build#>.zip
    Note: <build#> is the running build number of sqlite.
  3. Unpack the ZIP files into your favourite folder. Add folder path to the PATH system variable to make the SQLite command line shell available within the environment.
  4. OPTIONAL: If you plan to develop any application that host a sqlite database then you will need to download the source code in order to compile and utilize its API .
    • sqlite-amalgamation-<build#>.zip

SQLite on Linux
SQLite binaries can be obtained in a variety of ways depending on the Linux distro that you are using.

/* For Debian or Ubuntu /*
$ sudo apt-get install sqlite3 sqlite3-dev
/* For RedHat, CentOS, or Fedora/*
$ yum install SQLite3 sqlite3-dev

SQLite on Mac OS X
If you are running a Mac OS Leopard or later, then it alraedy have pre-installed SQLite.

3. Create you first SQLite Database

you now should have the SQLite binaries ready and time to create your first SQLite database now. Type the following command in windows’s command prompt or Linux’s terminal.

To create a new database called test.db:

sqlite3 test.db
To create a table in the database:
sqlite> create table mytable(id integer primary key, value text);
2 columns were created. A primary key column called “id” which has the ability to automatically generate value by default and a simple text field called “value”.
NOTE: At least 1 table or view need to be created in order to commit the new database to disk. Otherwise, it won’t database won’t be created.

To insert data into mytable:
sqlite> insert into mytable(id, value) values(1, 'Micheal');
sqlite> insert into mytable(id, value) values(2, 'Jenny');
sqlite> insert into mytable(value) values('Francis');
sqlite> insert into mytable(value) values('Kerk');

To fetch data from mytable:
sqlite> select * from mytable;
1|Micheal
2|Jenny
3|Francis
4|Kerk

To fetch data from mytable by improving the formatting a little:
sqlite> .mode column;
sqlite> .header on;
sqlite> select * from mytable;
id          value
----------- -------------
1           Micheal
2           Jenny
3           Francis
4           Kerk
The .mode column will display data into column format.
The .header on will display table’s column name.

To add additional column into mytable:
sqlite> alter table mytable add column email text not null '' collate nocase;;

To create a view for mytable:
sqlite> create view nameview as select * from mytable;

To create an index for mytable:
sqlite> create index test_idx on mytable(value);

3. Useful SQLite’s command

Display table schema:

sqlite> .schema [table]

Retrieve a list of tables (and views):
sqlite> .tables

Retrieve a list indexes for a given table:
sqlite> .indeces [table]

Export database objects to SQL format:
sqlite> .output [filename]
sqlite> .dump
sqlite> .output stdout

Import database objects(SQL format) to database:
sqlite> .read [filename]

Formatting exported data into CSV format:
sqlite>.output [filename.csv]
sqlite>.separator ,
sqlite> select * from mytable;
sqlite>.output stdout

Import CSV formatted data to a new table:
sqlite>create table newtable(id integer primary key, value text);
sqlite>.import [filename.csv] newtable

To backup database:
/* usage: sqlite3 [database] .dump > [filename] */
sqlite3 mytable.db .dump > backup.sql

To restore database:
/* usage: sqlite3 [database] < [filename] */
sqlite3 mytable.db < backup.sql