Postgres: Setup & Basics
Tags: postgres, webdev, tutorial, sql
Published: 2022-05-09 02:49:00


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](https://www.postgresql.org/docs/).

## 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](https://brew.sh/). Now pop open a terminal window and lets get to work.

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

```javascript
brew update
```

Next we'll install postgres:

```javascript
brew install postgresql
```
Once installed lets start the service:

```javascript
brew services start postgresql
```
Postgres is now installed and running. You can stop the service at anytime with:

```javascript
brew services stop postgresql
```

## 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:

```javascript
psql postgres
```

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

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

```javascript
\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
```

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:

```javascript
postgres=# CREATE USER username WITH PASSWORD 'password';
postgres=# ALTER ROLE username CREATEDB CREATEROLE;
```

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:

```javascript
postgres=# \q
```

```javascript
psql -d postgres -U username
```

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: 

```javascript
postgres=>
```

## Creating a Database and Tables

Lets create a new database:

```javascript
postgres=> CREATE DATABASE dbName;
```

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:

```javascript
dbname=> CREATE TABLE messages (
   id SERIAL PRIMARY KEY,
   name VARCHAR(30),
   message TEXT
);
```

To add data to that table: 

```javascript
dbname=> INSERT INTO messages (name, message) VALUES ('Donald Duck', 'Qwack Qwack');
```

Then to check out our data:

```javascript
dbname=> SELECT * FROM messages;
```

Result: 

```javascript
id |    name     |   message
----+-------------+-------------
  1 | Donald Duck | Qwack Qwack
(1 row)
```