SQLite Linux Tutorial for Beginners

This SQLite Linux tutorial is intended for beginners who wish to learn how to get started with SQLite database. SQLite is one of the world’s most widely-used Database programs. So, what is a Database, and what is SQLite?

In this tutorial you will learn:

• What a Database is, and what SQLite is
• Quick Installation
• How to create a Database using SQLite
• The basics of SQLite

From this, you will be able create your own simple databases, and quickly be able to put them to use, when and where needed. SQLite is used in many, many devices world-wide. One should consider this a basic part of computer learning.

Software Requirements and Conventions Used

Software Requirements and Linux Command Line Conventions

Category	Requirements, Conventions or Software Version Used
System	Any GNU/Linux distribution
Software	sqlite3
Other	Privileged access to your Linux system as root or via the sudo command.
Conventions	# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command $ – requires given linux commands to be executed as a regular non-privileged user

What is SQLite, and what is a Database?

SQLite is an Open-Source database program that uses a sub-set of the SQL database descriptor language. Databases are useful for collecting similar bundles of information in one place, a database. SQL is a well known open-standard. The database query language is then able to send queries to extract particular data from the database, or to select all data.

Quick Installation

So, let’s get started. This tutorial isn’t about installing programs. There are many other tutorials for that. But here’s a few pointers that may help someone new to Linux. For example, on Debian, one would issue the command:

$ apt install sqlite

which will likely install SQLite version 3. In Gentoo, it would be:

$ emerge sqlite

then portage will automatically load and compile the most current version. See your distro’s documentation. (synaptic, is another popular installer.)

For those that prefer to build from source-code, you can type in the following script, or download it:

$ FILE=sqlite-autoconf-3360000
$ wget --show-progress https://sqlite.org/2021/${FILE}.tar.gz
$ tar zxvf ${FILE}.tar.gz
$ cd ${FILE}
$ ./configure --prefix=/usr/local
$ make
$ sudo make install
$ cd -

Now, if you succeeded with the install, type the following command:

$ sqlite

You’ll likely get a message like:

bash: sqlite: command not found

If you press the <UP-ARROW>, then <ENTER>, you’ll likely see:

$ sqlite3
SQLite version 3.35.5 2021-04-19 18:32:05
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 

If that’s what you get, then we’re ready to rock! (Substitute appropriate expression….) If not, your Internet Search Engine is your friend.

What the sqlite> prompt means is that SQLite is working and ready to do whatever you tell it. Now, exit out, by typing either ^D or .exit.

For the examples we will assume an arbitrary person, in this case, a numismatist (coin-collector). The database will be called numismatist.db, and the schema table will be coins. Of course, you are welcome and encouraged to change things, as you work with the examples.

There are many ways to begin using SQLite. We are going to begin with a text file representation, and build from there. So, break out your favorite text editor and edit numismatist.sql to contain the following (if you don’t have an editor, yet, then keep reading, and you’ll soon be able to finish anyway):

/*
 * numismatist.sql	Beginner's database
 */

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

Create Table coins(
	denomination	TEXT,
	value		FLOAT,
	comments	TEXT
);

Insert into coins values(
	'Quarter',
	30.35,
	'Gift from Grandpa'
);

COMMIT;

Now, save this file, then make a copy for yourself, for when you wish to create a another new database, for example:

$ cp numismatist.sql skeleton.sql
$ chmod -w skeleton.sql

Now, anytime you want to start over, or create your own database, you’ll have a trivial example to modify and extend. (Or, put it in a make file under new: and let make do the work, for those familiar with make.)

Next, we have to tell SQLite to create our database using the commands in the file we just created. Then, we’ll have a real database.

$ cat numismatist.sql | sqlite3 numismatist.db

Now test that it worked using:

$ sqlite3 numismatist.db 'select * from coins'

You should see something very similar to the following:

Quarter|30.35|Gift from Grandpa

All of this can be done from inside SQLite, if, for example, you don’t have an editor, or just want to work directly in SQLite to learn its command-line behavior. Just type the following:

$ sqlite3 numismatist.db

