SQLite Tutorial

Ever since man started creating systems, there have always been databases corresponding to them. Fast forwarding to our present day where technology has evolved, and nearly everything has now been automated. While developing systems and applications, SQL has been the leading language which developers use to create and administer databases. For SQL to run properly, it needs an engine. This engine is then responsible for handling operations and transactions throughout the database.

What is SQLite?

SQLite is a fast and simple open source SQL engine. While this engine might have others confused with full RDBMS's such as Postgres and MySQL, SQLite is different and performs at its peak in certain conditions. SQLite is a new library that enforces a serverless, self-contained, no setup SQL database engine. Since it does not need configuration like other databases, it is easier to use and install. Unlike other databases, it is not a standalone process. You can link the database to your application so that the records it stores can be accessed either dynamically or statically.

There has always been a misconception about SQLite being only for development and testing. While it is a great tool for that job, it is not only limited to system testing. For instance, SQLite can handle a website receiving more than 100,000 visits per day on the lower side. The maximum size limit for SQLite is 140 Terabytes which is more than what most applications clock.

Why should you use SQLite?

• Since the system is serverless, it does not need an additional server process to function.
• There being no configuration, there is no need for setup or monitoring administrators.
• SQLite is compact as a full SQLite database can fit in one cross-platform disk file. Fully configured on a system, the whole database can be about 400KiB or about 250KiB when some features have been omitted.
• Supports most of SQL92 (SQL2) query language features thus quite familiar.

Since it is written in ANSI-C, the API is easy to use and quite straightforward.

---

INSTALLATION

Since SQLite's pride comes from its surprisingly simple configuration, the installation process is quite straightforward. In this tutorial, we shall focus more on Linux than other platforms. These days we find that SQLite is being shipped with almost all the versions of Linux operating system. So, before bothering to install it, you should check whether the installation has already taken place. To be sure, type this:

$ sqlite3

If properly installed, you should see the following result:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

If not, it means SQLite has not been installed on your system. To install, you can;

Go to the SQLite official page and download SQLite-autoconf-*.tar.gz from the section with the source codes. After that, open command line and run the following command;

$tar xvfz SQLite-autoconf-3071502.tar.gz
$cd SQLite-autoconf-3071502
$./configure --prefix = /usr/local
$make
$make install

You can also use the following method to install:

sudo apt-get update
sudo apt-get install sqlite3

Both these methods will do the same thing. You can confirm that installation is complete by running the first test.

---

Meta commands

Meta commands are mostly used for administrative operations such as examining databases and defining output formats. The unique thing about all these commands is that they always start with a dot (.). Here are some of the more common ones that come in handy over time.

Command	Description
.dump	Dump database usually SQL text format
.show	Displays the current settings for various parameters
.databases	Provides complete database names and files
.quit	Quits the SQLite3 program
.tables	Show a list of all current tables
.schema	Display schema of the table
.header	Hides or displays the output table header
.mode	Select mode for the output table

Standard Commands

When dealing with SQLite, there exist common commands used for various activities in the database. They are referred to as standard commands since they are usually the most frequently used ones. They are classified into three groups owing to their various functions across the scope.

Data definition language

The very first group are the commands responsible for storage structure and also methods of data access from the database. They are:

• CREATE
• DROP
• ALTER

Data manipulation language

These are the commands mostly used to manipulate data in the database. Data manipulation includes adding, removing and modifying the data. In this section, we have:

• INSERT
• UPDATE
• DELETE

Data query language

The last type of commands are those that enable the users to fetch certain data from the databases. Here we only have one:

• SELECT

It is important noting that these are not the only commands SQLite can support. However, since we are at the beginner stage, we shall only be exploring these for now.

---

Creating a database

When dealing with SQLite3, commands are used to create a new database. Unlike other RDBMS's, you need not have special privileges to do this. Remember that the database name should be unique. The following is the syntax for creating a database:

sqlite3 DatabaseName.db

A new database called linuxDB would be written as follows

