A web app using postgresql locally
Creating a web app using postgresql on a server
Making the web application work with PostgreSQL provided by an Ubuntu server
This lesson assumes that you have set up an Ubuntu 24.04 server as I go over in this guide. This lesson assumes that you have gone through this lesson: A web app using postgresql with Docker. With those assumptions, the main things are to setup PostgreSQL on that type of server, and to modify the queries.js file from the project involving Docker.
Installation of Postgresql
This is how I installed PostgreSQL on my server:
$ sudo apt update
$ sudo apt install postgresqlThe second command installs a number of packages including postgresql-client-common and postgresql-16.
Configuration and setup of PostgreSQL
As installed from the packages above, PostgreSQL must be configured and setup to be able to use. One problem is that the postgres user can connect to the postgres database without a password. Since the postgres user is a superuser, that is not the best thing from a security standpoint. So, we need to create another superuser that is required to use a password. In addition, we need to change the configuration so that connecting via peer is no longer allowed. The peer connection is what allows any user to connect to a database with the same name as the username. So, that is not a good idea, in my opinion.
Creating a new superuser
Here are the steps I went through to create the new superuser:
$ sudo -u postgres psql -d postgres
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.
postgres=# create role mysuper superuser createdb createrole login;
CREATE ROLE
postgres=# alter user mysuper encrypted password 'make_one_up';
ALTER ROLE
postgres=# create database mysuper_db owner mysuper;
CREATE DATABASE
postgres=# \qOn line 1 we are running psql with the user postgres to open the database named postgres. This is done using sudo as this requires admin privileges on the server. Notice that psql does not prompt at all for a password. In fact the password does not exist.
On line 5 we create a super user called mysuper with the superuser, createdb, createrole and login privileges. On line 7 we give mysuper a password. Use a password of your choice. On line 9 we create a database called mysuper_db that is owned by mysuper. From this point on, when we want to manage something on the PostgreSQL server, we will use the mysuper user and the mysuper_db database.
On line 11, we quit out of psql.
Configuring postgresql
At this point, if you tried to login using the mysuper user, you would get an error message like this:
$ psql -u mysuper -d mysuper_db
/usr/lib/postgresql/16/bin/psql: invalid option -- 'u'
psql: hint: Try "psql --help" for more information.
vern@virtual-ubuntu24:~$ psql -U mysuper -d mysuper_db
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "mysuper"This is because the database, mysuper_db, does not have the same name as the username, mysuper. This is what is required by peer authentication. So, we need to change the way that authentication is performed.
To do this, we will edit the file /etc/postgresql/16/main/pg_hba.conf
$ sudo nano /etc/postgresql/16/main/pg_hba.confOnce nano starts up, type CTRL+/ to jump to a line. Use line number 118 and you will see this:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256To prevent the postgres user from being able to connect to the postgres database without a password, we need to comment this line out:
local all postgres peerJust put a # at the beginning of that line to comment it out
This line, must also be commented out:
local all all peerSo, comment that line out with a # at the beginning. Finally replace that last line with the following line:
local all all scram-sha-256When you are done with these changes, the lines should look like this:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
#local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all peer
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256The modified or new lines are shown shaded. Type CTRL+O and hit <enter> to save the changes. Then, type CTRL+X, to exit nano. Then, you need to restart the postgresql service:
$ sudo service postgresql restart
$ psql -U mysuper -d mysuper_db
Password for user mysuper:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.
mysuper_db=#Creating a new regular user
Once you are connected as your super user, you do the following to create a new regular user.
mysuper_db=# create role joe login;
CREATE ROLE
mysuper_db=# alter role joe encrypted password 'joepass';
ALTER ROLE
mysuper_db=# grant joe to mysuper;
GRANT ROLE
mysuper_db=# create database joe_db owner joe;
CREATE DATABASE
mysuper_db=# \qFrom that point on, you will use the joe username with the joe_db database. I used a working directory called student_app. The path to that directory will be designated as /path/to/student_app. I will make use of the same init.sql file that I used for the previous project. Here is that file:
drop table if exists students cascade;
create table students (
id serial,
first_name text,
last_name text,
major text,
primary key (id)
);Now, I will use psql to initialize the joe_db database:
$ psql -U joe -d joe_db
Password for user joe:
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.
joe_db=> \i init.sql
psql:init.sql:1: NOTICE: table "students" does not exist, skipping
DROP TABLE
CREATE TABLE
joe_db=> \qI won’t add the test data in this time, so our table in the front end application will be empty initially.
Modifying the files associated with the web application
We are really only changing the backend of the application. So, the files that are of interest are index.js and queries.js. Here are those files in the final form from the application when using Docker for the postgresql service. Here are the files, with the highlighted lines the ones that are new.
const express = require('express');
const bodyParser = require('body-parser');
const db = require('./queries.js');
const cors = require('cors');
const app = express();
const port = 3000;
app.use(cors({
origin: 'http://localhost:8000'
}));
app.use(bodyParser.json());
app.use(
bodyParser.urlencoded({
extended: true
})
);
app.get('/', (req, resp) => {
resp.json({ info: 'Express and Postgresql' });
});
app.get('/students',db.getStudents);
app.post('/students',db.createStudent);
app.get('/students/:id', db.getStudentById);
app.put('/students/:id', db.updateStudent);
app.delete('/students/:id', db.deleteStudent);
app.listen(port, () => {
console.log('App running on port 3000');
});If you look carefully, there is nothing in this file that depends on the specifics of the postgresql service. So, nothing needs to be changed here.
const Pool = require('pg').Pool;
const pool = new Pool({
user: 'joe',
host: 'localhost',
database: 'joe_db',
password: 'joepass',
port: 5432
});
function getStudents(req,resp) {
pool.query('select * from students order by id asc',
(error, results) => {
if (error) {
throw error;
}
resp.status(200).json(results.rows);
});
}
function createStudent(req, resp) {
const { first_name, last_name, major } = req.body;
pool.query('insert into students (first_name, last_name, major) ' +
'values ($1, $2, $3)', [first_name, last_name, major],
(error, results) => {
if (error) {
throw error;
}
resp.status(201).send('Student added');
}
);
}
function getStudentById(req, resp) {
const id = Number(req.params.id);
pool.query('select * from students where id=$1', [id],
(error, results) => {
if (error) {
throw error;
}
resp.status(200).json(results.rows);
});
}
function updateStudent(req, resp) {
const id = Number(req.params.id);
const { first_name, last_name, major } = req.body;
pool.query('update students set first_name=$1, last_name=$2, ' +
'major=$3 where id=$4',[first_name, last_name, major, id],
(error, results) => {
if (error) {
throw error;
}
resp.status(200).send('Student updated');
}
);
}
function deleteStudent(req, resp) {
const id = Number(req.params.id);
pool.query('delete from students where id=$1', [id],
(error, results) => {
if (error) {
throw error;
}
resp.status(200).send('Student deleted');
}
);
}
module.exports = { getStudents, createStudent, getStudentById, updateStudent,
deleteStudent };Line 4 is the only line that needed to be changed, because I used the same username and database name. So, only the host has changed. It was 172.17.0.2 for the Docker container’s postgresql. But, now that we are running locally off the server, the host is just localhost.
Installing the Node.js packages
The same Node.js packages we used for the previous application need to be installed. So, here are the installation steps:
$ cd /path/to/student_app
$ npm install express pg corsIf you don’t have the http-server package already installed, you can install it using the following command:
$ npm install -g http-serverUsing the same front end
Since we are only changing the backend, we only needed to modify the part of the middleware that specified the connection to the database. That was done in the queries.js. This means that we can use the students_app.html file from the previous project without any changes. Here is the code for that file:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<script>
document.addEventListener('DOMContentLoaded', init);
let students_data = [];
let students_tbody;
let add_dlg;
let edit_dlg;
let student_id;
function removeChildren(elem) {
while (elem.childNodes.length > 0) {
elem.removeChild(elem.childNodes[0]);
}
}
async function getStudents() {
try {
const students = await axios({
method: 'get',
url: 'http://localhost:3000/students'
});
students_data = students.data;
console.log(students_data);
updateStudentsTable();
}
catch (error) {
console.log(error);
}
}
async function handleAdd() {
const add_fn_box = document.getElementById('add_fn_box');
let first_name = add_fn_box.value.trim();
const add_ln_box = document.getElementById('add_ln_box');
let last_name = add_ln_box.value.trim();
const add_maj_box = document.getElementById('add_maj_box');
let major = add_maj_box.value.trim();
try {
await axios({
method: 'post',
url: 'http://localhost:3000/students',
data: {
first_name: first_name,
last_name: last_name,
major: major
}
});
getStudents();
add_dlg.close();
}
catch (error) {
}
}
async function showEditDlg(event) {
const id = Number(event.currentTarget.id);
student_id = id;
try {
const results = await axios({
method: 'get',
url: `http://localhost:3000/students/${id}`,
});
const student = results.data[0];
const edit_fn_box = document.getElementById('edit_fn_box');
edit_fn_box.value = student.first_name;
const edit_ln_box = document.getElementById('edit_ln_box');
edit_ln_box.value = student.last_name;
const edit_maj_box = document.getElementById('edit_maj_box');
edit_maj_box.value = student.major;
edit_dlg.showModal();
}
catch (error) {
console.log(error);
}
}
async function handleEdit() {
const edit_fn_box = document.getElementById('edit_fn_box');
let first_name = edit_fn_box.value.trim();
const edit_ln_box = document.getElementById('edit_ln_box');
let last_name = edit_ln_box.value.trim();
const edit_maj_box = document.getElementById('edit_maj_box');
let major = edit_maj_box.value.trim();
await axios({
method: 'put',
url: `http://localhost:3000/students/${student_id}`,
data: {
first_name: first_name,
last_name: last_name,
major: major
}
});
getStudents();
edit_dlg.close();
}
async function handleDelete() {
await axios({
method: 'delete',
url: `http://localhost:3000/students/${student_id}`
});
getStudents();
edit_dlg.close();
}
function updateStudentsTable() {
removeChildren(students_tbody);
for (let i = 0; i < students_data.length; i++) {
const student = students_data[i];
let tr = document.createElement('tr');
tr.addEventListener('click', showEditDlg);
const id = student.id;
tr.setAttribute("id", id);
let td = document.createElement('td');
let content = document.createTextNode(student.first_name);
td.appendChild(content);
tr.appendChild(td);
td = document.createElement('td');
content = document.createTextNode(student.last_name);
td.appendChild(content);
tr.appendChild(td);
td = document.createElement('td');
content = document.createTextNode(student.major);
td.appendChild(content);
tr.appendChild(td);
students_tbody.appendChild(tr);
}
}
function init() {
students_tbody = document.getElementById('students_tbody');
add_dlg = document.getElementById('add_dlg');
const add_button = document.getElementById('add_button');
add_button.addEventListener('click', () => { add_dlg.showModal(); });
const add_cancel = document.getElementById('add_cancel');
add_cancel.addEventListener('click', () => { add_dlg.close(); });
const add_ok = document.getElementById('add_ok');
add_ok.addEventListener('click', handleAdd);
edit_dlg = document.getElementById('edit_dlg');
const edit_cancel = document.getElementById('edit_cancel');
edit_cancel.addEventListener('click', () => { edit_dlg.close(); });
const edit_ok = document.getElementById('edit_ok');
edit_ok.addEventListener('click', handleEdit);
const delete_button = document.getElementById('delete_button');
delete_button.addEventListener('click', handleDelete);
getStudents();
}
</script>
</head>
<body>
<h1>Students Table</h1>
<button id="add_button">Add student</button><br>
<dialog id="add_dlg">
First Name:
<input type="text" id="add_fn_box"><br>
Last Name:
<input type="text" id="add_ln_box"><br>
Major:
<input type="text" id="add_maj_box"><br>
<br>
<button id="add_cancel">Cancel</button>
<button id="add_ok">Ok</button>
</dialog>
<dialog id="edit_dlg">
First Name:
<input type="text" id="edit_fn_box"><br>
Last Name:
<input type="text" id="edit_ln_box"><br>
Major:
<input type="text" id="edit_maj_box"><br>
<br>
<button id="edit_cancel">Cancel</button>
<button id="edit_ok">Ok</button>
<button id="delete_button">Delete student</button>
</dialog>
<table border="1">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Major</th>
</tr>
</thead>
<tbody id="students_tbody"></tbody>
</table>
</body>
</html>Note that the front end application makes use of the routes set up in the middleware. Since the middleware has been updated, this file does not need any changes made. So, you can open terminals to /path/to/student_app and run the express server and the http-server:
$ cd /path/to/student_app
$ node index.jsIn another terminal, run this:
$ cd /path/to/student_app
$ http-server -a localhost -p 8000Here is a screen shot of the front end web application (students_app.html) running in the browser:
Next, is a screen shot after adding Jane Doe a Biology major:
You can do further testing and you will see that the web application functions just as we expect.