Express - Creating ORM for Postgres
Lesson Plan
In todays lesson we are going to connect our express app to a database. Being more specific we are going to connect to a postgres database. We are going to create ORM over our database tables. From these we are going to create a Todo application Rest Server. To finalize the lesson we are going to talk about migrations for our database.
ORM
Lets start by explaining what is ORM. ORM stands for Object-relational mapping which means transforming data into object oriented classes. This means instead of sending SQL query to grab data from our database, we are using an abstraction layer, defining models that match our database tables, and use the models and abstraction api to query the database instead of sending SQL queryies. This turns lines of code that look like this: Select * from persons To this: Person.findAll() Our code becomes much cleaner plus we are not depending on any specific database and we can easily switch databases.
Postgres
Postgres is an open source relational database. It uses an SQL language and extends it to provide many additional features. We will start by creating our postgres database. First lets install postgres using homebrew.
> brew install postgresql
After installation postgres we will need to create a new database. Lets create a database for a todo application rest server. our database name will be todo We will create a project directory for our project and create a folder in that project to hold our database files.
> mkdir express-sequelize-postgres
> cd express-sequelize-postgres
> mkdir db
> initdb express-sequelize-postgres/db -E utf8
initdb created a database cluster. A database cluster is a collection of databases that is managed by a single server instance. We need to create our todo database in that cluster. After creating the cluster database we can now start the database server by typing:
> pg_ctl express-sequelize-postgres/db -l logfile start
Now that we started the server instance we can create our database in the database cluster. In the terminal type:
> createdb todo
You can also connect via the terminal to the database server and perform queries from the terminal with psql
> psql todo
You can list all the databases in the server by typing:
> \l
Verify that your database is in the list. Now that our database is installed, lets try to connect to it by code by using sequelize
sequelize - connecting to our database
Lets bootstrap a new express project. In the project directory we created earlier, lets init npm, install express, and install sequelize. We will also need to install postgres engines so sequelize will be able to connect and perform queries on postgres
> npm init --yes
> npm install express --save
> npm install sequelize --save
> npm install pg pg-hstore --save
Create a file called db.js where we are going to create the database connection, verify that the connection is working, and export the sequelize instance.
const Sequelize = require('sequelize');
// create db connection
const sequelize = new Sequelize('postgres://localhost:5432/todo');
// verify connection is made
sequelize.authenticate()
.then(() => {
console.log('we are now connected!!!');
})
.catch(err => console.error(err.message));
module.exports = sequelize;
sequelize models
Now that we have the db connection we need to create models. For every table we will have there will be a model mapping that table. ORM means we will use our models to query the database. Our database will have two tables: - users - table to hold email of users - tasks - table to hold tasks of a user. A single user can have many tasks (one to many) So we will also have to sequelize models one for users and one for tasks. In your project create a directory called: models and in that folder create a file called user.js with the following code:
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('user', {
email: {
type: DataTypes.STRING,
allowNull: false,
validate: {
isEmail: true
}
}
});
User.sync();
return User;
}
Lets go over the code here. Sequelize will create a table for every model we create, if that table doesnt exist. We want to seperate our models to different files. We wont be able to require the modules like we use to do cause we want sequelize to manage caching for our models and not recreate them. sequelize has an import method, and it requires us to wrap our module creation in a function and export that function and in that function create the model. We create sequelize models using the sequelize.define first argument is the name of the module and the second describes the table. When describing the table we are specifing the field names and types. We can also add validation to the fields. We added validation that the entry is email. When the field should be required we can set allowNull to false Before returning the model we are calling sync which will create the table in case it doesn't exist. OK now lets create a file called: task.js with the following code:
const path = require('path');
module.exports = (sequelize, DataTypes) => {
const Task = sequelize.define('task', {
title: {
type: DataTypes.STRING,
validate: {
isAlphanumeric: true,
max: 20
}
},
when: {
type: DataTypes.DATE,
validate: {
isDate: true
}
}
});
const User = sequelize.import(path.resolve(__dirname, 'user'));
Task.belongsTo(User, {
allowNull: false
});
Task.sync();
return Task;
}
We added another model here called task. This model has 2 columns, one for holding the title which we validate to be alphanumeric string up to 20 characters. The second column is the date of the task. After defining the model we create the relation with the user table by calling the belongTo on the User model. The belongTo can have an optional argument specifying additional options on the foreign key, we added a required on that field so no null can be passed. By default the field name of the foreign key will be userId Note that in order to place the models in seperate files we need to import them by using sequelize.import which will deal with the caching of the model for us.
User API
Lets create an API to create, update, delete, read users from the database, and present the result as a json. We are going to seperate each API in a different file, and to do this we are going to use the express Router to create the express routes for this api in a seperate file. Create a folder in the root project called routes and in it place a file called user.js with the following code.
const express = require('express');
const sequelize = require('../db');
const path = require('path');
const User = sequelize.import(path.resolve(__dirname, '../models/user'));
const userRouter = express.Router();
userRouter.route('')
.get(function(req, res) {
User.findAll()
.then((result) => res.json(result))
.catch(err => res.send(err.message));
})
.post(function(req, res) {
User.create(req.body)
.then(user => res.json(user))
.catch(err => res.send(err.message))
});
userRouter.use('/:pk', function(req, res, next){
User.findById(req.params.pk)
.catch(() => res.status(404).send('no such user'))
.then(user => {
req.pkUser = user;
next();
})
});
userRouter.route('/:pk')
.put(function(req, res) {
req.pkUser.update(req.body)
.then(user => res.json(user))
.catch(err => res.send(err.message));
})
.delete(function(req, res) {
req.pkUser.destroy()
.then(() => res.status(204).send())
.catch(err => res.send(err.message));
})
module.exports = userRouter;
Lets go over the code above. We define routes for get and post for the root url. the get use the model findAll to retrieve all user rows. A big advantage of using sequelize is the usage of promises, so the findAll will return a promise that will be resolved with all the data which we will pass to res.json to make it a json response. The post will just pass the user body to the create function. Notice that we are not validating the user data which seems weird, but since we added validation to our model, sequelize will validate the data for us. To check the validation try to send a post request with a body with an invalid email address, and you will see an error is sent. The update and delete objects will sit in a different url. They will need to know the object to update/delete and in rest convention this is passed through the url of the request. So we define another url with pk as a param in the url. Since the delete and update will need to grab the object first, we added a common middleware for both of them that will grab the user and pass it in the request. Now that we have the user object we can call update or delete. Notice that in the create and update, we are using req.body which means we will have to install express body parser which we will do later on.
Task API
Time to create the CRUD API for a todo task. Similar to what we did with the user, we will define a router to deal with the task routes In the routes folder, create a file called task.js
const express = require('express');
const sequelize = require('../db');
const path = require('path');
const Task = sequelize.import(path.resolve(__dirname, '../models/task'));
const User = sequelize.import(path.resolve(__dirname, '../models/user'));
const taskRouter = express.Router();
taskRouter.route('')
.get(function(req, res) {
Task.findAll({
include: [
{model: User}
]
})
.then(tasks => res.json(tasks))
.catch(err => res.send(err.message));
})
.post(function(req, res) {
Task.create(req.body)
.then(task => res.json(task))
.catch(err => res.send(err.message));
});
taskRouter.use('/:pk', function(req, res, next) {
Task.findById(req.params.pk)
.then(task => {
req.task = task;
next();
})
.catch(err => res.status(404).send(err.message));
});
taskRouter.route('/:pk')
.put(function(req, res) {
req.task.update(req.body)
.then(task => res.json(task))
.catch(err => res.send(err.message));
})
.delete(function(req, res) {
req.task.destroy()
.then(() => res.status(204).send())
.catch(err => res.send(err.message))
})
module.exports = taskRouter;
Really similar to the user. One thing we added in the get method: By default the foreign key to the user table will be returned as an integer, if we want to populate the entire user we will have to add additional argument to the findAll. The additional arguemend apecify to include and the model will be the user model.
app.js
Time for us to create the main express app and to connect the router we created. In the root project folder create a file called app.js with the following code:
const express = require('express');
const userRouter = require('./routes/user');
const taskRouter = require('./routes/task');
const bodyParser = require('body-parser');
const app = express();
app.use(bodyParser());
app.use('/user', userRouter);
app.use('/task', taskRouter);
app.listen(3002, function() {
console.log('now listening on port 3002');
});
We are creating an express app, connecting the body parser (which you will have to install via npm), and connecting the two routes to our app. You can now launch your app and verify all the routes we created are working and returning proper response and performs the proper manipulation on the database
Migrations
The last topic on today lesson is migrations. Throughout the lifecycle of our app, our tables will have changes in them. Currenlty our users table containing only email field, but later on we might want to add another column to store gender, or maybe create another table called UserSettings that contains the settings for a user and is connected with a relation to the users table. Changes in our tables are so frequent, and sometimes also need to revered back to the original state. Since those changes to the database accour often, its imperative that our database structure will be declarative, meaning the changes will be written by code and with code we will be able to revert the changes back. We call these changes migrations and the idea is to store the changes in migration files, save what migration we ran in a table in the database, and if there is a new migration which we didn't run then run it and add to the database. Sequelize help us deal with these kind of migrations so lets see how we can do it. In this example we will be using migrations to add another column to the tasks table. This column is called description and will hold additional text on a todo task. Create a folder in the root project called migrations Install the sequelize cli by typing in the terminal:
> npm install sequelize-cli --save-dev
Now we can use the cli to create a new migration file. In the terminal type:
> sequelize migration:generate --url postgres://localhost:5432/todo --models-path models/task --name add-description
This will create a file in the migrations folder, with an object with two methods: - up is used to migrate the db - down is used to rollback the changes We will modify the up method to add a new text column, and the down method will drop that column. Modify the create file to look like this:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.addColumn('tasks', 'description', Sequelize.TEXT)
},
down: (queryInterface, Sequelize) => {
return queryInterface.removeColumn({
tableName: 'tasks'
}, 'description');
}
};
Now to run this migration, in the terminal type:
> sequelize db:migrate --url postgres://localhost:5432/todo
This will run the migration, and you can check your database that the column is added. To roll back the migration you can run:
> sequelize db:migrate:undo --url postgres://localhost:5432/todo
You should also properly update your task model to have a description.
Summary
We highly recommend for abstraction of database action to use ORM and to map the tables to classes. We saw in this tutorial how sequelize is a perfect fit for ORM for postgres.
Nerdeez is a software compnay specializing in delivering quality software, educating software teams by giving advanced courses about software technologies, and giving consulting services to software development.