Intro
We learned:
- how to do stuff with PostgreSQL
- how to create a server with express
- how to connect express and PostgreSQL with pg
Now we want to learn how to connect our PostgreSQL database with our express server with sequelize
as ORM.
Create A Project Folder, do npm init
, install all needed packages
mkdir node-postgres-sequelize
cd node-postgres-sequelize
npm init
npm i express sequelize pg pg-hstore
We can see that sequelize
needs 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-sequelize;
psql -d node-postgres-sequelize
\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 only the database stuff
const Sequelize = require('sequelize');
// your credentials
DATABASE_URL = 'postgres://[db-user]:[password]@127.0.0.1:5432/node-postgres-sequelize';
const database = new Sequelize(DATABASE_URL);
module.exports = database;
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
. 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 Sequelize = require('sequelize');
const database = require('./database');
const User = database.define(
'users',
{
nickname: {
type: Sequelize.TEXT
}
},
{ timestamps: false }
);
User.readAll = async (req, res) => {
try {
const users = await User.findAll();
return res.send({ users });
} catch (error) {
return res.send(error);
}
};
module.exports = User;
We separate the User
model and its readAll()
function to make things more obvious. We don't have to write the SQL query (e.g. SELECT * FROM users
), we can use the sequelize
's abstraction of it, findAll()
. We don't care how sequelize
is doing this.
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}/`);
});
The exact some file like in the tutorial without sequelize
.
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 newsequelize
method (create()
,destroy()
etc.) - add a new route in
index.js
that runs the new method
Next Part
We created a connection between server and database without ORM (pg
) and with ORM(sequelize
).
In the next part, we will take a closer look at the pros and cons of both methods.
Further Reading
Express Docs
PostgreSQL Docs
SQL Syntax
sequelize at npm
sequelize docs
Top comments (3)
I love this
You saved a drowning man here with your series. Kudos
@usman ,
I'm very happy about that!
What challenge did you face?