06 - Being PRAGMATIC
I have been too used to PostgreSQL in the time I have spent in my career so far. PostgreSQL and MySQL are what I had worked with in the beginning. PostgreSQL appealed more to me and so everything that I built later was built using Postgres. With time, the way it works became habit and the way one configures (at least the basics) it became a muscle memory, of sorts.
It is the first time I am working with SQLite. It is for Chamber that I am dealing with this “lite” database. That name is confusing though, the facade is thin and this thing too is a monster.
Let’s get PRAGMA-tic!
The word pragmatic means dealing with problems in a practical way rather than by following ideas or principles. That’s what Google says anyway. Pragmatic is why some languages use the keyword PRAGMA for selecting settings, or changing the way compilers build the code.
SQLite says it’s “zero configuration” and really, you can just open a DB file and start working on it without a worry in the world and in most cases you would be safe enough. However, there are multiple behaviors that it lets you change. The good and the bad part about that is - it is “per connection”. You read it right! So if you open the database, you are also “locking it”. And every single time you open the database, you can choose different behavior for the database.
That’s quite different from the way I have been used to working with the databases. Typically, you will configure your databases using a config file, start the daemon and connect your clients. Not with SQLite though. And for what SQLite was designed for - I think that is a great thing.
The PRAGMA directives
SQLite has a few pragma directives. Each one controls a behavior. You can query for the current value or set the value of your choice. And that is a bit of a problem. You can ask “but how is that a problem?”. Have a look at the screenshot below!
If you followed my videos on Chamber, you know that I was keeping the chamberfile in that location ~/code/tmp/chamberpath. You can see me opening the file with .open command and I am showing the tables contained in the database using the .tables command. The PRAGMA dance follows after that.
If you want to check the value, or set a new one for any pragma setting, you use the PRAGMA query. It takes two forms. The first is just sending PRAGMA <pragma_name>; and the other is PRAGMA <pragma_name> = <pragma_value>;. The first form is to retrieve the current value, the second is to set the desired value. Remember that SQLite is configuration free. There is nothing to configure. That means - when you open the database, the value for each PRAGMA setting is the default one. For example, the default for foreign_keys is set to false and journal_mode is delete.
But do you see the problem? The inconsistency?
Being pragmatic with PRAGMA - the inconsistencies
Pragmas are usually enumerations and sometimes values. They can be expressed as integers or strings. They can also be tabular in nature (which means multiple rows and multiple fields).
That’s inconsistent if you think of how you wanna set configuration! But then, maybe it is because there is no boolean data type in SQLite!
There is yet another one - you see in the image above? How I can set the value for foreign_keys to ON or OFF but when asked to return the values, it always returns as either 0 or 1! Again - boolean values, so you can excuse that - but isn’t that inconsistent? Maybe accept 1 or 0 only for setting the values too, or just return the strings ON or OFF depending on the integer value?
I am sure there is a great explanation of why that is so, and I am guessing “backwards compatibility” is the reason here.
Now, there are some which can accept both integers or their respective enumerated value! That is yet another level of inconsistent. Why? While 0 and 1 represent boolean values. When setting a value, you can use 1, true, yes or on for truthy value and 0, false, no and off for falsy values. But that confusion does not end there. You can also set those non-integral values with or without single quotes! 🤔
The synchronous one is a great example of that! Check the screenshot below. What do you see?
The creators of SQLite woke one morning and chose violence?
But if you look at the first screenshot, you will see a pragma named as journal_mode. That is another beast! The thing with that one is: it accepts a string and returns a string. That’s right. It is still enumerated, but only internally. You can say something like PRAGMA journal_mode = paper; and it will return delete back! And if you are trying to get it, it will not give you 1, 2, 3 or some other number. It will give you delete.
That is definitely inconsistent!
And to put the cherry on the top, when working with these pragmas using golang (again, using the package that I have mentioned in the video), I saw that the integers being returned were being returned as “strings”. Yup! So that was a pleasant surprise, honestly. Because I now did not have to write a separate function to handle functions which were giving out integers and another one for strings.
Having said that, since we are moving forward with thought and trying to offload any kind of highly sophisticated architecture for the codebase, while still keeping it organized, I have made the functions for just setting the ones that I needed for Chamber to be built. I will cover these in the upcoming video.
Now, if you think that I was dissing SQLite over the minor inconvenience, well, yes and no. Yes - because it was new to me, and I have not seen configs being managed like this. And No - because this is just one of the ways of doing things. It is not inherently good or bad; it’s just different.
As I am building Chamber, and digging a bit deeper into SQLite, I am finding it very interesting. As a user of PostgreSQL for more than a decade, I think SQLite breaks many conventions set by the larger databases and is yet an amazing one that mostly gets sidelines because of its own name. So I would like to go into why I think SQLite is a great database!
The GOAT
While PostgreSQL has a logo of an elephant, SQLite has a feather to show you. I think with the amount of work this thing can do, the logo could be at least a goat if not an elephant. But SQLite doesn’t always behave like a goat, sometimes it feels like a GOAT (greatest of all time).
Why? The question is rather “Why not?”
- SQLite is Open Source but is not open to modifications. I think this is amazing. They have not deviated from the core principals and it is not affected by 10,000 requests to do something other than what founders think would be good.
- SQLite works on almost all known practical operating systems (macOS, Linux, Windows, Android, iOS, BSDs) and architectures (AMD64, ARM64)
- SQLite is embeddable and can work without specifying any configuration, although allows you to specify some!
- SQLite stores everything in 1 file. Copy it - that’s your backup. Paste it and it’s restored. That’s it!
- Has support for transactions, foreign keys, joins, indexes etc. It has full-featured SQL!
- It has tools to perform replication, watching differences between two databases and has a CLI shell.
- It has drivers for every sensible, practical and modern programming language.
- Offloads the authentication to you. So using it is super simple. You can put a wrapper of any kind of authentication but you won’t have to deal with “I forgot the root password of the database”! Ever!
My interest piqued when I read a Reddit post which mentioned an article whose title was: searchcode.com’s SQLite database is probably 6 terabytes bigger than yours. Let that sink in.
6.4 TeraBytes (6400+ GigaBytes). 1 file!
And you should go and use that site. It is faster than I expected. Way faster than I would hope something like SQLite to pull off anyway.
And if that was not enough, you will find this text inside (as a part of) the License file:
> ~~~
The author disclaims copyright to this source code. In place of
a legal notice, here is a blessing:
* May you do good and not evil.
* May you find forgiveness for yourself and forgive others.
* May you share freely, never taking more than you give.
~~~Tell me that’s not a GOAT move. I’ll wait!