Getting Started with SQLite3 – Basic CommandsBy Shivam Mamgain
SQLite is a C library that implements an SQL database engine. It is a Relational Database Management System (or RDBMS). Most of the SQL databases work with the client/server model. Take MySQL for an example. To enter and receive data from a MySQL database you need to send a request to the MySQL server, which on reception will provide you with the appropriate response. Contrary to MySQL, SQLite databases are operated directly from the disk. There is no need to create requests to the server.
We will operate on our database using the sqlite31 Command Line Interface(CLI). We will work under the Linux Platform. sqlite3 CLI for MAC OS and Windows are just the same, but for the sake of consistency, it’s recommended you install Homestead Improved – a lightweight VM runnable in 5 minutes that will keep your computer clean of extra unneeded software.
To install it on Homestead Improved (or any Debian based OS like Ubuntu), run the following:
sudo apt-get install sqlite3 libsqlite3-dev
This will install sqlite3. 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. You must see something of this kind :
The second line tells you to enter
.help for instructions. There is a prompt waiting for you to type a command. So go ahead and enter
.help. This will give you a list of Meta Commands and their descriptions.
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|
Let us go through the standard commands in sqlite3. Meta commands are issued to examine a database. 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. Since the tutorial is a beginner level introduction, we will cover only the above mentioned commands.
SQLite databases are cross-platform portable files. They can be stored on various storage devices and can be transferred across different computers.
We will learn to work with sqlite3 with our Comment Section database. Almost every website today has a comment section. To post a comment, the user must enter the following details:
Of the four of these, only website URL is optional. We must also define a column that numbers the comments. Let’s call it post_id.
We can define the datatypes of the columns (attributes) as follows:
You can see the documentation for different Datatypes and Storage Classes provided in SQLite3.
SQLite3 uses Manifest Typing2. Most other database engines including MySQL use Static Typing.
Now let us create a database. If you are still in the sqlite3 program, quit it by entering
.quit at the sqlite prompt. Then, issue the command:
This will create a database file comment_section.db in the current working directory.
Note: If no filename follows the command, sqlite3 automatically creates a temporary database. It is what happened when we entered the single command sqlite3 for the first time.
To store the comments we must define a table. Let us name it
comments. To create it, enter the statement:
CREATE TABLE comments ( post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL );
To check if table has been created, issue the meta command
.tables. It will display the table name
Note: To get the schema of the table enter
Now that the table has been created, we need to enter data.
Suppose a user has entered a comment with the details:
Name : Shivam Mamgain Email : email@example.com Website : shivammg.blogspot.com Comment : Great tutorial for beginners.
To insert it, we use the INSERT command.
INSERT INTO comments ( name, email, website_url, comment ) VALUES ( 'Shivam Mamgain', 'firstname.lastname@example.org', 'shivammg.blogspot.com', 'Great tutorial for beginners.' );
I have not provided the value for post_id, even though it is defined as
NOT NULL. The thing is, it is not needed because it is an
Enter some more rows in the table to practice.
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 * FROM comments;
This will retrieve all rows from the table. The resultant table might look obfuscated without headers and proper separation between columns. To overcome this, we must alter some parameters.
To display column attributes, enter
To display rows in column style, enter
Enter the SELECT statement again.
Note: Select a mode of display that suits you. Enter
.help and search for
.mode. You will be shown the various values
.mode can have.
Suppose email for ‘Shivam Mamgain’ was changed to ‘email@example.com’ . We need to update the row. Issue the following statement:
UPDATE comments SET email = 'firstname.lastname@example.org' WHERE name = 'Shivam Mamgain';
This will change the email attribute for the name ‘Shivam Mamgain’ to ‘email@example.com’.
Note: The name attribute is not unique, so the above statement may affect more than one row. For every name = ‘Shivam Mamgain’ the email will be set to ‘firstname.lastname@example.org’. To update a particular row use the post_id attribute. It is defined as a
PRIMARY KEY and will always be unique.
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 post_id 9. We can enter the command:
DELETE FROM comments WHERE post_id = 9;
We are in a need to delete all comments that are posted by ‘Bart Simpson’ and ‘Homer Simpson’. We can run the following query:
DELETE FROM comments WHERE name = 'Bart Simpson' OR name = 'Homer Simpson';
New columns can be added to a table using ALTER. On the web, most people don’t necessarily use their real name. They generally work under an alias, a username. We need to add a column username to our table. The query below accomplishes the task:
ALTER TABLE comments ADD COLUMN username TEXT;
This will create a column username in
comments and will set its datatype to TEXT. The value of username for already inserted rows will be set to NULL.
The ALTER command is also used to rename tables. Let us change the
comments table to
ALTER TABLE comments RENAME TO Coms;
Dropping a table means deleting the entire table. The
Coms table can be deleted with the following query:
DROP TABLE Coms;
SQLite3 offers many advantages over other relational databases. Some of its distinctive features can be read here. Most PHP Frameworks and many other web frameworks including Django, Ruby on Rails and web2py 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, including Android and Windows Phone 8. 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. Two such applications are the DB Browser for SQLite and SQLiteStudio. You can also practice SQL online at SQL Fiddle.
This guide has covered the basics to get you started with SQLite3. I hope it will tempt you to go further. You can easily integrate it with PHP using the common PDO class.
- sqlite version 3 ↩
- Manifest Typing releases many restrictions on the type of value that can be entered for a particular field. This allows you to enter any value of any datatype into a column, irrespective of the declared type of the column (except for
INTEGER PRIMARY KEY). You are not even required to provide the maximum size for datatypes. After the user inputs data, SQLite determines the size it would require on the disk. To get a deeper insight refer to Datatypes in SQLite3. ↩
PRIMARY KEYcan uniquely define a particular row. Such an attribute must always be defined as
NOT NULL. ↩
AUTOINCREMENTautomatically sets the corresponding attribute of the currently inserted record to a number that is one plus the value of
AUTOINCREMENTdefined attributes must necessarily be