SQLite 3 Beyond the Basics

In the introductory post to SQLite 3, we covered some basic commands, database and table creation, and more. In this one, we’ll go into more depth – exploring both familiar features from other databases and features that make SQLite stand out.

It is assumed you went through the introductory post before continuing with this one, or that you are comfortable with the basics of SQLite 3.

SELECT Query

SELECT is a standard SQL command and belongs in the Data Query Language (or DQL). It lets you perform queries on a database and fetch required records. The results can be further filtered using various clauses provided by SQLite.

Open up the terminal and type: sqlite3 Library.db

This would create a database named Library.db in the current directory. Now, let us create a table that stores information about library users. A library user must have a name, an ID provided by the library which is unique, a user’s age and their date of joining. The following SQL query will create this table:

CREATE TABLE Users ( 
  SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  Name TEXT NOT NULL,
  Id TEXT NOT NULL UNIQUE,
  Age INTEGER NOT NULL,
  DOJ TEXT NOT NULL );

SerialNo is the Primary Key.

Remember: There can be any number of UNIQUE columns in a table but there must only be one PRIMARY KEY.

Insert a record into the table using the following query:

INSERT INTO Users ( Name, Id, Age, DOJ)
VALUES ( 'Shivam', 'U123', 19, '2015-01-31' );

Note: SQLite does not have a different datatype just for dates. To store Dates in SQLite you must enter them in YYYY-MM-DD format or they will be treated as strings.

You can insert more records using the above command, but it would be tedious for a large set of data. We will use the .read meta-command. It executes SQL queries from the specified file. Save the following to a file called newusers.sql:

BEGIN TRANSACTION;

CREATE TABLE NewUsers (
    SerialNo INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Id TEXT NOT NULL UNIQUE,
    Age INTEGER NOT NULL,
    DOJ TEXT NOT NULL );

INSERT INTO NewUsers VALUES (1,  'Abraham',     'U123', 19, '2015-02-28');
INSERT INTO NewUsers VALUES (2,  'Michael',     'U124', 23, '2014-12-14');
INSERT INTO NewUsers VALUES (3,  'Steve',       'U125', 35, '2012-11-20');
INSERT INTO NewUsers VALUES (4,  'Ben',         'U126', 15, '2013-05-12');
INSERT INTO NewUsers VALUES (5,  'Alice',       'U127', 17, '2013-03-01');
INSERT INTO NewUsers VALUES (6,  'Christopher', 'U128', 19, '2014-05-03');
INSERT INTO NewUsers VALUES (7,  'Elena',       'U129', 27, '2011-07-14');
INSERT INTO NewUsers VALUES (8,  'Daniel',      'U130', 43, '2010-08-14');
INSERT INTO NewUsers VALUES (9,  'Candice',     'U131', 18, '2014-02-05');
INSERT INTO NewUsers VALUES (10, 'Billy',       'U132', 20, '2015-01-24');
INSERT INTO NewUsers VALUES (11, 'Michael',     'U133', 19, '2013-02-12');
INSERT INTO NewUsers VALUES (12, 'Alice',       'U134', 25, '2010-07-14');
INSERT INTO NewUsers VALUES (13, 'Richard',     'U135', 40, '2011-12-14');
INSERT INTO NewUsers VALUES (14, 'Lester',      'U136', 18, '2014-09-05');
INSERT INTO NewUsers VALUES (15, 'Malvo',       'U137', 21, '2015-01-04');
INSERT INTO NewUsers VALUES (16, 'Alice',       'U138', 19, '2013-02-02');
INSERT INTO NewUsers VALUES (17, 'Drako',       'U139', 24, '2010-12-02');

COMMIT;

and read it from the sqlite command line using:

.read newusers.sql

If everything goes well, you will have a NewUsers table with new records. Use the SELECT query to display them.

SELECT Id, Name, Age, DOJ
FROM NewUsers;

By default, column headers are not displayed and display mode is set to line. Switch on the header display by entering .header ON and change mode style to column using .mode column. Enter the SELECT query again.

Tip: Use the .show command to display values of various settings. You can also specify the column width manually using the .width1 meta-command.

You can change the number of columns to be displayed and specify another name (alias) for a header. For example:

SELECT Id AS 'User ID', Name, DOJ AS 'Date of Joining'
FROM NewUsers;

You can use the .schema command to display all the SQL statements that were used to create the database. You can optionally provide a table name to it, to display the schema of the particular table.

WHERE Clause

The WHERE clause specifies a condition to fetch the results. For instance, to fetch user IDs and names with age 20 or above we can enter the following query.

SELECT Id, Name FROM NewUsers
WHERE Age >= 20;

Operators

SQLite3 provides a number of operators to optimize the WHERE clause. Binary operators in the descending order of their precedence are:

||
*   /   %
+   -
<<  >>  &   |
<   <=  >   >=
=   ==  !=  <>  IS  IS NOT  IN  LIKE  GLOB  MATCH  REGEXP
AND   
OR

SQLite3 supports four unary prefix operators, - + ~ NOT.

Besides these, there are some other operators like BETWEEN and EXISTS.

