SQLite vs the filesystem

Mountains

16 A little that a righteous man hath is better than the riches of many wicked.

17 For the arms of the wicked shall be broken: but the LORD upholdeth the righteous.
Psalm 37:16-17, The Bible

I know it seems like I'm beating a dead horse at this point, but I must say, I'm loving every moment of it. Not necessarily because I like to shit on SQL (though I do), but because I am currently enjoying the benefits of avoiding the language completely.

Take for instance this website. I am building it from scratch without using a single line of SQL. In fact, recently I made some changes to my website and it was relatively easy to change and test afterwards compared to SQL.

In this blog post, I would like SQLite and the filesystem to go head-to-head to see who the real champion of the database management system (DBMS) space is. I will compare the current way I'm doing things to how I would have done them with SQLite.

Without further ado, let the punching begin! 🥊

Referee's banter

Referee: Okay boys, I want a nice clean fight, and by clean, I mean foul and rough. I want no holding back. Teeth missing, ribs cracked, lungs being coughed out, the whole nine yards, capice? Let's give the audience what they asked for tonight!

Round 1: Database creation

Using SQLite:

CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    category TEXT,
    content TEXT,
    banner TEXT,
    link TEXT,
    preamble TEXT,
    public INTEGER DEFAULT TRUE,
    hidden INTEGER DEFAULT FALSE,
    date_published TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

Using the filesystem (i.e. GNU core utils):

$ mkdir -p data/posts

Some observations:

SQLite requires you to know information about your data types up front. The filesystem does not have this limitation since it does not use many types. Data integrity could be compromised but the possibility is low since I fill this out manually in my Markdown file.

Commentator: What a blow by filesystem! SQLite did not see that swift right cross coming, but he's still on his feet!

Round winner: Filesystem

Round 2: Database population

Using SQLite:

-- After writing the blog post in 'sql-sucks.md'

INSERT INTO posts (
    title,
    category,
    content,
    banner,
    link,
    preamble
) VALUES (
    'SQL sucks!',
    'General',
    'It really does!',
    'images/sql-burning-in-fire.webp',
    '/posts/sql-sucks',
    'This is an introduction'
)

Using the filesystem:

$ # After writing the blog post in 'sql-sucks.md'
$ cp sql-sucks.md data/posts

This works because all the metadata that I would usually keep in the SQL database is at the top of the file already. This concept is so widely used in real life applications. The concept I'm referring to is the head-body concept, though that is not the official name.

The concept of that head and body parallels heaven and earth (as I mentioned in my yin-yang post), though I don't want to focus on that now. What I do want to focus on is how data in files are usually stored with some header information (i.e. head) before the content (i.e. body). We see this in music files (MP3, AAC, WMV), video files (MP4, MPEG, AVI), documents (XML, HTML, SVG) and so many other places.

Therefore, I just copy the same formula here; all Markdown files contain all their metadata. This makes it easy to query in the future, as we will see later.

Commentator: Straight punch to the gut by filesystem! SQLite is coughing out blood now!

Round winner: Filesystem

Round 3: Database query

Using SQLite:

SELECT id,
    title,
    category,
    content,
    banner,
    link,
    preamble,
    public,
    hidden,
    date_published
    FROM posts
    LIMIT 10

Querying with the filesystem is a little different. The whole purpose of using the filesystem is customization and vendor lock-in prevention. That being said, I had to build my database management system API from scratch so it took a little longer than it would take for a SQL database, admittedly. However, when I was done, I came up with this:

$ python
>>> from app.database import Database
>>> db = Database()
>>> posts = db.posts.previews.get(limit=10)
>>> posts[0].title
'SQLite vs the filesystem'

A couple things to unravel here:

I created a node system for my database where each node represents a point of data, like a tree. Under db is posts and under that is previews.
The action verbs chosen for each node are based on (or more accurately, stolen from) HTTP request methods (GET, POST, PUT, DELETE). This parallels CRUD (Create, Read, Update, Delete) for databases.
Note that previews are different from posts. previews are the summarized versions of the posts (for my home page), while posts are the full posts.
The data can instantly be accessed by switching to my website's root directory and starting a python session.
I can add new database API as I go (i.e. progressively).

Commentator: A swift uppercut from the filesystem throws SQLite back a couple steps! He is really in a daze!

