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.jswith a new query (INSERT,DELETEetc.) - add a new route in
index.jsthat runs the new method
Next Part
We will learn how to use an ORM.
Top comments (0)