At end of my 6th week at Flatiron School's Web Development course, my partner and I had finally completed our Caribbean Stud Poker Rails Project and were ready to deploy it to Heroku. However, we had started our project using a SQLite database instead of using PostgreSQL, which ended up causing a frustrating (but not insurmountable) problem. Heroku, it turns out, is not compatible with SQLite. While we were eventually able to migrate our sqlite3 database over to postgres and deploy to Heroku, it was not immediately clear to me why the database migration was necessary, or even what the differences were between the two Database Management Systems (DBMs)'s. I think this topic will prove even more relevant as we continue developing more complex websites, which is why I wanted to learn more about it!

Database Terminology

While this seems like a basic topic - I've noticed the terms "database" and "database management system" used interchangeably, and it's important to get these right.

A database is an organized collection of data, generally stored and accessed electronically from a computer system. For example, the file 'development.sqlite3' in the above image is a database that stores valuable information for our poker app in tables like Users, Cards, Hands, etc.

A database management system (DBMS) is the software that interacts with end users, applications, and the database itself to capture and analyze the data (run queries). In our poker example, we initially used the SQLite DBMS and then migrated to PostgreSQL.

SQL is a domain-specific language designed for managing data held in a relational database management system. This is the language that we use to actually create our tables and store, manipulate and read our poker game data.

Types of DBMS's

Another topic that initially confused me was why there was a need for multiple DBMS's. Wouldn't it be better if there just be one that worked for every use case? Maybe, but it turns out that different types of projects are best suited to different DBMS's based on their storage, speed, and functionality.

SQLite

Embedded Database

According to its website, SQLite is described as "serverless", which is different from most DMBS's. Usually, a program that wants to access a database can communicate with the server to send requests and receive data back. SQLite skips the intermediate server process and requires programs to read and write directly from the saved database file.

Advantages of SQLite

• A serverless DBMS avoids the administrative need to install, setup, configure, initialize, manage, and troubleshoot a server process. This makes it easier to integrate with an application.
• SQLite allows multiple applications to access the same database simultaneously (rare for serverless DBMS's).
• Unsurprisingly, SQLite's library is very lightweight. And the entire database is stored in one file, making it extremely portable.

Disadvantages of SQLite

• Being serverless makes it harder to provide protection from bugs and corrupted memory in the client application.
• Even though it can host multiple applications at the same time, only one process can make changes to a SQLite database at one time.
• SQLite does not have a user management system that give users defined access privileges to the database.

When to use SQLite

• Embedded applications like mobile applications, game consoles, televisions, airplanes, drones, watches, and robots (the internet of things).
• Testing. Includes an in-memory mode that can run tests without the overhead of actual database operations.
• Working with small amounts of data. The SQLite website recommends that any database approaching 1TB be housed on a centralized client-server database.
• Only require 1 writer at a time.

PostgreSQL

Client-Server Model

PostgreSQL labels itself as "The World's Most Advanced Open Source Relational Database", and uses a client-server model requiring a DB server to set up and run over the network, as opposed to SQLite's serverless model. In the client-server architecture, each sessions consists of:

1. A server process, which manages the database files, accepts connections to the database from client applications, and performs database actions for the client. The database server is called postgres.
2. The user' clients application that performs database operations. These client applications could be anything from a web server to a graphical application to a special database maintenance tool.

The Postgres server can handle multiple simultaneous connections from different clients, which is a huge strength. For each new connection, the server "forks" a new server process, and then the client and new server process can communicate without intervention by the original postgres process.

Advantages of PostgreSQL

• Postgres is very compliant to SQL standards.
• It is an open source project and community driven.

Disadvantages of PostgreSQL

• Since it forks a new process for every new client connection, it has poor memory performance (each new process is allotted about 10MB of memory).
• It is less popular than other client-server DBMS's like MySQL, meaning there aren't as many third-party tools to help manager a Postgres DB.

When to use PostgreSQL

• Need to integrate the DB with other tools. Postgres is compatible with numerous other programming languages and platforms. So if you ever need to migrate your database to fit with a different tool, Postgres is a great option.
• Using complex operations such as data warehousing and online transaction processing.
• Your project doesn't require the fastest read speed.
• You are interested in optimizing data integrity.

Our un-deployable sqlite3 file

Migrating from SQLite to Postgres

Based on the factors detailed in the section above, it is understandable that a cloud-based platform like Heroku would require a client-server DBMS like Postgres to maintain data consistency and not the disk-backed storage provided by SQLite.

Since Rails has SQLite installed by default and we used an sqlite3 database for our Poker project, we had to migrate our DB to Postgres in order to deploy to Heroku. Here is what we did:

1. Remove the following line in the Gemfile:
   gem 'sqlite3'
   and replace it with
   gem 'pg'. Then run bundle install

2. Next, convert the config/database.yml file. Replace the adaptor lines that read adapter: sqlite3 to adapter: postgresql. Also, change the database: to a custom line.

3. After changing the Gemfile and the config/database.yml, you will have to create and migrate your new Postgres database:
   

   $ rake db:create
   $ rake db:migrate
   

4. Finally, just run a git add . and git commit -m "postgres", and you will all set up with your Postgres DB. At this point you can deploy your project to Heroku with git, if that is your end goal.

Conclusion

Hopefully this post was able to detail some of the main differences between two widely-used DBMS's (PostgreSQL and SQLite), and will help you select an optimal DBMS for your next project. Feel free to add any questions or comments below!

Apart from the inline links, I used the following references in this post:

https://en.wikipedia.org/wiki/Database
https://en.wikipedia.org/wiki/SQL
https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems
https://devcenter.heroku.com/articles/sqlite3