To get help, either use man sqlite3 or at the sqlite> prompt, type .help. Then type the commands from the above numismatist.sql file above. Now, type:

sqlite> select * from coins;

Again, you should see:

Quarter|30.35|Gift from Grandpa

Now, we have a database. Let’s look at it more closely. The create table coins(... command created the record format for the database.

The insert into coins values(... actually populated one record. Now, you can just press the <UP-ARROW> and keep editing and adding records until your heart is content. List the records with, select * from coins; If you forget to add the ;, then you’ll get a ...> continuation prompt. Just type the ; there, and it will terminate the command.

How Databases are used

Now that we have the most basic building block, a database, we can explore what happens as this database grows in size. Databases are usually huge aren’t they? So how do we type a zillion lines into a database for testing? Nobody’s going to do that! Simple, here’s how (if you use bash), just use this script:

#!/bin/bash
function my_insert() {
      echo "insert into coins values('$1', $2, 'From Grandpa');"
}
i=1.25        # establish minimum coin value
for a in `seq 1 10000` ; do
      new_val=`calc "$i+$RANDOM/1000"`
      my_insert "Quarter-$a" $new_val >> big_add-list
      printf "Processing %i\n" $a # modulo 100 == 0 would be better
done

Now, merge the new commands into the existing database:

$ cat big_add-list | sqlite3 numismatist.db
$ sqlite3 numismatist.db 'select * from coins'
Quarter|30.35|Gift from Grandpa
Quarter-1|126.11|From Grandpa
Quarter-2|193.31|From Grandpa
		.
		.
		.
Quarter-9998|290.69|From Grandpa
Quarter-9999|211.44|From Grandpa
Quarter-10000|286.31|From Grandpa

And there you have it. A large database full of records. I encourage you to go in and manually explore this database. You can add, delete, and modify any of its records, until you are satisfied you understand how things work. And, if you break it, try to understand what you did to break it, then attempt to repair it, rather than just scrap it and restore the backup.

This is a valuable lesson. Now, anytime you wish to add records to a database, just place all of the insert commands into a file, then pipe it to SQLite.

The Basics of SQLite

But this isn’t all you can do with SQLite. You can write programs that include the SQLite library in them to allow you to create whatever you wish with it. As mentioned, you can use bash, or other, scripts to help create extensive and quite complex databases. You can explore the select command to query your database in very unique ways. This is just the beginning.

Here are a few of the very many thing you may wish to study next in your SQLite journey. There are many, so don’t consider this to be anything near a complete list:

• c/c++ program interface
• Python Interface
• SQLite Browser

I would be remiss if I didn’t explain a few of the most common SQLite commands. First, the dot commands used within the SQLite command itself, but see the manpage, and .help command as they will be much more complete:

sqlite> .help
.open		clears out memory
.open FILE	clears memory, then opens FILE
.dump		converts resident database to TEXT
.exit, .quit	terminates current session
.read FILE	reads database named FILE
.read '|cmd'	execute command 'cmd', then read its stream
.output	?FILE?	send output to FILE; reset to STDOUT
.shell, .system	execute an Operating System (shell) command
.save		writes database to storage
.show		display settings
.cd		change directory
.lint		identify possible schema errors

And lastly, here are the Types that may be included in any record (list is not exhaustive):

• NULL empty or null value
• INTEGER 1 to 8-byte integer
• FLOAT 8-byte IEEE floating point number
• TEXT UTF-8 Text string (currently the default for Linux)
• BLOB Data stored verbatim. Example: photo/jpeg, large text, binary

Conclusion

In this SQLite Linux Tutorial we have seen that creating a database can be a very simple task. When coupled with SQLite, any individual has the power to greatly simplify tasks commonly handled by a database. Here are some suggestions for the user to experiment with:

• Address and Telephone directory
• Inventory (coin collections, etc)
• TODO List
• Archive files: photos; letters; resumes; articles, such as this one
• Break down an entire book or codex into a searchable database. (This is the task the author took on for his first database experience. Several codices at once, as a matter of fact.)

We hope you enjoyed this article. Feel free to send any feedback you may have.