PostgreSQL

Relational and Non Relational?

PostgreSQL is a relational database system that is able to support both relational and non-relational querying. This means that PostgreSQL has the benefit of both, it can have the benefits of a relational database and/or have the benefits of a non-relational database. PostgreSQL is highly extensible, you can define your own data types and build out custom functions. PostgreSQL has been proven to be highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. PostgreSQL is like if MySQL and Mongo had a kid that took steroids.

Installation

To start you will need to create the file repository configuration and to do this you will need to run sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' in your terminal. Then you will have to import the repository signing key by running this wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -. After you will need to update the package lists by running this sudo apt-get update. Lastly to get postgres on your system you will need run this sudo apt-get -y install postgresql.

How to start working in PostgreSQL

Once you have successfully installed PostgreSQL you can open it by running sudo -u postgres psql. To create a database you can run CREATE DATABASE databasename. To check your database you can run \l. To connect to a database you can run \c databasename. To create a table you can run CREATE TABLE table_name (
column1 datatype(length) column_contraint,
column2 datatype(length) column_contraint,
);. Now that you have a database and a table you are able to manipulate data. To insert data into the your table you will run INSERT INTO table(column) VALUES (value);. Once you have successfully inserted data you can check it by running SELECT * FROM tableName;.

using PostgreSQL in Visual Studio Code

To be able to use PostgreSQL in Visual Studio Code you will need to npm install pg. Once that is successfully installed you can start by creating a file where your database will be stored. In this file you will import the client from pg. Then you will create a new instance of client with valid arguments, client takes in your PostgreSQL information. Next you can connect with the connect function and use a dot then statement to log if the connection was successful. Lastly you would want to export this file to the main server file. if done correctly you should see what you log in your terminal.

const {Client} = require('pg');

const db = new Client({
    user: 'postgres',
    password: 'newpassword',
    host: 'localhost', 
    port: 5432,
    database: 'testing'
})

db.connect()
    .then(() => console.info('connected'))
    .catch((err) => console.error(err));

module.exports = db;

querying to the front

Now that you imported the database to your main server you can send data to the front. First you can set up a route that you would like to receive your data. Next use the database you imported to do a query. You can do this with the .query function. Lastly insert the query as a string into the augment and use a then statement to send it to the front.

const express = require('express');
const path = require('path');
const http = require('http')
const socketio = require('socket.io')

const db = require('./database/index')

const app = express();
app.use(express.json());
const sever = http.createServer(app);
const io = socketio(sever)

const DIST_DIR = path.join(__dirname, '../client', 'dist');
app.use(express.static(DIST_DIR))

app.get('/data', (req, res) => {
    db.query(‘SELECT * FROM roles;’)
      .then(result => res.send(result.rows));
      .catch(err => res.send(err));
})

sever.listen(5000, function() {
    console.log('listen on: http://localhost:5000')
});