On SQLite

First published: 2026-08-27

Choosing a Database

I’m currently working on porting a Python web-application to Java. It is a stock-price predictor web-application that uses an adaptation of the Monte-Carlo method to generate a future price prediction. The python application never had a database to store data in a searchable manner, nor could it store predictions made for verification in the future.

For this reason, I wanted my Java port to use a database to store, at the very least:

1. Stock history data.
2. Predictions, should the user choose to.
3. User details for a user-registration.

I wanted a few modern features though – although I did not want a full size database at this point.

The features I’d like the database to have are:

• JSON data support – it should natively support JSON data, preferably with the ability to store, index and search JSON efficiently.
• Lightweight – preferably embeddable in the application, so that I do not have to run a separate database server and connect to it.
• Fast – performance is important as I would expect stock history to build up pretty quickly over time.

The choices

After some research, I narrowed it down to the following choices:

1. SQLite – per their website, they are a

   …small, fast, self-contained, high-reliability, full-featured, SQL database engine.

   which makes it ideal for my needs.

2. MariaDB – this used to be MySQL, but they split over ownership concerns when Oracle took over Sun Microsystems. While light-weight, this is a heavier option than SQLite.

3. PostgreSQL – this is a full-featured, enterprise scale, database server. While I certainly want to get familiar with Postgres, I did not think an enterprise grade database is required for my project.

All three offer JSON support natively. However, for my currently limited needs, SQLite seemed the closest fit.

Installation

So having settled on SQLite, the next step was installing it. I tried that by running:

sudo dnf install sqlite

This failed – there was no package of the name. Searching the package repositories revealed the package name was sqlite2.

I installed this.

sudo dnf install sqlite2

Life is never quite so simple…

Once installed, I promptly set out to try my ddl. It was a fairly simple script, so I expected it to JustWork™. But of course, it did not!

It turned out that the problem was the SQLite version – I needed v3 instead of v2. But my package manager did not even show v3 – so what was going on here?

A little digging around revealed a potential solution – perhaps my package database was out of date and I needed to update it?

So I first uninstalled the SQLite package.

sudo dnf history undo <transaction_number>

Where the <transaction_number> is the number of the transaction that installed SQLite – this can be found by running sudo dnf history list and looking for the SQLite installation.

I then tried the following, to refresh the local package database.

sudo dnf upgrade --refresh

Now I tried to install it again.

sudo dnf install sqlite

This time it worked!

sqlite3 -version
3.51.2 2026-01-09 17:27:48 b270f8339eb13b504d0b2ba154ebca966b7dde08e40c3ed7d559749818cbalt1 (64-bit)

Re-running the ddl script worked. I had a searchable table.

I tried inserting a few data rows and querying, and could see it all worked as expected.

Closing thoughts

There were, of course, some issues with the format of statements in my scripts – once corrected, the scripts worked as expected and queries seemed to perform well enough. I will add a full set of data across multiple stock symbols – that should give me a better feel for the performance and the foot-print of stock data. Once I have stock data for the stocks I’m generally interested in, I think it’ll be clear whether this decision is a good one, or not.