Round winner: Filesystem

Round 4: Data manipulation

In SQLite:

UPDATE posts SET title = 'SQL - Stupid Query Language',
    description = 'A query language'
    WHERE id = 1;

For the filesystem, I could open any file directly and make the update. If I wanted to update multiple files, sed comes to my rescue:

$ sed -i -e "s/#\s+(.*)/SQL - Stupid Query Language/g" -e "s/^(Description:)\s+(.*)/\1\sA query language/g" blog-post-in-question.md

Using sed is just one way. There are other programs, both terminal and graphical applications, that can search through text files. So don't let the sed script scare you; the point is that you can use whatever you want since each Markdown is a plain old text file.

Commentator: Just when you thought SQLite was out of the match, he's fighting back! Way to go landing his first haymaker of the fight!

Round winner: SQLite (since it's a bit less cryptic)

Round 5: Data deletion

In SQLite:

DELETE posts WHERE id = 1;

In the filesystem:

$ rm data/posts/sql-sucks.md

Commentator: SQLite is still standing, but the filesystem is still swinging his fists!

Round winner: Filesystem

Round 6: Database alteration

Adding a column in SQLite:

ALTER TABLE posts ADD COLUMN date_updated TIMESTAMP DEFAULT NULL;

Sadly, all your information has to be uniform. You cannot have more fields for one post than others. This may be desired in some applications, but specifically with this website, I don't care to have that restriction.

For the filesystem, I just open the file in question in my favorite editor and change its metadata:

$ vim sql-sucks.md

Each column in the SQLite database would represent a metadata field in the file. In my Markdown file, I have it like this:

Title: SQL sucks
Description: The many reasons SQL sucks
Published: 2024-03-15 00:12

# SQL sucks

It really does.

So to add the field Updated, I need to add it to my file:

Title: SQL sucks
Description: The many reasons SQL sucks
Published: 2024-03-15 00:12
Updated: 2024-03-15 16:37

# SQL sucks

It really does.

Commentator: SQLite should change his name to beat, battered and bruised!

Round winner: Filesystem

Round 7: Database dump

In SQLite:

$ sqlite3 data.db .dump > dump.sql

For the filesystem, just archive your files:

$ tar -cvzf mywebsite.zip data/

A couple takeaways:

All files remain readable. For SQLite, neither a binary file nor a SQL dump file can be easily read by a human.

Commentator: This is training for the filesystem! SQLite won't even fight back anymore!

Round winner: Filesystem

Round 8: Database restore

In SQLite:

$ sqlite3 data.db < dump.sql

For the filesystem:

$ tar -xvf mywebsite.zip

Commentator: Uh-oh, with that last blow, SQLite is on the floor and may not be getting up anymore!

Round winner: Filesystem

Who's the champion?

Commentator: This was a tough battle, but very entertaining. I wish I could say both parties held their ground, but that was not what I saw today. SQLite was outsmarted, outperformed and outclassed. He lives to fight another day, but he may not want that day to ever come.

Bonus: more jabs at SQL (stolen from Reddit)

SQL is old and feels like FORTRAN.
You have loads of dialects. SQL depends on your Server (MS SQL Server, MySQL, MariaDB, etc) what is supported.
It's difficult to create functions. DRY is very hard to maintain.
Syntax is not consistent: You may or may not put a ; or "Go".
You have no step-by-step debugger.
"Anyone" in the team can deploy triggers and procedures. There is no real "build" and "deploy" process. Git Integration is very bad. Everyone "works on the living object". (I know, you have prod, test and dev environments, but that is not comparable to developing locally and having a real continous integration platform like Jenkins etc).
I feel, there are no real standards. There is no scientific Community. Its ugly. Not like C++ or Python.

The bottom line

While they promise them liberty, they themselves are the servants of corruption: for of whom a man is overcome, of the same is he brought in bondage.
2 Peter 2:19, The Bible

SQL is not the worst language in the world, but it is one of the most overused. The old me would not consider any other option simply due to ignorance of other methods.

When in doubt, use the filesystem and ditch SQL. Don't mean to fat-shame but SQLite needs to lose a couple more pounds and probably drop to someone in a lower weight class.

If you would like to reply to or comment on this blog post, feel free to email me at efe@mmhq.me.