Getting started with our PostgreSQL database

Introducton
We will be talking about how to get a database set up in PostgreSQL and establishing the connection to the database and how to do some basic query functions to get us practicing using postgres. Postgresql is an object-relational database meaning it runs with an object-oriented database model: objects, classes and inheritance can be a part of the schema. Because PostgreSQL is a SQL database it makes a good switch for someone who is familiar with databases such as MySQL and mariaDB. The setup is a little different, but the queries are very similar. Which is why the switch to PostgreSQL is not a hard task.

Setting up the database
Assuming you already have PostgreSQL installed on your local machine. There are some steps we can take to set up our database.
1) The first thing we need to do is start the postgres server. Run the command sudo service postgresql start
2) Next, we need to connect to postgres Run the command
sudo -u postgres psql

Now you should be inside the postgres shell. Now we can create our database. To create the database, we run the command
CREATE DATABASE [name]
Where name will be the name of the database. Next, we need to connect to the database so run the command: \c [name]
You should see a message that says the database is connected. At this point you have created the database and now can start adding things to it. If you are familiar with MySQL you can create a schema.sql file in your code editor, where you can list out your schema such as tables without having to create the table in the command line. To run the schema.sql file in postgres you first need to cd into the directory that the schema.sql file is located. Once in that directory run the command: \i schema.sql

Database Connection
Now that we have our schema loaded into the database, we now need to connect establish a database connection. The database connection is like setting up other SQL connections such as MySQL. You will need to know the user, password, host, database name, and port of the PostgreSQL server. The port is something that is different because with MySQL we didn’t have to know the port of the database just the server port. If you haven’t changed the port when you set up postgres its default is 5432. Assuming you are going to be use pg-promse to make queries, your database connection will look like this

const pgp = require('pg-promise')({});

const db = pgp({
  user: DB_USER,
  password: DB_PASS,
  host: 'localhost',
  port: 5432,
  database: 'My postgress db name',
});

Above, user will be set to whatever user you set up for the PostgreSQL database. By default, the user is postgres but if u changed it to maybe your name then user will be your name.
password is the password you initially set for that user. Host is of course local host. database will be the name of the database you want to connect to.

Queries
Now that we set up the database connection, we can see some example of basic PostgreSQL queries. Let us say your database has a animals table. and we wanted to get all the animals in the database. We could create a function that select all the animals in the database and returns them. Let’s look at an example below

const getAllAnimals = async(req, res) => {
  try {
    const animal = await db.any('SELECT * FROM animals');
    res.send(animals);
  } catch (err) {
    console.log(`no animals, ${err}`);
  }
};

Because we are using promises to handle the queries, we can use Async. In the above example we establish a function called getAllAnimals. Next, we use to async keyword meaning this function will return a promise. inside the function we use the try keyword which essentially means try to do this but if that is not successful, then move on. So, inside the try is where we would put our query. db.any means anything that matches this should be returned. If the query has an error, then we will have our catch keyword to catch the error.

To add something into the database we can take similar steps. We create the function and inside the function handle the query and data. The difference will be is how we receive the data. this will normally be done in a post request with an incoming body object with the data that needs to be added. Let us look at an example

 const createAnimal = async(req, res) => {
    try {
     await db.query('INSERT INTO animals (name, color, legCount, 
      diateryNeeds) VALUES ( ${animalName}, ${animalColor}, 
      ${AnimalLegs}, ${animalFood}', req.body);
     res.send({ message: 'Animal added' });
   } catch (err) {
    console.log('Sorry, no animals', err);
   }
};

Inside our query, we can see where we are inserting values into and then we see some template literals that whole the key values of our incoming object and at the end we put req.body because that will be where our object is held in the incoming response. We are essentially saying this object will have these keys and insert the value of the keys into the specific database columns.

*Conclusion
In conclusion, we have set up a database in PostgreSQL by creating it and connecting to it. We can of course manually create tables in the terminal, but I feel that having a schema already set up in the file and running it through postgres will save a lot of time. After setting up the tables we then set up the database connection so that we could start creating functions that would execute our queries. And we have set up some basic query functions to get us started with playing with PostgreSQL.