The day started off well, and I was confident that I would complete all of my objectives for the day. Rails, on the other hand, had different ideas for me. If you're new to Ruby on Rails, you've almost likely had your bug fest baptism. This is a positive thing in any case, because it speeds up your learning.

Despite draining the fun out of an otherwise interesting day, the lesson learned from the strain was my consolation.

In building a rails app some of the errors you would have to deal with are database-related.

At the end of this article, you would learn the following

• What is database Migration
• How to fix duplication issues on your database.
• How to fix PostgresQL insufficient privilege errors

Let's get right into it.

What is Database Migration

Database migrations are a convenient way for you to alter your database in a structured and organized manner. Learn all about migration from Rails guide

Rails Migration allows you to use Ruby to define changes to your database schema, making it possible to use a version control system to keep things synchronized with the actual code.

How to fix duplication issues on your database.

Have you seen this and wondered what in the blue skies could be the cause of this?

rails aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_email"
DETAIL:  Key (email)=() is duplicated. 

Caused by:
ActiveRecord::RecordNotUnique: PG::UniqueViolation: ERROR:  could not create unique index "index_users_on_email"
DETAIL:  Key (email)=() is duplicated.

This error happens when attempting to migrate a database, and the error message indicates that a unique identification has been violated. In other words, an item in your database has been duplicated.

I would assume you already have PostgreSQL set up with Rails.

To fix this issue, you first have to drop the database, re-create and then migrate it by running this code

rails db:drop db:create db:migrate

How to fix PostgresQL insufficient privilege errors

If your PostgreSQL database is not properly set up after implementing the code to drop, create, and migrate your database, it will raise another error.

PG::InsufficientPrivilege: ERROR:  must be owner of database railsblog
Couldn't drop database 'railsblog'
rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  must be owner of database railsblog


Caused by:
PG::InsufficientPrivilege: ERROR:  must be owner of database railsblog

The error above indicates you do not have sufficient privileges to drop database and attempting to create one would result in another error.

rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create database


Caused by:
PG::InsufficientPrivilege: ERROR:  permission denied to create the database

I'm sure you're thinking, "How?"

especially if you are certain you have granted the user all privileges. To correct this, run

rails db:migrate VERSION=0

To revert all migrations.

What we have to do next is Open our SQL shell(psql) and alter our database. SQL shell(psql) is the command-line interface for PostgreSQL.

We must first confirm the existence of our database once we are in our SQL shell (psql).

postgres=# \l

Then,

postgres=# ALTER DATABASE <database name> OWNER TO <username>;
ALTER DATABASE

You now have full permission to run drop functions in your database after modifying it.

Return to your code editor terminal and execute

rails db:reset

Dropped database 'railsblog'
Dropped database 'railsblog'
PG::InsufficientPrivilege: ERROR:  permission denied to create database
Couldn't create 'railsblog' database. Please check your configuration.
rails aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied to create database


Caused by:
PG::InsufficientPrivilege: ERROR:  permission denied to create database

But, while you've been able to drop the database, you might not be able to create a database. So we have to go back to our SQL shell(psql)

postgres=# ALTER USER <username> CREATEDB;
ALTER ROLE

Now you can create a database

rails db:create db:migrate

Conclusion

Hurray!!!

All of the issues have been resolved, our database is in good shape, and our app is operational. Fixing this problem was the highlight of my day. Please like and comment if you found this useful. Do not hesitate to share with friends. Also, you can leave feedback if you have a question or contribution.

Happy coding!