BETWEEN

Provides a range of values (minimum to maximum) to operate on. For example, to select users that have a date of joining between 12-30-2011 and 12-30-2014 and with age between 17 and 27 we can enter the following query:

SELECT * FROM NewUsers
WHERE DOJ BETWEEN '2011-12-30' AND '2014-12-30' 
AND Age BETWEEN 17 AND 27;

AND, OR and NOT

The AND operator provides an intersection of two conditions, where as the OR operator provides a union of the two conditions. For example, to select users with Age greater than 20 and with SerialNo greater than 5, enter the query:

SELECT * FROM NewUsers
WHERE Age > 20 AND SerialNo > 5;

Now, to select users with Age > 20 or users with SerialNo > 5, enter:

SELECT * FROM NewUsers
WHERE Age > 20 OR SerialNo > 5;

The NOT operator is simply the negation of the corresponding condition. Users that do not have Ages between 20 and 30 can be extracted using:

SELECT * FROM NewUsers
WHERE Age NOT BETWEEN 20 AND 30;

IS and IS NOT

IS and IS NOT work same as = (equals to) and != (not equals to) respectively.

IN and NOT IN

The IN operator searches for a value in a list of literals. For example, to search for users with library IDs U124, U127 and U129:

SELECT * FROM NewUsers
WHERE Id IN ('U124', 'U127', 'U129');

LIKE and GLOB

LIKE does a pattern matching comparison using wildcards % and _. % wildcard matches zero or more characters and _ matches any single character. To select users with names beginning with ‘B’, ending with ‘y’ and the third last character as ‘l’ we may enter:

SELECT * FROM NewUsers
WHERE Name LIKE "B%l_y";

Note: LIKE is case insensitive, so “B%” and “b%” work the same when used with LIKE.

GLOB is similar to LIKE except it is case sensitive and uses Unix File Globbing syntax.

EXISTS

The EXISTS operator evaluates either to 0 or to 1. If the query to the right of EXISTS returns no rows then EXISTS returns zero and if the query returns one or more rows then EXISTS returns one.

SELECT EXISTS ( SELECT * FROM NewUsers WHERE Age < 10 );

This would return a zero since there are no users with Age less than 10.

|| Operator

|| operator concatenates the specified strings. The below query would give the result: “sitepoint.com”.

SELECT 'sitepoint' || '.' || 'com';

ORDER BY Clause

As it implies, the ORDER BY clause sorts the records in either ascending or descending order. It operates on a particular column and accepts ASC for ascending order and DESC for descending order.

For instance, to sort all user records in ascending order of Name.

SELECT * FROM NewUsers
ORDER BY Name ASC;

To sort user records in descending order of Age and with Date of Joining after 2013-05-12, you must enter the following query:

SELECT * FROM NewUsers
WHERE DOJ > '2013-05-12' ORDER BY Age DESC;

LIMIT Clause

LIMIT clause limits the number of results of a query to the specified number. It comes with an optional OFFSET flag that declares the number of records to be skipped. For example to select the sixth, seventh and eighth record we need to put a limit of three and an offset of 5.

SELECT * FROM NewUsers
LIMIT 3 OFFSET 5;

Note: The above query can also be written as:
SELECT * FROM NewUsers LIMIT 5, 3;

GROUP BY Clause

GROUP BY combines multiple records and groups them using one or more columns. For example, to count the number of users with a particular name we may enter the query:

SELECT Name, COUNT(Name) FROM NewUsers
GROUP BY Name;

HAVING Clause

If we need to specify a condition on groups we cannot use the WHERE clause because the WHERE clause specifies conditions on columns. To provide conditions on groups the HAVING clause is used. In the above example let us extract users with same names.

SELECT Name, COUNT(Name) FROM NewUsers
GROUP BY Name HAVING COUNT(Name) > 1;

DISTINCT

The DISTINCT keyword retrieves unique records. To fetch unique names enter the following query:

SELECT DISTINCT Name
FROM NewUsers ORDER BY Name;

Attaching and Detaching Databases

When operating with multiple databases, an alias can be attached to each database. The ATTACH DATABASE command attaches a name to a database. Enter .databases. You will get a list of database names and their respective files. The main database is the primary database. Besides main, there is a temporary, hidden database temp. Both of these can neither be attached nor be detached from an alias. Let us attach Library.db to an alias LIB.

ATTACH DATABASE 'Library.db' AS 'LIB';

Consider another database Students.db. Attach it to STU.

ATTACH DATABASE 'Students.db' AS 'STU';

Enter .databases. You will get something like this:

The seq column does not have the record 1 between 0 and 2. It is actually the hidden database temp.

Now you can execute queries on both databases in the same sqlite session. To select top ten records from LIB we may enter the following:

SELECT * FROM LIB.NewUsers
WHERE 1 LIMIT 10;

Suppose STU contains a table Students with the student names. We need to find out details of library users that are students (consider names to be a column in both Students and NewUsers for this example). We may enter the following query:

