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
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.
The biggest, and most important, reason is simplicity. When I realized that I actually would have to use a database for an application that I was writing, finding a suitable candidate wasn’t easy. Most databases are complex systems, much like a server. And that’s what many are called, database servers. Such things often require a team of professionals to setup, manage, and maintain. But, SQLite makes it trivial to create, manage, and maintain your own database, all by yourself. But, another good reason, as I stated above, is that SQLite is one of the most widely used database programs in the world–a tool very much worthy of our study.
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.