Relational databases have been in use for a long time. They became popular thanks to management systems that implement the relational model extremely well, which has proven to be a great way to work with data [especially for mission-critical applications]. In this tutorial we are going to understand how to work with a very powerful, embedded relational database management system called SQLite3.
SQLite is an amazing library that gets embedded inside the application that makes use of. As a self-contained, file-based database, SQLite offers an amazing set of tools to handle all sorts of data with much less constraint and ease compared to hosted, process based (server) relational databases. When an application uses SQLite, the integration works with functional and direct calls made to a file holding the data.
SQLite’s Supported Data Types
- NULL:
NULL value.
- INTEGER:
Signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
- REAL:
Floating point value, stored as an 8-byte IEEE floating point number.
- TEXT:
Text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
- BLOB:
A blob of data, stored exactly as it was input.
Pros of SQLite
- File based:
The entire database consists of a single file on the disk, which makes it extremely portable.
- Standards-aware:
Although it might appear like a “simple” DB implementation, SQLite uses SQL. It has some features omitted (RIGHT OUTER JOIN or FOR EACH STATEMENT), however, some additional ones are baked in.
- Great for developing and even testing:
During the development phase of most applications, for a majority of people it is extremely likely to need a solution that can scale for concurrency. SQLite, with its rich feature base, can offer more than what is needed for development with the simplicity of working with a single file and a linked C based library.
Cons of SQLite
- No user management:
Advanced databases come with the support for users, i.e. managed connections with set access privileges to the database and tables. Given the purpose and nature of SQLite (no higher-levels of multi-client concurrency), this feature does not exist.
- Lack of possibility to tinker with for additional performance:
Again by design, SQLite is not possible to tinker with to obtain a great deal of additional performance. The library is simple to tune and simple to use. Since it is not complicated, it is technically not possible to make it more performant than it already, amazingly is.
Installation
To install it on Mac OSX, run the following:
brew install sqlite3
sudo apt-get install sqlite3 libsqlite3-dev
To install it on other platforms, follow their official instructions.
After it is installed we can start the session. Open the Terminal/Command Line and enter the command sqlite3
Meta Commands
Meta Commands are used to define output format for tables, examine databases and for other administrative operations. They always start with a dot. Even .help is a meta command. You can go through the list. Here are some that will frequently come in handy:
.show Displays current settings for various parameters
.databases Provides database names and files
.quit Quit sqlite3 program
.tables Show current tables
.schema Display schema of table
.header Display or hide the output table header
.mode Select mode for the output table
.dump Dump database in SQL text format
Standard Commands
Standard SQL commands are issued to operate on a database. Standard Commands can be classified into three groups:
- Data Definition Language: It provides the storage structure and methods to access data from the database system.
- Data Manipulation Language: It enables users to manipulate (add/modify/delete) data.
- Data Query Language: It enables users to retrieve required data from the database.
Note: SQLite understands many other standard commands, a list of which can be read here.
CREATE TABLE
Let’s create the first table and name it location.
CREATE TABLE location (
location_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
current_location TEXT NOT NULL,
city TEXT NOT NULL,
postcode TEXT NULL,
country TEXT NOT NULL );
NOT NULL makes sure that the particular value is not left empty in a record. PRIMARY KEY3 and AUTOINCREMENT4 elaborate the location_id attribute..tables. It will display the table name locationINSERT ROWS
Suppose a user has entered a location with the details:
Current location : Big Ben
City : London
PostCode : SW1A0AA
Country : England
To insert it, we use the INSERT command.
INSERTINTO location ( current_location, city, postcode, country )
VALUES( 'Big Ben', 'London','SW1A0AA', 'England');
NOT NULL. It is not needed because it is an AUTOINCREMENT field.SELECT
We have learned to insert data into the table. To retrieve data we use the SELECT command.
SELECT post_id, name, email, website_url, comment
FROM comments;
The above statement can also be written as:
SELECT*FROMcomments;.headers ON. To display rows in column style, enter .mode column. Enter the SELECT statement again.UPDATE
Suppose postcode for ‘Big Ben’ was wrong and we need to change it. We need to update the row. Issue the following statement:
UPDATElocation
SET postcode = 'SW1A0BB'
WHERE current_location= 'Big BEn';
DELETE
To DELETE row(s) from the table we can use the WHERE condition, just like in UPDATE.
Suppose we need to delete a row with location_id7. We can enter the command:
DELETE FROM location
WHERE location_id = 7;
ALTER
New columns can be added to a table using ALTER. We need to add a column new_col to our table. The query below accomplishes the task:
ALTER TABLE location
ADD COLUMN new_col TEXT;
location table to location_history.ALTER TABLE location
RENAME TO location_history;
DROP
Dropping a table means deleting the entire table. The location_history table can be deleted with the following query:
DROP TABLE location_history;
Conclusion
SQLite3 offers many advantages over other relational databases. You can read of SQLite distinctive features here. Most PHP Frameworks and many other web frameworks including Django and Ruby on Rails have SQLite3 as their default database. Being lightweight makes it preferable for local storage in web browsers. It is also employed as client storage for many Operating Systems. It is one of the most widely deployed database engines.
In the tutorial we interacted with an SQLite database system. There are GUI applications to do the same, without having to learn commands:
- DB Browser for SQLite
- SQLite Studio
- SQL Fiddle practice SQL online
