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!
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.
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.
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.
- 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.
- 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.
- 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 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:
- 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
- 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 is very compliant to SQL standards.
- It is an open source project and community driven.
- 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.
- 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.
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:
Remove the following line in the
and replace it with
gem 'pg'. Then run
Next, convert the
config/database.ymlfile. Replace the adaptor lines that read
adapter: postgresql. Also, change the
database:to a custom line.
After changing the
config/database.yml, you will have to create and migrate your new Postgres database:
$ rake db:create $ rake db:migrate
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.
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!