I am currently working in an application that need it to connect to a PostgreSQL database, after some research and of course trial and error, I found the way, that you will see. I installed PgAdmin that is a Database management tool that it´s very useful to manage PostgreSQL.
First we have to make sure that we install npm package pg, after we install it, we setup the request in our server file in this case the file it´s called server.js, you will see that other packages are already install and below you can see how your server file should look like
const express = require('express');
const bodyParser = require('body-parser');
const cookieParser = require("cookie-parser");
const { Client } = require('pg');
app.get('/', function (req, res) {
});
app.listen(8080, function() {
console.log("Port listening on 8080");
});
As you can see we requested with { Client }, this is going to help us set up the connection with the database through PgAdmin, after we set up our pg package, we go ahead and create the connection string below of const { Client } = require('pg');. Let´s go ahead and do that.
Create a constant with the name connectionString, the string will have the following format.
'postgres://userpostgres:password@localhost:port/databasename'
Let me explain the format, first we specify that is our database system, after that were you see userpostgres, you will type your postgres user, you can find by going to pgadmin right click on the postfres icon, click properties and select the connection tab and there you find the user, in password use the password that you set up, then we have localhost, because pgadmin comes up on your default browser when and because your databse it´s set up on your computer, after that we have the port you can find the port number in the same window that you found your user name, and last but not least your database name.
Now we our server file looks like this
const express = require('express');
const bodyParser = require('body-parser');
const cookieParser = require("cookie-parser");
const { Client } = require('pg');
const connectionString = 'postgres://postgres:12345@localhost:5433/test';
app.get('/', function (req, res) {
});
app.listen(8080, function() {
console.log("Port listening on 8080");
});
Now, we set up the connection, below the connection string, so we add this.
const client = new Client({
connectionString: connectionString
});
client.connect();
Now after we set up our connection, let´s go ahead and inside our our home route, we are going to query our database, we go ahead and type client.query we open parenthesis, inside the parenthesis we open quotation marks, inside the quotation marks you go ahead get the data from your table in SQL syntax, and at the add a callback function. see below
app.get('/', function (req, res) {
client.query("SELECT * FROM users", function (err, result) {
console.log(result);
});
});
Then when we go to our homepage you will see, that it retrieves the information in the table.
Hope that, this was helpful and happy coding.