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
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.
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';
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.
Once you have identified the need of specific release
you can go ahead and download from sqlite releases
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.
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.
Note: If you find below error during building binaries
exec: tclsh: not found
then install Tcl
sudo apt-get install --reinstall tcl
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.
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.
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.