SELECT LIB.NewUsers.Id, LIB.NewUsers.Name, LIB.NewUsers.Age
FROM LIB.NewUsers
INNER JOIN STU.Students
ON STU.Students.Name = LIB.NewUsers.Name;

To detach Library.db from its alias LIB enter the following:

DETACH DATABASE 'LIB';

Transactions

Once the database has been created we can perform queries to create and modify records in tables. One or more of these queries that change the previous state of the database give rise to a Transaction. So a transaction can include inserting, updating and deleting records. It also includes creation, modification and deletion of tables. A transaction can have several smaller executions inside – consider a bank’s database. A user withdraws money, their account status is updated, the bank’s status is updated, the log database gets a new record, etc – all this needs to happen flawlessly, otherwise the money will be missing in one table, creating a problem for everyone.

When you perform queries in the form of a transaction, the changes are not added directly to the database. You have to commit them yourself. This helps in case some error occurs or the system fails during a transaction. In such a situation, the database will not change its state and will not be left with partial changes.

SQLite is a Transactional database i.e. it is ACID compliant. ACID is an acronym for Atomicity, Consistency, Isolation and Durability. These terms are discussed below.

Atomicity

Atomicity makes transactions indivisible. Either changes are committed all at once or the entire transaction fails.

Consistency

Every transaction is consistent i.e. the final state reached after a transaction will also be a valid state.

Isolation

Transactions are separated from one another in such a way that during their concurrent execution the failure of one will not affect the execution of others.

Durability

Once a transaction has been committed successfully, the changes in the database will be permanent and immune to any system failures.

SQLite offers commands to control transactions. The following table lists the control commands.

Command	Description
BEGIN TRANSACTION	Marks the beginning of a transaction.
ROLLBACK	Erases current transaction. If it is used with the TO keyword, it rollbacks to a particular savepoint.
COMMIT	Commits a transaction to database. It is another alias for END TRANSACTION.

Besides these there are two more commands: SAVEPOINT and RELEASE. SAVEPOINT is similar to BEGIN except that you must provide a name to the beginning transaction, thus allowing you to nest transactions. RELEASE removes a predefined savepoint and merges a named transaction into its parent transaction.

Let us continue with Library.db. Start a transaction by entering: BEGIN TRANSACTION;. Insert some valid records in the table:

INSERT INTO NewUsers VALUES (18, 'Frank', 'U140', 45, '2009-11-02');
INSERT INTO NewUsers VALUES (19, 'Claire', 'U141', 43, '2009-11-01');

To save these changes you may enter COMMIT;. Or if you want to discard them enter ROLLBACK;.

SAVEPOINT statement starts the transaction with a name. Create a savepoint named SPT1 and add a new record.

SAVEPOINT SPT1;
INSERT INTO NewUsers VALUES (20, 'Ricky', 'U142', 26, '2011-07-22');

Create another savepoint SPT2 and add another record.

SAVEPOINT SPT2;
INSERT INTO NewUsers VALUES (21, 'Sundar', 'U143', 31, '2011-09-21');

Both these savepoints are nested (SPT2 in SPT1). You can commit both of them by entering:

RELEASE SAVEPOINT SPT1;

When RELEASE is applied on the outermost savepoint of the nested savepoints, it commits all the savepoints starting with the most recent one. Thus SPT2 and SPT1 both are committed.

RELEASE SAVEPOINT SPT2; will release SPT2. But since it is child to the parent transaction SPT1, its fate depends on it. If SPT1 is rolled back so is SPT2.

To discard both of them, you can rollback to the outermost savepoint i.e. SPT1.

ROLLBACK TO SAVEPOINT SPT1;

Autocommit mode

If you execute queries without explicitly defining a transaction, each query is wrapped in its own transaction and then executed. You can think of it as each query with a particular BEGIN TRANSACTION and a COMMIT statement. This is the autocommit mode. Autocommit mode is switched on by default.

If you explicitly start a transaction, the autocommit mode is turned off. After a commit (or rollback) to the same transaction the autocommit mode is again turned on.

Exporting Databases

At the beginning of the tutorial we imported a table to the database using the .read command. To export a database or table we use the .dump command. .dump displays the database in SQL text format. You can use the redirection operator in the command line to save the dump to a file.

sqlite3 Library.db '.dump' > LibBackup.sql

If a file LibBackup.sql already exists, it is overwritten. The above command will dump the entire database to the file. You can optionally provide a table name as an argument to .dump. It will export that particular table. To dump NewUsers enter:

sqlite3 Library.db '.dump NewUsers' > LibBackup.sql

Output Results

By default the output generated through a query is written on the standard output. SQLite provides the .output command to write the output of one or more queries to a file.

Enter .output output.txt and a few queries. The results will be written to output.txt stored in the current working directory. You can exit the sqlite session and view the file.

.output stdout displays output to the standard output.

Conclusion

We’ve learned about SQLite operators and expressions. The tutorial also includes transactions and a brief discussion about SQLite’s transactional nature. I hope you found it useful; if you have any questions feel free to ask them in the comments and I will do my best to answer them.

Frequently Asked Questions (FAQs) about SQLite 3 Beyond Basics