I had an idea the other day for a small and simple web application. Just a bit of fun. My first thought was "How bare bones can I keep this?" I wanted to be able to query a small database. Rather than using MySQL which is probably the database I have most experience with over the years I decided to try out SQLite. But how do you create an SQLite database? And how do you interact with it? And what is an SQLite database in the first place?!
Let's start with that last question. Unlike MySQL, Postgres or MariaDB which are all database engines and run a database service, SQLite is at the very core a simple file based database. It's serverless, zero configuration and self-contained. You could copy an SQLite database from your PC to a phone or other device and start working with it right away. You can read more About SQLite here or read why it's the "most deployed" database in the world. The entire database is in the .sqlite file itself.
Ok, so how do you interact with it? This is what I wanted to understand and one method is detailed below. There are tons of ways to access, read from and write to an SQLite database. You can do it using command line tools (this is what I went with!), use libraries from your favourite programming language (e.g. Python) or use interactive tools like phpLiteAdmin or DB Browser for SQLite.
I'll answer how to create an SQLite database a little later. First, let's take a bit of an inventory. Here are the things I started with:
- A Win11 machine
- Some command prompt knowledge
- A Search Engine and Web Browser
- A local version of XAMPP to run my little app
At this point I will give a disclaimer... XAMPP is old. Like old, old. I think the latest version available for Windows is from 2023. I used to use XAMPP all of the time about 20 years ago. There are other ways to put a webserver on your computer but this is quick and dirty (and familiar and as you'll find out later, useful. Don't judge me). All I want is a simple portable way to play with a web server on my machine. Maybe I'll do a proper installation of the AMP stack (Apache, MySQL, PHP) on a Raspberry Pi or even play around with Nginx (another web server similar to Apache) at some point. For now this will do.
For the simple web application, if you can even call it that, there are three files:
- A simple html file which calls a PHP script (index.html)
- A small PHP script to interact with the database (db.php)
- A .sqlite file that will contain the database (knowledgebase.sqlite)
I'm not going to go into how the HTML or PHP files work here. They're basic and don't do much. The HTML just allows you to enter a letter and get back all of the entries from the database that begin with that letter. Ultimately they're just a simple way to query the data from the SQLite database via the browser. The main aim here is to learn a little about SQLite and hopefully documenting this will give you some useful information too.
So, there's a way to display the application in a browser using XAMPP at https://localhost/kb/ (Simply put the index.html and db.php into C:\xampp\htdocs\kb\). The index.html file interacts with the PHP which can read from the sqlite database and then there's the knowledgebase.sqlite database.
But, eh, how do you create an SQLite database?
First create a file called knowledgebase.sqlite. You can call it something else if you like just remember to update any of the commands or instructions below accordingly. In Windows Explorer just go into the kb folder, right-click and select New > Text Document and changed the .txt extension to .sqlite. Click Yes to the warning about changing a file extension. Now you have an empty file but still no database. How do you actually populate the database itself?
Enter the Command Line Shell For SQLite. To get this you'll need to navigate to the SQLite Download Page and grab the sqlite-tools-win-x64 package. This is a zip file that contains the sqlite3.exe that we will need to interact with the database. There's no installer, just a couple of executables that can be run from the command line. I initially made the mistake of downloading the sqlite-dll package first and only realized after I had scanned for viruses, verified the checksum and extracted it that it only included a DLL and a DEF file. No use to me!
Let's crack on then with the correct download. We're going to scan it using Defender and verify the checksum published on the SQLite website. When downloading things from the internet that you aren't familiar with it is best practice to scan for viruses and if there is a checksum available to verify it. Your browser might attempt this but there's no harm in giving it a double-check either way, right? Sometimes you have to know what goes on "under the hood" when it comes to computers. If you don't want to know, you can probably skip a load of this post...
Manually scanning for viruses using Defender is straightforward, right-click on the ZIP file while holding the shift key on your keyboard and select "Scan with Microsoft Defender." Once you see "0 threats found" you are good to proceed.
Next, we want to make sure that the package we downloaded is correct by verifying the checksum. The SQLite website provides checksums for each file they make available to download. A checksum is a way to verify that a file is what the original uploader intended and that it wasn't inadvertently replaced, maliciously modified or worse, downloaded incorrectly. The checksum for any file is always going to be the same. If you get a different result on your system than the one they have listed on their website then something has gone wrong. Do not proceed until they match.
The checksums that SQLite provide are SHA3-256. Hmm... Usually you can use the built in CertUtil command in Windows to verify checksums but it doesn't support SHA3-256 yet. OpenSSL does though! Luckily that's available in the XAMPP installation (under apache/bin/openssl.exe) - there was a reason other than quick and dirty to use XAMPP after all!
To verify a checksum using openssl you're going to run something similar to the following command:
openssl dgst -SHA3-256 sqlite-tools-win-x64.zip
You'll need to adjust that command to match the file you download. All going well the result matches the checksum listed on the downloads page! In my case it was the following:
I downloaded sqlite-tools-win-x64-3500400.zip to a folder called Downloads\Software. openssl.exe is located in C:\xampp\apache\bin. The checksum is in the red box in the below image, so in this case the result we are looking for is "a17b6e4d58efd95ecda7a77aa2ec0be97022dd91dc8d08a7f827fe3d5993b719"
To verify the checksum you can do the following using the Windows Command Prompt:
- Press the Windows key + R at the same time to open the "Run" dialog window
- Enter "cmd" into the "Open:" box and hit Enter or click OK
- Type cd C:\xampp\apache\bin and hit Enter to get to the directory containing openssl.exeNote: I blanked out my username here, old habits die hard!
- If you have openssl installed somewhere else then change to that directory instead (cd = change directory)
- Then run the following command: openssl dgst -SHA3-256 "C:\Users\xxxx\Downloads\Software\sqlite-tools-win-x64-3500400.zip"
- You may need to modify both the folder name and file name to point to the version of sqlite-tools that you downloaded
- And the result:
SHA3-256(C:\Users\xxxx\Downloads\Software\sqlite-tools-win-x64-3500400.zip)= a17b6e4d58efd95ecda7a77aa2ec0be97022dd91dc8d08a7f827fe3d5993b719 - The output from openssl matches the checksum listed on the website
- Success!
Now safe with the knowledge that Defender hadn't picked up any viruses and that the zip file was downloaded correctly I unzipped it into a folder ready for use. As I mentioned earlier, you could skip all of this but having an understanding of checksums is good knowledge and may help you out in other situations where you want to check the integrity of a file. If you're doing that, you don't have to use SHA3-256, there are loads of other algorithms that can be used e.g. the SHA2 family or if you are going really old school MD5. That said, algorithms like MD5 are not recommended for use anymore and there are ways to fool the MD5 algorithm that can be used maliciously. Not something I'm going to get into here but we might revisit that topic again.
In the extracted sqlite-tools folder we have four executables.
- sqldiff.exe
- sqlite3.exe
- sqlite3_analyzer.exe
- sqlite3_rsync.exe
For our purposes only one is relevant for now: sqlite3.exe
Copy or move your empty knowledgebase.sqlite file into the sqlite-tools folder.
You can either stay in the command prompt and navigate to the sqlite-tools folder and run sqlite3.exe or just double-click the sqlite3.exe file from Windows Explorer to open it. At this point, you are now in the SQLite tool but you are not interacting with a real database. Anything you do at the moment is temporary and if you exit or close the application any changes you make will be lost. SQLite helpfully tells you this:
The transient in-memory database warning when you open sqlite3.exe
Typing .help and hitting Enter will give you a list of options you can use. We're going to use the .open command to interact with our empty knowledgebase.sqlite database. If you prefer, you can open the knowledgebase.sqlite file directly by entering sqlite3.exe knowledgebase.sqlite at the outset.
Type the command .open knowledgebase.sqlite and hit Enter
The application simply goes back to the sqlite> prompt but doesn't display any success message. Not great, not terrible. If you made a mistake and entered the wrong file name a new file will be created in the sqlite-tools directory. Kinda useful...
Now that sqlite3 has our database open, we need to give the database a structure. As a simple example, let's create a single table. This is outside of the scope of this little tutorial but it's pretty standard SQL. There are a few good places to learn about SQL such as https://www.w3schools.com/sql/
Type the following and hit Enter:
CREATE TABLE topics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
summary TEXT NOT NULL
);
If you copied the text into the command prompt you may get a warning, you can click "Paste Anyway" - the command will run across multiple sqlite> lines.
Once you hit Enter sqlite3 saves the database structure to your file. Now you'll notice that the sqlite database went from 0Kb to having a bigger file size. In my case this was 12Kb. Still pretty tiny but at least you know there is something in there now. You can verify the table was created by using the .tables command:
You can also check how the table is built or what should go into it using the .schema command. So for our "topics" table we'll enter .schema topics and it returns the below:
Now you can populate some data into the table with an INSERT query:
INSERT INTO topics (title, summary) VALUES
('Apple', 'A fruit that grows on trees.'),
('Astronomy', 'The study of stars and planets.'),
('Biology', 'The study of living organisms.');
You might notice that this command only enters values for title and summary. The "id" in the table is a number that will increase with each row which might be useful later on.
To verify that the INSERT query has worked correctly you can do a SELECT, like so:
SELECT * FROM topics;
Finally to use the SQLite database with our little web app we simply exit the SQLite program by using the .quit command and we can then copy the knowledgebase.sqlite file into our kb directory within the XAMPP htdocs folder.
So that's it. We downloaded and verified the SQLite tools package, created a very basic SQLite database that has some data in it and can be used later. If you found this useful or have any questions please leave a comment. I know this post could be replaced with a simple prompt to your favourite LLM but I don't believe that the internet is dead yet and I still think there is value to writing these things down.
