I have been working on a project, and there was a need for serverless mini database. I decided to go with sqlite, it has its own advantages and limitations

Challenges

Many times you will run into error those are SQLite version/release specific, they could be

• Features your are looking is available only in new release or specific release
• Your application use old release.
• You may want to try some deprecated feature for some experiment.

Example

I was struggling with upsert query

INSERT INTO upsert_table (
                             folder,
                             filename,
                             count
                         )
                         VALUES (
                             '2021-01',
                             'abc.json',
                             1
                         )
                         ON CONFLICT (
                             filename
                         )
                         DO UPDATE SET count = count + 1 WHERE filename = 'abc.json';

On local system sqlite version 3.28.0 worked fine and on server I ran into the error with sqlite version 3.22.0 Error: near "ON CONFLICT": syntax error

After some googling i found ON CONFLICT is supported on versions > 3.23.0

The Take away is you may come across many challenges that are release specific, and hence it becomes important to learn to download, compile and build binaries from sources.

Download

• Once you have identified the need of specific release

• you can go ahead and download from sqlite releases

• scroll to your version and click on hash-id named as check-in
  

• You will be re-directed to page that contains details of that specific release
  

• Instead of downloading by clicking on it we will copy the link of the tar.gz and download using wget, and same command can be used in server as well.
  

  wget https://www.sqlite.org/src/tarball/884b4b7e/SQLite-884b4b7e.tar.gz

Installation

Use below commands to build the binary

cd                                  ;#  Takes you to Home directory
tar xzf SQLite-884b4b7e.tar.gz      ;#  Unpack the source tree into "sqlite"
mkdir bld                           ;#  Build will occur in a sibling directory
cd bld                              ;#  Change to the build directory
../SQLite-884b4b7e/configure        ;#  Run the configure script
make                                ;#  Run the makefile.
make sqlite3.c                      ;#  Build the "amalgamation" source file
make test                           ;#  Run some tests (requires Tcl)

You can also find this steps in README.md in downloaded folder SQLite-884b4b7e/

After Installation append binary path to Linux $PATH variable to access sqlite from anywhere in your system, If you don't run below command you will have to run sqlite from folder where its binary is stored.

export PATH=$HOME/bld:$PATH

Note: If you find below error during building binaries

exec:  tclsh:  not found

then install Tcl

sudo apt-get install --reinstall tcl

Upgrading python's SQLite

Now you have upgraded sqlite version on your OS. But you won't be able to access the new version from python, Because Python can't use the sqlite3 binary directly. It always uses a module which is linked against the sqlite3 shared library.

So when you print sqlite version from python , it will give you old version

import sqlite3
sqlite3.sqlite_version #sqlite_version - sqlite version
sqlite3.version # version - pysqlite version

To make python use new sqlite version, we have to update the .so file in linux.
Go to directory where you had build the sqlite from source, You will find libsqlite3.so.0.8.6 in lib folder.
Move libsqlite3.so.0.8.6 to /usr/lib/x86_64-linux-gnu/

cd $HOME/bld/.libs
sudo mv libsqlite3.so.0.8.6 /usr/lib/x86_64-linux-gnu/

Now you are ready to use new sqlite version from python.
 

Why should you consider using SQLite

SQLite is a lightweight, small and self-contained RDBMS in a C library. Popular databases like MySql, PostgreSQL, etc. works in the client-server model and they have a dedicated process running and controlling all the aspects of database operation.

But SQLite has no process running and has no client-server model. SQLite DB is simply an file with .sqlite3/.sqlite/.db extension. Every programming language has a library to support SQLite.

You can find SQLite being used in

• Web browsers(Chrome, Safari, Firefox).
• MP3 players, set-top boxes, and electronic gadgets.
• Internet of Things (IoT).
• Android, Mac, Windows, iOS, and iPhone devices.

There are lot more areas where SQLite is used. Every smartphone in the world has hundreds of SQLite database files and there are over one trillion databases in active use. That’s quite huge in numbers.