By Gareth Dwyer
Founder @ ritza.co
Note: This tutorial uses a deprecated version of Ubuntu. Click here to read an updated version written for Ubuntu 20.04.
Sqlite is a very simple and fast open source SQL engine. This tutorial will explain when it is optimal to use Sqlite, as opposed to a full-blown RDBMS such as Mysql or Postgres, as well as how to install it and basic usage examples covering CRUD - Create, Read, Update, and Delete.
Don’t be deceived into thinking that Sqlite is only for testing and development. For example, it works fine for websites receiving up to 100,000 hits a day-- and this is a conservative limit. The maximum size for a Sqlite database is 140 Terabytes (which should be enough, right?), and it can be substantially faster than a full-blown RDBMS. The full database and all other necessary data is stored in a normal file in the host’s file system, so no separate server process is needed (cutting out all need for slow inter-process communication).
Sqlite is focused on simplicity. Because it is completely internal, it is often significantly faster than alternatives. If you are looking for portability (with regards to both languages and platforms), simplicity, speed, and a small memory footprint–Sqlite is ideal. Its shortcomings are only apparent if you need high reading or writing concurrency: Sqlite can only support one writer at a time, and the normally high file system latency may be inconvenient if there is a need for many clients to access a Sqlite database simultaneously. A final possible disadvantage is that its syntax, though similar to other SQL systems, is unique. While it’s fairly trivial to move to another system, if you do ‘outgrow’ Sqlite, there will be some overhead involved in the transition.
For more information, there are some very good outlines on the pros and cons of Sqlite inside the official documentation.
The sqlite3 module is part of the standard Python library, so on a standard Ubuntu installation or any system with Python installed, no further installation is strictly necessary. To install the Sqlite command line interface on Ubuntu, use these commands:
sudo apt-get update sudo apt-get install sqlite3 libsqlite3-dev
If you need to compile it from source, then grab the latest autoconf version from the official SQLite website. At the time of writing:
wget http://sqlite.org/2013/sqlite-autoconf-3080100.tar.gz tar xvfz sqlite-autoconf-3080100.tar.gz cd sqlite-autoconf-3080100 ./configure make make install
(Notes for building from source: 1) Don’t do this on a standard Ubuntu installation, as you’ll probably get a “header and source version mismatch” error, due to conflict between an already installed version and the newly installed one. 2) If the
make command seems to expect further input, just be patient, as the source can take a while to compile).
To create a database, run the command:
Where ‘database’ is the name of your database. If the file
database.db already exists, Sqlite will open a connection to it; if it does not exist, it will be created. You should see output similar to:
SQLite version 3.8.1 2013-10-17 12:57:35 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
Now let’s create a table and insert some data. This table named “wines” has four columns: for an ID, the wine’s producer, the wine’s kind, and country of the wine’s origin. As it’s not Friday yet, we’ll insert only three wines into our database:
CREATE TABLE wines (id integer, producer varchar(30), kind varchar(20), country varchar(20)); INSERT INTO WINES VALUES (1, "Rooiberg", "Pinotage", "South Africa"); INSERT INTO WINES VALUES (2, "KWV", "Shiraz", "South Africa"); INSERT INTO WINES VALUES (3, "Marks & Spencer", "Pinot Noir", "France");
We’ve created the database, a table, and some entries. Now press
Ctrl + D to exit Sqlite and type the following (again substituting your database’s name for ‘database’), which will reconnect to the database we just created:
SELECT * FROM wines;
And you should see the entries we’ve just made:
1|Rooiberg|Pinotage|South Africa 2|KWV|Shiraz|South Africa 3|Marks & Spencer|Pinot Noir|France
Great. That’s it for creating and reading. Let’s do an update and delete:
UPDATE wines SET country="South Africa" WHERE country="France";
Which will update the database so all wines which are listed as coming from France will instead be listed as coming from South Africa. Check the result with:
SELECT * FROM wines;
And you should see:
1|Rooiberg|Pinotage|South Africa 2|KWV|Shiraz|South Africa 3|Marks & Spencer|Pinot Noir|South Africa
Now all our wines come from South Africa. Let’s drink the KWV in celebration, and delete it from our database:
DELETE FROM wines WHERE id=2; SELECT * FROM wines;
And we should see one fewer wine listed in our cellar:
1|Rooiberg|Pinotage|South Africa 3|Marks & Spencer|Pinot Noir|South Africa
And that covers all of the basic database operations. Before we finish, let’s try one more (slightly) less trivial example, which uses two tables and a basic join.
Exit from Sqlite with the command
Ctrl + D and reconnect to a new database with
We’ll be creating a very similar
wines table, but also a
countries table, which stores the country’s name and its current president. Let’s create the countries table first and insert South Africa and France into it with (note that you can copy-paste several lines of sqlite code at once):
CREATE TABLE countries (id integer, name varchar(30), president varchar(30)); INSERT INTO countries VALUES (1, "South Africa", "Jacob Zuma"); INSERT INTO countries VALUES(2, "France", "Francois Hollande");
And then we can recreate our wines table with:
CREATE TABLE wines (id integer, kind varchar(30), country_id integer); INSERT INTO wines VALUES (1, "Pinotage", 1); INSERT INTO wines VALUES (2, "Shiraz", 1); INSERT INTO wines VALUES (3, "Pinot Noir", 2);
Now let’s see what kinds of wine there are in South Africa with:
SELECT kind FROM wines JOIN countries ON country_id=countries.id WHERE countries.name="South Africa";
And you should see:
And that covers a basic Join. Notice that Sqlite does a lot for you. In the join statement above, it defaults to
INNER JOIN, although we just use the keyword
JOIN. Also we don’t have to specify
wines.country_id as it’s unambiguous. On the other hand, if we try the command:
SELECT kind FROM wines JOIN countries ON country_id=id WHERE country_id=1;
We’ll get the error message
Error: ambiguous column name: id. Which is fair enough as both of our tables have an
id column. But generally Sqlite is fairly forgiving. Its error messages tend to make it fairly trivial to locate and fix any issues, and this helps speed up the development process.
For further help with syntax, the official documentation is full of diagrams like this one, which can be helpful, but if you prefer concrete examples, here is a link to a tutorial with a nice overview of most of the join types.
Finally, Sqlite has wrappers and drivers in all the major languages, and can run on most systems. [A list of many of them can be found here](http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers" target="_blank).
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Click below to sign up and get $200 of credit to try our products over 60 days!
hi I am confused? Is a VPS different from droplet. if yes what is the pricing?
“Its shortcomings are only apparent if you need high reading or writing concurrency: Sqlite can only support one writer at a time…”
That’s incorrect. See [https://www.sqlite.org/faq.html]:
"Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
SQLite uses reader/writer locks to control access to the database. (Under Win95/98/ME which lacks support for reader/writer locks, a probabilistic simulation is used instead.) But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows, Microsoft’s documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true, sharing an SQLite database between two or more Windows machines might cause unexpected problems.
We are aware of no other embedded SQL database engine that supports as much concurrency as SQLite. SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.
However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine. "
Further, if Write-Ahead Logging (WAL) is enabled, concurrency is higher as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. [https://www.sqlite.org/wal.html]
In a vanilla CentOS 6.5 x64 Droplet it comes already installed: rpm -qa | grep sqlite sqlite-3.6.20-1.el6.x86_64
which sqlite3 /usr/bin/sqlite3
It would be better to write a new article in this case for CentOS.
Recommendation: Add the commands for all supported OS, it would be better. This points to Ubuntu, but not in the title.