Lessons from PostgreSQL

#postgres #whatilearned #rails

Hello there! Today, I’ll be talking about some noteworthy lessons I learnt while using PostgreSQL for Our Shiftwork App.
As much as we didn’t go in-depth using PostgreSQL on the App, they were still quite some things to learn using PostgreSQL.
For the Shiftwork app, my team and I are building, the major thing PostgreSQL was to do was in creating the database.
As I had said before in my blogpost here, I jumped from one error to another trying to get the database to be created with most of my issues; PostgreSQL related.

PostgreSQL Power

Postgres is an incredibly powerful database that’s packed with many different features. Some of its most interesting and productive capabilities, however, aren’t always evident.
When we started the initial setup of the app, I wondered why we chose PostgreSQL over MySQL, but I got to learn that PostgreSQL is a superior choice over MySQL based on its rich features, active community, and support.

As much as PostgreSQL and MySQL are both immensely popular open-source databases, and a variety of real-time applications today utilize both. MySQL is known to be the world’s most popular database, whereas PostgreSQL is known as the world’s most advanced RDBMS database(Relational Database Management System).

As an RDBMS database, MySQL is not fully SQL compliant and does not have many of the features that PostgreSQL has. This is why PostgreSQL has become a great choice for developers, and its popularity is growing exponentially with each passing day.
PostgreSQL also makes setting up and using databases easier and simpler both on-premises or in the cloud. PostgreSQL can also be a very good data warehouse for running complex reporting queries and procedures on large volumes of data.

PostgreSQL is an easy-to-use database with its full stack of RDBMS database features and capabilities that can handle structured and unstructured data. Installations can be easily done on Linux-flavored environments using yum or source code from the PostgreSQL website. Installing from source code gives you much more fine-grained control over the installation.
Just as it’s the same with Rails, I learned that a lot can be going under the “hood” despite how deceptively easy it might be to use.

Another thing I learned was that there are no “users” in PostgreSQL, just roles.
By running psql Postgres in your terminal, you’ll automatically log in with your username to PostgreSQL, therefore accessing the role created. Once a role is created, you can see it by using the \du command.

The following roles attributes are seen by default:
Superuser
Create role
Create DB
Replication
Bypass RLS
and it’s possible not to be a member of any of the roles (Herein lies some of the major errors that might be encountered with PostgreSQL).

Creating a new role
A new role is created using the CREATE ROLE command:
CREATE ROLE <role>;

Quick Summary of useful commands in postgreSQL
sudo -u Postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

As I had to consume some documentation and go through resources, I learned a tip which is the importance of reading the PostgreSQL Manual. The official documentation (or docs as they are referred to in short) of any product is the best place to find the largest wealth of information.

The Little Things

I remember running into this bug, “ActiveRecord::AdapterNotSpecified: 'PostgreSQL database is not configured”, “Rails Aborted” when I was trying to migrate into the database.
After checking and trying several solutions on StackOverflow, I found out the problem was because there was a space before development in the db config file.
This was fixed by simply removing space from the first line and following the indentation. This got me past my hour+ blockade as I didn't know leading spaces were an issue. Haha funny, isn’t it?

From here I learned that:

“It can be the simplest things that can cause the most aggravating errors.” - Arit Amana

Below are some links to the helpful resources/documentations I used with the bugs I encountered, if you desire to know more:

While I initially felt quite anxious and tired about how I kept running into one error from another, I’m glad that it has helped foster my learning process. I hope to learn more facets of PostgreSQL soon! Thank you for reading!

Referencing:
https://www.enterprisedb.com/blog/postgresql-vs-mysql-360-degree-comparison-syntax-performance-scalability-and-features

Photo by Christopher Gower on Unsplash