$ sqlite3 linuxDB.db
SQLite version 3.21.0 2017-10-24 00:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
SQLite>

You can confirm the creation of the database by using the .databases command.

sqlite>.databases
seq name file
--- --------------- ----------------------
0 main /home/SQLite/linuxDB.db

Creating tables

Since tables are the skeletal of the database, it is essential to know how to create them. To create a table means you have to name the table, define the columns and the data type for each column. This is the syntax for creating a table.

CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
…
columnN datatype
);

In action, this is what a sample table called product_x will look like. The ID is the primary key. You should always remember to specify fields that cannot be null.

sqlite> CREATE TABLE product_x(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
UNITS INT NOT NULL,
PRICE INT,
DISCOUNT REAL
);

Drop table

This command is used when the developer wants to remove a table together with all its contents. You should always be careful when using this command since once the table is deleted, all subsequent data is lost forever. This is the syntax:

DROP TABLE database_name.table_name;

sqlite> DROP TABLE product_x;

Alter table

This command is used to edit the contents of a table without having to dump and reload the data. In SQLite, there are only two operations you can perform on a table with this command; renaming a table and adding or removing current columns.

This is the syntax for renaming an already existing table and adding a new column respectively;

ALTER TABLE database_name.table_name RENAME TO new_table_name;
ALTER TABLE database_name.table_name ADD COLUMN column_def… ;

For example, a table named product_x can be renamed to product_yz and we can add a new column to product_yz in the two lines of code below:

sqlite3> ALTER TABLE product_x
… > RENAME TO product_yz;
sqlite3> ALTER TABLE product_yz
… > ADD COLUMN manufacturer_name TEXT;

Insert query

The INSERT INTO command is used to add rows of data into a table inside the database. The syntax for this is quite direct:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,… valueN);

Column1, column2,… columnN are the names of the columns belonging to the table you want to insert data. It is important to specifically note the column name in SQLite when adding values to all columns in the table.

SELECT Query

The SELECT statement in SQLite is primarily used to fetch data from the SQLite database and return said data in the form of a results set. This is the syntax for using the SELECT statement;

SELECT column1, column2, columnN FROM table_name;

From the above syntax, column1, column2… are the respective fields in the table where you want to fetch values. In case you want to fetch all fields in that table, then you use the following syntax. The wildcard (*) basically means 'all'.

SELECT * FROM TABLE_NAME;

UPDATE Query

In a database, records need to change for one reason or another. Supposing a user wants to change their email address on your platform, this is exactly the command you need to make this process work. While using the UPDATE clause, we must also use the WHERE clause to update the selected rows. If not, you will find all the rows have been updated! That would be really bad. This is the syntax for performing this operation:

UPDATE table_name
SET column1 = value1, column2 = value2… ., columnN = valueN
WHERE [condition];

If you have an N number of conditions to be met, the AND or OR operators come in very handy. Example:

sqlite> UPDATE product_x
… > SET UNITS = 103 WHERE ID = 6;

The AND & OR operators

These are what could be called conjunctive operators. They are used to compile several conditions in order to shrink the selected data in an SQLite environment. These operators make it possible for a developer to make multiple comparisons of values using different operators on one SQLite statement.

The AND operator is unique since it allows the user to have multiple conditions in conjunction with the WHERE clause. When using this operator, the condition is regarded as true if all the conditions are met. This is the syntax for the AND operator.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]… AND [conditionN];

On the flip side of things, we have the OR operator which is also used together with the WHERE clause. Unlike the AND operator, the condition is true if one of the conditions has been met. The syntax is pretty simple.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]… OR [conditionN]

Sources and Additional Info

http://linuxgazette.net/109/chirico1.html
http://www.yolinux.com/TUTORIALS/SQLite.html
https://www.sitepoint.com/getting-started-sqlite3-basic-commands/
https://www.digitalocean.com/community/tutorials/how-and-when-to-use-sqlite
http://www.thegeekstuff.com/2012/09/sqlite-command-examples/?utm_source=feedburner