Database Management Systems: SQLite vs. PostgreSQL

When starting a new api to use as a backend, Ruby on Rails makes it as simple as could be. One simple command sets up a full backend environment, including an SQLite database.

rails new --api filename

I am now of the mindset that I will, for always and ever (with one exception), add one extra addition to that command to start up my environment as a PostgreSQL database. At the end of the previous command, simply add:

--database=postgresql

Both of them are RDBMS’s (Relational DataBase Management Systems) and they are both SQL databases, which means you can query using SQL commands. By comparison, in NOSQL databases you query data by key, or using map/reduce functions to iterate through a table of data and obtain the needed information.

The major exception I have is for quick & simple build in burns. Let’s talk about the differences, and why I’ve chosen this mindset…

SQLite

First, let’s talk about SQLite. As the name implies, SQLite is a light weight database model. It is very small (usually less than 600kb), and is designed to be quick and easy, making it very user friendly. The issue is, it’s therefore missing several major components necessary to certain applications.

1. First and foremost, SQLite is a server-less database. With SQLite, there are no other processes, threads, machines, or other mechanisms (apart from host computer OS and filesystem) to help provide database services or implementation. This makes it much more difficult and less powerful to deploy, and certain deployment options (like Heroku) don’t support SQLite databases.
2. The second major reason I dislike SQLite is it’s limited data-types. It supports only 5 major data types, which can be very limiting when designing your application and modeling your database:

3. Lastly, it does not support XML format, has no Secondary database models, and it does not support any replication methods, or partitioning methods.

It also has limitations when it comes to Concurrency Restrictions, User Access Management, and Security measures.

PostreSQL

Not only is their logo much cuter, but right out of the gates, PostgreSQL is set up for deployment. Heroku makes deploying your backend incredibly simple. Again, I’d probably stick with SQLite for quick builds and simple set ups for practice or simple apps I’d use locally on my computer.

1. DATA!!! It’s actually fascinating the amount of data-types that PostgreSQL allows for. To start, they offer for 9 different types of numeric data, 8 different types of data/time data, 7 different types of geometric data, text search types, JSON types, XML, money, and many more, including ARRAYS of most of those types of data. There are so many types of acceptable data that I don’t even understand the differences in some of them.
2. I know I just mentioned this, but I’ll touch on it again… ARRAYS. You can send full arrays of data, and postgres handles this no problem. This can be VERY helpful with certain types of applications, like one I just built that helps songwriters write better lyrics. Being able to send a full array of lines of lyrics made the save process INFINITELY easier, smoother, and more powerful than a previous project where I had to send each item of an array individually to my SQLite3 backend.
3. Unlike SQLite, Postgres does support XML format, has Document store as Secondary database models, and it also supports Master-master replication, as well as partitioning by range, list, and hash.
4. On top of my own biases, PostgreSQL is FAR more SQL compliant than SQLite or other options like MYSQL, which is fairly far from full compliance. PostgreSQL is also 100% open source and focused on the community, and is highly extensible.

All in all, there are certainly some advantages to keeping it basic with a SQLite database, but aside from practice problems and simple solutions, I will continue to utilize PostgreSQL as my go to for RDBMS’s.

Thanks for listening in and feel free to let me know your thoughts when it comes to SQL or NOSQL database systems.

Cheers!