Intro
We learned:
Now we want to learn how to connect our PostgreSQL database with our express server with pg
and no ORM.
Create A Project Folder, do npm init
, install express & pg
mkdir node-postgres-pg
cd node-postgres-pg
npm init
npm i express pg
Create index.js
and setup a small server
const express = require('express');
const app = express();
const PORT = 8080;
app.get('/', (req, res) => {
res.send({ message: 'endpoint working' });
});
app.listen(PORT, () => {
console.log(`Server running at: http://localhost:${PORT}/`);
});
Test your server with node index.js
,
it should run at localhost:8080
Create a local database, table & entry
sudo -iu postgres
createdb -O postgres node-postgres-pg
psql -d node-postgres-pg
\conninfo
CREATE TABLE users(id SERIAL PRIMARY KEY, nickname TEXT NOT NULL);
INSERT INTO users(nickname) VALUES ('miku86');
Test your database with SELECT * FROM users;
,
there should be 1 row
.
Create database.js
with a generic query
const { Pool } = require('pg');
// your credentials
DATABASE_URL = 'postgres://[db-user]:[password]@127.0.0.1:5432/node-postgres-pg';
const pool = new Pool({
connectionString: DATABASE_URL
});
// a generic query, that executes all queries you send to it
function query(text) {
return new Promise((resolve, reject) => {
pool
.query(text)
.then((res) => {
resolve(res);
})
.catch((err) => {
reject(err);
});
});
}
module.exports = {
query
};
Note: This implementation is very simple and not for production. You should never move your credentials into this file, you should use something like dotenv
. You should also move the database config into a separate file. But for the sake of simplicity, I make this example as simple as possible.
Create User.js
with one method to read all database rows
const database = require('./database');
const User = {
async readAll(req, res) {
try {
const readAllQuery = 'SELECT * FROM users';
const { rows } = await database.query(readAllQuery);
return res.send({ rows });
} catch (error) {
return res.send(error);
}
}
};
module.exports = User;
A User object, that has one method readAll()
.
This method sends a PostgreSQL query (SELECT * FROM users
) to the generic query in database.js
(we imported it at the top) and awaits the response, where it destructures the rows
and returns them.
Update index.js
with a new route
We now have to add the users
endpoint to index.js
.
const express = require('express');
// new: import User
const User = require('./User');
const app = express();
const PORT = 8080;
app.get('/', (req, res) => {
res.send({ message: 'endpoint working' });
});
// new: route to users, that runs readAll()
app.get('/users', User.readAll);
app.listen(PORT, () => {
console.log(`Server running at: http://localhost:${PORT}/`);
});
Test your server with node index.js
,
it should run at localhost:8080/users and show all (= 1) rows from the PostgreSQL database.
Summary
In then end, we did these steps:
- created an express server
- created a database with one table and content
- created a method to read the content
- added a route that runs this method
To setup additional database queries, we only have to:
- add a new method in
User.js
with a new query (INSERT
,DELETE
etc.) - add a new route in
index.js
that runs the new method
Next Part
We will learn how to use an ORM.
Top comments (0)