SQLite Installation
Tags: database, serverless
Published: 2021-07-03 10:01:03


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](#Why should you consider using SQLite)

&nbsp;

## 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 



```sql
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](https://www.sqlite.org/src/info/884b4b7e502b4e99)  worked fine and on server I ran into the error with [sqlite version 3.22.0](https://www.sqlite.org/src/info/0c55d179733b46d8)   `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.

&nbsp;

## Download

- Once you have identified the need of specific release

- you can go ahead and download from [sqlite releases](https://www.sqlite.org/src/timeline?t=release) 

- scroll to your version and click on hash-id named as check-in
 ![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9g3ro3q9itfj76k8ob30.png)

- You will be re-directed to page that contains details of that specific release
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tih218car42jvjfo66w1.png)

  

- 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.

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



&nbsp;

## Installation



Use below commands to build the binary

```bash
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 

```bsh
sudo apt-get install --reinstall tcl
```


&nbsp;



## 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

```python
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/`

```bash
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.
&nbsp;





## Why should you consider using SQLite

**[SQLite](https://www.sqlite.org/index.html)** 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.