Getting Started with PostgreSQL

Why PostgreSQL?

In this post, I am going to be looking at creating a managed PostgreSQL (which from now on I'm just going to call a SQL database) and connecting to it using node_postgres. I have a decent amount of experience using MongoDB databases and have built a fairly good sized project using Prisma, which uses SQL behind the scenes but I had not ever had to actually write a SQL query and interact directly with a SQL database.

With Mongo Atlas (or mLab) and mongoose, it was extremely easy for me to find out how to get up and running but I was having a hard time just getting up and running with my own SQL database.

Here were the considerations:

• I didn't want to use some weird free website that I would never be able to use in production.
• I wanted it to be cheap (I was maybe successful?!)
• It needed to be a real solution that I could see myself using in a real Node application. (opposed to using something like a GoDaddy server with a bunch of databases set up for me already)

Without further ado, here is what I'll be walking through:

1. Getting a DB up and running with DigitalOcean.
2. Setting up the config for your project in Node with node-postgres.
3. Creating a table and testing it.

Creating a PostgreSQL Database

If you haven't used DigitalOcean yet, it's pretty great. In short its a site where you can set up virtual servers among other things. At first, I tried to set up my own server with MySQL installed (which would have been about $5 a month to run) but that was becoming a bit of a beast to figure out, and ultimately my goal was to just be able to access a SQL database to be able to write a frontend application so I didn't want to spend all my time there. So...

I settled on a DigitalOcean managed database. That costs $15 a month, but it's cheaper than a course, it's legit, I saved me a bunch of time (I mean, people have full-time jobs managing databases) and DigitalOcean gives you referral codes that let you get credits for a certain amount of time (usually 30-60 days) and I had credit so it was free to try! If you want to give it a try sign up using my referral code and you can get $50 in credits for 30 days so it's free for you to get started.

Setting up the DigitalOcean Database

1. Sign up for DigitalOcean
2. Create a new project
3. Create a managed database. I used the PostgreSQL, which at the time of writing is the only option. You'll need to set up some options here but it's pretty self-explanatory.
4. The database will get created and you can see it in your project. Go to it to complete the setup.
5. Add your IP address so the database access isn't open to the public.
6. Download the SSL certificate, which we will use to connect.

Nice job! Your database is up and running.

Connecting to Database

I'm not trying to write a tutorial on how to make a whole application, so we are just going to tackle a few steps here. First, we will set up node-postgres, and then we will write a basic query to make a new table and log it to the console.

Project setup

To connect you only need 2 things, a .js file for conecting, and the .crt certificate you downloaded from DigitalOcean. Here is my structure.

postgresql-tutorial
  /certificate.crt (I renamed this)
  /connect.js (you can name whatever you want)

Set Up Connection

First, here is what I have to connect to my database. I got everything from the docs on node-postgres, but I left comments within to explain each part. You access all of the config options from your DigitalOcean, as seen from the photo below.

// node-postgres also support a pool connection, but I was more familiar with the Client type so I started with that
const { Client } = require('pg');
// fs is used to read the certificate
const fs = require('fs');

const config = {
  // everything here you can get from the "Connection Details" when viewing your database in DigitalOcean
  user: 'doadmin',
  host: 'db-postgresql-nyc1-21328-do-user-4669604-0.db.ondigitalocean.com',
  database: 'defaultdb',
  password: 'tqjgkd6uxyqger6n',
  port: 25060,
  /* You have to include the ssl options because DigitalOcean requires them.
  This is essentially just added on from the boilerplate config in the node-postgres docs */
  ssl : {
    rejectUnauthorized : false,
    ca: fs.readFileSync("./certificate.crt").toString(), //make sure name matches your certificate name you downloaded
  }
}

// creates a new Client instance with the config options above (you can also just put them directly in as argument)
const client = new Client(config);

// Connects! (Or tells me I don't know what I'm doing)
client.connect((err) => {
  if (err) {
    console.log("Connection error!", err)
  } else {
    console.log("Connection to DB successful")
  }
});

Testing out the database

With the code above (and your own config options since the ones above won't work anymore) you should see in your console that you are connecting. From here, it's pretty easy to work with the database. Basically, node-postgres has queries where you pass in a SQL query. I'm not getting into that much, after all, this was to get better at that in the first place. The query, if successful, will return a response object.

In the same connect.js file, add the code below.

const makeTable = `
  CREATE TABLE products (
  product_no integer,
  name text,
  price numeric
);`;

const testQuery = `SELECT * FROM products`

client.query(makeTable) //pass in the testQuery to see if making the table was successful
  .then(res => console.log(res))
  .catch(e => console.error(e))
  .then(() => client.end())

After adding those line, you should be able to run node connect.js and see a response object returned.

Essentially, client.query is taking in the makeTable SQL query (that I got from the PostgreSQL docs) which is a promise that will either log the response that's returned or log the error. Finally, the connection is closed.

You can see there is a second query testQuery which you can pass into client.query (replacing makeTable) and you should be able to get another response object that shows the details of the table you created at first.

That's it. Hopefully, someone else can find this useful and get up and running playing around with SQL in a real application!

I made this post pretty quick as my hands froze to death in and over-airconditioned Starbucks, so if something seems unclear please let me know so I can revise and clarify!