Setup postgresql for web development

Login into postgresql server using admin/root privileges #

• sudo -u postgres psql
  

Inside postgresql root shell #

• Create a database

  create database database_name;
  

  _

• Create a new user

  create user username_here with encrypted password 'password_here';
  

  _

• Grant all privileges to user on the database

  grant all privileges on database database_name to username_here;
  

  _

So far this is enough for most cases like working with Ruby on Rails(Active record), Django(Django ORM), Flask(SQLAlchemy), Laravel(Eloquent), And Spring(Hibernate) etc.

But things are a bit different in when it comes to prisma (ORM mostly popular in js ecosystem) it requires us to make a new shadow database before every migrations.

You can read more about it here

Basically we need our database user to have permission to create new database in postgresql server. To do this run:

ALTER ROLE username_here CREATEDB;

To check if user has permission to create new databases run this:

\du

it should output a table something similar to this:

 Role name         |                         Attributes                         | Member of
-------------------+------------------------------------------------------------+-----------
 postgres          | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 username_here     | Create DB                                                  | {}

Here are some common postgresql commands:

• \q or crtl + d quits the postgresql shell
• \l lists all available databases
• crtl + l clears the console
• crtl + c quits currently typed command in the postgresql shell
• \? for help

Thanks for reading & Keep coding!!! #