Creating a Node.js Sequelize App PostgreSQL on Alibaba Cloud

In this tutorial, we will create a Node.js Sequelize application connected to an ApsaraDB RDS for PostgreSQL database on an ECS instance with Ubuntu 16.

By Grace Amondi, Alibaba Cloud Tech Share Author. Tech Share is Alibaba Cloud's incentive program to encourage the sharing of technical knowledge and best practices within the cloud community.

Node.js is an open source server environment that allows you to create your own web server and build web applications on top of it.

When coded correctly, Node.js it's extremely fast and makes very efficient use of system resources. Node Js is light on system resources because it is single threaded. Rather than giving each visitor a unique thread and a separate silo of resources, every visitor joins the same thread. A visitor and thread only interact when needed, such as when the visitor is requesting something or the thread is responding to a request.

Sequelize is a promise-based ORM for Node.js v4 and up. It supports the dialects PostgreSQL, MySQL, SQLite and MSSQL and features solid transaction support, relations, read replication and more.

In this tutorial, we are going to create a Node.js Sequelize application connected to an ApsaraDB RDS for PostgreSQL database on an Alibaba Cloud Elastic Compute Service (ECS) Ubuntu 16.04 instance.

Prerequisites

For you to successfully complete this tutorial. you will need to have:

A valid Alibaba Cloud account. If you don't have one already, sign up to the Free Trial to enjoy up to $300 worth in Alibaba Cloud products.
An ECS instance running Ubuntu 16.04. You can select your preferred region and configurations; this will not affect the outcome of the server setup.
A root password for your server.
An ApsaraDB RDS for PostgreSQL instance.

Step 1: Install Node, npm and nvm

We should refresh our local package index first, and then install from the repositories:

sudo apt-get update

sudo apt-get install nodejs

Let's install npm, which is the Node.js package manager. Do can do this by typing:

sudo apt-get install npm

To check which version of Node.js you have installed after these initial steps, type:

nodejs -v

In order for some npm packages to work (those that require compiling code from source, for example), you will need to install the build-essential package:

sudo apt-get install build-essential

Step 2: Setting up Minimal Express Application

First we are going to create a directory where our application will live in. Then moving into the directory we will create node application. Let's run these commands to begin:

mkdir nodeapp

cd nodeapp

Next we are going to install express using npm. Express is a minimal and flexible Node.js web application framework that provides a robust set of features for web and mobile applications.

Why Express?

It makes Node.js web application development fast and easy.
Easy to configure and customize.
Allows you to define routes of your application based on HTTP methods and URLS.
Includes various middleware modules which can be used to perform additional tasks on request and response.
Easy to connect with databases such as PostgreSQL.

We are going to use a Express Application Generator which generates an Express application "skeleton".Type in the following commands:

npm install express-generator -g

Finally we will create our app with just this simple command:

express myapp --pug

NPM will create the new Express app in a sub folder of your current location, displaying build progress on the console. On completion, the tool will display the commands you need to enter to install the Node dependencies and start the app.

Then install modules:

cd myapp

npm install

Let's initiate the development server to see what we have so far:

DEBUG=myapp:* npm start

You should have something like this:

Step 3: Enable Server Restart on File Changes

Any changes we make to our Express website are currently not visible until we restart the server. It quickly becomes very irritating to have to stop and restart our server every time we make a change, so it is worth taking the time to automate restarting the server when needed.

We will be using nodemon to cure this headache.

npm install --save-dev nodemon

Update package.json by putting a comma at the end of the start script command, and adding the "devstart" line seen below:

"scripts": {
    "start": "node ./bin/www",
    "devstart": "nodemon ./bin/www"
  },

Now you can initiate your server with:

DEBUG=myapp:* npm run devstart

Step 4: Connecting to ApsaraDB RDS for PostgreSQL

When you connect to an RDS instance through a client, choose to use an intranet or Internet address as follows:

Use the intranet IP address when your client is installed on the ECS that is located in the same region and the same network type as the RDS instance to be connected.
Use the Internet IP address for the other situations.

Also include the new ApsaraDB credentials to database configurations:

Host name/address: refers to the connection address of the RDS instance. If your application accesses the RDS instance through the intranet, enter the intranet IP address of the RDS instance. If your application accesses the RDS instance through the Internet, enter the Internet IP address of the RDS instance.

Port: refers to the port number of the RDS instance. If your application accesses the RDS instance through the intranet, enter the intranet port number of the RDS instance. If your application accesses the RDS instance through the Internet, enter the Internet port number of the RDS instance.

User: refers to the initial account name of the RDS instance.

Password: refers to the password of the initial account name of the RDS instance.

All these configurations can be found at your RDS Console. You will need to select the region where the target instance is located.

Click the ID of the instance to visit the Basic Information page. In the Basic Information area, you can find the connection addresses and port numbers of the RDS instance.

Step 5: Sequelize Setup

We are going to be making use of the Sequelize CLI package to bootstrap the project for us. It will also help us generate database migrations.

Let's begin by installing Sequelize CLI package.

$ npm install -g sequelize-cli

Next, we need to configure Sequelize for our project. For that, we will create a .sequelizerc file in our project's root folder. In this file, we are going to be specifying the paths to files required by Sequelize. Put the following content into this file:

