DEV Community

Cover image for Postgres: Setup & Basics
Sean Tansey
Sean Tansey

Posted on • Updated on

 

Postgres: Setup & Basics

I recently started working on a personal project that includes a database in the back end. As a primarily Frontend Engineer it had been years since I'd set up a database. Below are the basic steps to getting started with postgresql.

Installation

I'll be installing postgres using Homebrew, a package manager used for Mac/Linux. If you don't have homebrew installed here's a link. Now pop open a terminal window and lets get to work.

We'll start by updating homebrew to make sure we have the latest:

brew update
Enter fullscreen mode Exit fullscreen mode

Next we'll install postgres:

brew install postgresql
Enter fullscreen mode Exit fullscreen mode

Once installed lets start the service:

brew services start postgresql
Enter fullscreen mode Exit fullscreen mode

Postgres is now installed and running. You can stop the service at anytime with:

brew services stop postgresql
Enter fullscreen mode Exit fullscreen mode

Log into postgres and create a user

psql is a terminal based front-end for postgres. It will connect us to postgres and allow us to create databases, users, issue queries, etc..

First we have to log in to psql as the default root user:

psql postgres
Enter fullscreen mode Exit fullscreen mode

We are now logged in as the root user. You should see this in the command prompt:

postgres=#
Enter fullscreen mode Exit fullscreen mode

Here's a list of some common postgres commands you'll use now that we are logged in:

\q - quit/exit psql
\list - lists all databases
\du - lists all database roles
\dt - lists all database tables
\conninfo - lists info about current database connection
\c [dbname] - connect to a different database 
CREATE DATABASE [dbname]; - creates a new database
CREATE USER [username]; - creates a new user
ALTER ROLE [username] CREATEDB; - allows user to create databases
ALTER ROLE [username] CREATEROLE; - allows user to create roles
DROP DATABASE [dbname]; - deletes database
DROP USER [username]; - deletes a user
Enter fullscreen mode Exit fullscreen mode

Currently we are logged in as our root user, its generally poor practice to use this user in postgres so lets create a new user that we'll use to perform our database actions and grant them some basic permissions:

postgres=# CREATE USER username WITH PASSWORD 'password';
postgres=# ALTER ROLE username CREATEDB CREATEROLE;
Enter fullscreen mode Exit fullscreen mode

We've now created a new user called username with a password and given them permission to create databases and roles/users. You can check this by running \du and you should see your user listed.

Log out of the root user and log in as our new user:

postgres=# \q
Enter fullscreen mode Exit fullscreen mode
psql -d postgres -U username
Enter fullscreen mode Exit fullscreen mode

Note: -d indicates which database we are logging into (we are logging into the default postgres db), -U indicates what user we are logging in as.

You should now see:

postgres=>
Enter fullscreen mode Exit fullscreen mode

Creating a Database and Tables

Lets create a new database:

postgres=> CREATE DATABASE dbName;
Enter fullscreen mode Exit fullscreen mode

If you use the \list command you should see your new database listed. Congrats you've just created a database in postgres! From here you can add tables and data to your database directly in psql, however I'd recommend doing this in a tool like TablePlus or pgAdmin as its much easier to visual and doesnt require memorizing SQL commands. However heres a basic example in psql for a table of messages.

To create a table:

dbname=> CREATE TABLE messages (
   id SERIAL PRIMARY KEY,
   name VARCHAR(30),
   message TEXT
);
Enter fullscreen mode Exit fullscreen mode

To add data to that table:

dbname=> INSERT INTO messages (name, message) VALUES ('Donald Duck', 'Qwack Qwack');
Enter fullscreen mode Exit fullscreen mode

Then to check out our data:

dbname=> SELECT * FROM messages;
Enter fullscreen mode Exit fullscreen mode

Result:

id |    name     |   message
----+-------------+-------------
  1 | Donald Duck | Qwack Qwack
(1 row)
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
andrewbaisden profile image
Andrew Baisden

PostgreSQL and SQL are fairly easy to learn wish more people realised this. Good setup guide.

Collapse
 
seantansey profile image
Sean Tansey

Thanks, yeah I think for the most part the set up is harder than the basics of using it. At least until you start doing some more complex SQL joins

Top Heroku Alternatives (For Free!)

Recently Heroku shut down free Heroku Dynos, free Heroku Postgres, and free Heroku Data for Redis on November 28th, 2022. So Meshv Patel put together some free alternatives in this classic DEV post.