Getting started with SQLite


Introduction :

SQLite is an open source database which is light weight compared to other databases. It is really efficient in terms of performance as the entire database is packaged and stored as a single file within our file system. It has most of the standard SQL features like transactions, support for triggers, views, primary and foreign key constraints. It is widely used and most of the Android applications use SQLite as the database. The entire source code for the database is publicly available.

Features :

Some of the important features of SQLite are :

  1. Support all ACID properties
  2. No configuration needed nor no external dependencies needed
  3. Database is light weight and performance is better than standard databases
  4. Database is available as a single file on a disk and is platform independent.
  5. Its open source and even source implement in C is publicly available
  6. Simple and easy to use and supports large databases

Download and getting started :

To start with SQLite, we need to download the latest version of the SQLite software. The current stable version of SQLite is 3.7.9 which can be downloaded at the following location : http://www.sqlite.org/download.html. Once you download the database and unzip it, you can start accessing the database using the command line shell as seen below.

sqlite
Once you enter sqlite3.exe, you can see the details about SQLite and you are all set to use it.

sqlite database
Creating a database :

By default, there will be Main database and all new tables created will go to Main databases.

Type.databases on SQLite command shell, to get list of databases.

You can type query as seen below to create a table which will go into main database by default.

sqlite3.gif

You can insert records and also view them as seen below :

sqlite4.gif
SQLite uses dynamic typing. Even though we define types for columns, but still any type of data can be stored in any column. Types are resolved based on data and there is no type safety.

Backup\ Loading of Database :

Use .backup command as seen below to take a backup of Main db.

sqlite5.gif

This is brief overview of SQLite and this explains how easy it is to work with SQLite. For more details, go through SQLiteHome Page.