const path = require('path');

module.exports = {
  "config": path.resolve('./config', 'config.json'),
  "models-path": path.resolve('./models'),
  "seeders-path": path.resolve('./seeders'),
  "migrations-path": path.resolve('./migrations')
};

The config.json file contain our application configuration settings, such as database authentication configuration. migrations folder will hold our application's migrations, while the models folder will hold the application models. Seed data is initial data provided with a system for testing, training or templating purposes. The seeders folder typically holds seed data, but we're not going to be using that in this tutorial.

Now we need to install sequelize alongside its packages:

$ npm install --save sequelize pg pg-hstore

We will need to run the simple sequelize init command in order to create the specified folders and generate boilerplate code.

$ sequelize init

If you check models/index.js file it should be like this:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(module.filename);
const env = process.env.NODE_ENV || 'development';
const config = require(`${__dirname}/../config/config.json`)[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable]);
} else {
  sequelize = new Sequelize(
    config.database, config.username, config.password, config
  );
}

fs
  .readdirSync(__dirname)
  .filter(file =>
    (file.indexOf('.') !== 0) &&
    (file !== basename) &&
    (file.slice(-3) === '.js'))
  .forEach(file => {
    const model = sequelize.import(path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

Step 6: Setting up a Connection

Sequelize will setup a connection pool on initialization so we should ideally only ever create one instance per database.

With the application bootstrapped, the only thing that we still have to do is creating our database then updating the config.json file to reflect our various environments settings.

First, we need to create a development database.

Replace the database url with the correct credentials for your ApsaraDB RDS for PostgreSQL instance.Edit config/config.json file with your correct ApsaraDB RDS credentials:

{
  "development": {
    "username": "<user>",
    "password": "<password>"",
    "database": "<dbname>",
    "host": "<dbhost>",
    "port": <dbport>,
    "dialect": "postgres"
  },
  "test": {
     "username": "<user>",
    "password": "<password>"",
    "database": "<dbname>",
    "host": "<dbhost>",
    "port": <dbport>,
    "dialect": "postgres"
  }
}

Step 7: Generating Models

We are going to create a simple user model. Run the following command:

$ sequelize model:create --name User --attributes username:string

This will generate a user.js file in the server/models folder as well as a -create-user.js migration file in the server/migrations folder. will be the date the model was generated.

The generated User model code is:

'use strict';
module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('User', {
    username: DataTypes.STRING
  }, {
    classMethods: {
      associate: function(models) {
        // associations can be defined here
      }
    }
  });
  return User;
};

Finallly connect your app with your db and app.js should look something like this:

var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'pug');

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

module.exports = app;

With the models and migrations in place, we're now ready to persist the models to the database by running the migrations. To do this, we run the following command:

$ sequelize db:migrate

This will find all the migrations in our migrations folder and execute them.

Step 8: Creating Controllers and Routing

With our models in place we a re going to create a userController. This will be responsible for creating users. Therefore create a users.js file in controllers folder and add the following inside.

const User = require('../models').User;

module.exports = {
  create(req, res) {
    return User
      .create({
        username: req.body.username,
      })
      .then(user => res.status(201).send(user))
      .catch(error => res.status(400).send(error));
  },
};

Next create an index.js file in controllers folder with the following code:

const users = require('./users');

module.exports = {
  users,
};

Next, we need to add an API route that maps to this functionality. Create a routes folder inside the root of the application. Inside the new routes folder, create an index.js file. We are going to place all our routes in this index.js file. Add the following to the file:

const usersController = require('../controllers').users;

module.exports = (app) => {
  app.get('/api', (req, res) => res.status(200).send({
    message: 'Welcome to the Users API!',
  }));

  app.post('/api/users', usersController.create);
};

This will add two new routes, a welcome route at /api and a route to create todos at /api/todos.We need to make the application aware that we just added the routes. Open up your app.js. We're going to be adding a require statement right before the route we'd earlier created, such that our app.js file now looks like:

const express = require('express');
const logger = require('morgan');
const bodyParser = require('body-parser');

const app = express();
app.use(logger('dev'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: false }));

// Require our routes into the application.
require('./routes')(app);
app.get('*', (req, res) => res.status(200).send({
  message: 'Welcome to the api root.',
}));

module.exports = app;

Next, we open up Postman and issue a POST request to create a new user as in the image below.

If you make a GET request to /api using either Postman or your browser, you should see the welcome message we specified in our routes.

Conclusion

With everything set in place, you should now be able to work well with Sequelize and ApsaraDB RDS for PostgreSQL. You can go ahead and create more API routes and see how far you go.

In case you need reference to the source code, you can find it on my Bitbucket

Community

Creating a Node.js Sequelize App PostgreSQL on Alibaba Cloud

Prerequisites

Step 1: Install Node, npm and nvm

Step 2: Setting up Minimal Express Application

Why Express?

Step 3: Enable Server Restart on File Changes

Step 4: Connecting to ApsaraDB RDS for PostgreSQL

Step 5: Sequelize Setup

Step 6: Setting up a Connection

Step 7: Generating Models

Step 8: Creating Controllers and Routing

Conclusion

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

ECS(Elastic Compute Service)

OSS(Object Storage Service)

ApsaraDB RDS for PostgreSQL