Let’s build a CRUD app with Node (v4.1.1), Express (v4.13.1), Sequelize (v3.12.2), and PostgreSQL (9.4.4).

Updates:

• November 1st, 2015 — Added Database Migrations

This a follow-up to PostgreSQL and NodeJS.

Contents

• Getting Started
• Sequelize
• Migrations
• CRUD
• Conclusion

Getting Started

Grab the initial boilerplate and install the dependencies:

$ git clone git@github.com:mjhea0/node-postgres-sequelize.git
$ git checkout tags/v1
$ npm install

Now run a quick sanity check:

If all went well, a new browser window should have opened to http://localhost:5000/ and you should see the “Welcome to Express.” text.

Sequelize

With Postgres listening on port 5432, we can make a connection to it using the Sequelize library, an Object Relational Mapper (ORM), written in JavaScript, which supports MySQL, PostgreSQL, SQLite, and MariaDB.

Need to set up Postgres? On a Mac? Check out Postgres.app.

Install Sequelize, pg (for making the database connection), and pg-hstore (for serializing and deserializing JSON into the Postgres hstore key/value pair format):

$ npm install sequelize@3.12.2 pg@4.4.3 pg-hstore@2.3.2 —save

Migrations

The Sequelize CLI is used to bootstrap a new project and handle database migrations directly from the terminal.

Read more about the Sequelize CLI from the official documentation.

Init

Start by installing the package:

$ npm install sequelize-cli@2.1.0 —save

Next, create a config file called .sequelizerc in your project root to specify the paths to specific files required by Sequelize:

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

Now, run the init command to create the files (config.json) and folders (“migrations”, “models”, and “seeders”):

$ node_modules/.bin/sequelize init

Take a look at the index.js file within the “models” directory:

'use strict'; var fs = require('fs');
var path = require('path');
var Sequelize = require('sequelize');
var basename = path.basename(module.filename);
var env = process.env.NODE_ENV || 'development';
var config = require(__dirname + '/../config.json')[env];
var db = {}; if (config.use_env_variable) { var sequelize = new Sequelize(process.env[config.use_env_variable]);
} else { var sequelize = new Sequelize(config.database, config.username, config.password, config);
} fs .readdirSync(__dirname) .filter(function(file) { return (file.indexOf('.') !== 0) && (file !== basename); }) .forEach(function(file) { if (file.slice(-3) !== '.js') return; var model = sequelize['import'](path.join(__dirname, file)); db[model.name] = model; }); Object.keys(db).forEach(function(modelName) { if (db[modelName].associate) { db[modelName].associate(db); }
}); db.sequelize = sequelize;
db.Sequelize = Sequelize; module.exports = db;

Here, we establish a connection to the database, grab all the model files from the current directory, add them to the db object, and apply any relations between each model (if any).

Config

Be sure to also update the config.js file for your development, test, and production databases:

{ «development»: { «username»: «update me», «password»: «update me», «database»: «todos», «host»: «127.0.0.1», «port»: «5432», «dialect»: «postgres» }, «test»: { «username»: «update me», «password»: «update me», «database»: «update_me», «host»: «update me», «dialect»: «update me» }, «production»: { «username»: «update me», «password»: «update me», «database»: «update me», «host»: «update me», «dialect»: «update me» }
}

If you are just running this locally, using the basic development server, then just update the development config.

Go ahead and create a database named “todos”.

Create Migration

Now let’s create a model along with a migration. Since we’re working with todos, run the following command:

$ node_modules/.bin/sequelize model:create —name Todo —attributes «title:string, complete:boolean,UserId:integer»

Take a look a the newly created model file, todo.js in the models directory:

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

The corresponding migration file can be found in the “migrations” folder. Take a look. Next, let’s associate a user to a todo. First, we need to define a new migration:

$ node_modules/.bin/sequelize model:create —name User —attributes «email:string»

Now we need to set up the relationship between the two models…

Associations

To associate the models (one user can have many todos), make the following updates…

todo.js:

'use strict';
module.exports = function(sequelize, DataTypes) { var Todo = sequelize.define('Todo', { title: DataTypes.STRING, complete: DataTypes.BOOLEAN }, { classMethods: { associate: function(models) { Todo.belongsTo(models.User); } } }); return Todo;
};

user.js:

'use strict';
module.exports = function(sequelize, DataTypes) { var User = sequelize.define('User', { email: DataTypes.STRING }, { classMethods: { associate: function(models) { User.hasMany(models.Todo); } } }); return User;
};

Sync

Finally, before we sync, let’s add an additional attribute to the complete field in the todo.js file:

'use strict';
module.exports = function(sequelize, DataTypes) { var Todo = sequelize.define('Todo', { title: DataTypes.STRING, complete: { type: DataTypes.BOOLEAN, defaultValue: false } }, { classMethods: { associate: function(models) { Todo.belongsTo(models.User); } } }); return Todo;
};

Run the migration to create the tables:

$ node_modules/.bin/sequelize db:migrate Sequelize [Node: 4.1.1, CLI: 2.1.0, ORM: 3.12.2] Loaded configuration file «server/config.json».
Using environment «development».
Using gulpfile ~/node_modules/sequelize-cli/lib/gulpfile.js
Starting 'db:migrate'…
== 20151101052127-create-todo: migrating =======
== 20151101052127-create-todo: migrated (0.049s) == 20151101052148-create-user: migrating =======
== 20151101052148-create-user: migrated (0.042s)

CRUD

With Sequelize set up and the models defined, we can now set up our RESTful routing structure for the todo resource. First, within index.js in the “routes” folder add the following requirement:

var models = require('../models/index');

Then add a route for creating a new user:

router.post('/users', function(req, res) { models.User.create({ email: req.body.email }).then(function(user) { res.json(user); });
});

To add a new user, run the server — gulp — and then run the following in a new terminal window:

$ curl —data «email=michael@mherman.org» http://127.0.0.1:3000/users

You should see:

{ «id»:1, «email»:»michael@mherman.org», «updatedAt»:»2015-11-01T12:24:20.375Z», «createdAt»:»2015-11-01T12:24:20.375Z»
}

Now we can add the todo routes…

GET all todos

// get all todos
router.get('/todos', function(req, res) { models.Todo.findAll({}).then(function(todos) { res.json(todos); });
});

When you hit that route you should see an empty array since we have not added any todos. Let’s do that now.

POST

// add new todo
router.post('/todos', function(req, res) { models.Todo.create({ title: req.body.title, UserId: req.body.user_id }).then(function(todo) { res.json(todo); });
});

Now let’s test:

$ curl —data «title=test&user_id=1» http://127.0.0.1:3000/todos
$ curl —data «title=test2&user_id=1» http://127.0.0.1:3000/todos

Then if you go back and hit http://127.0.0.1:3000/todos in our browser, you should see:

[ { id: 1, title: «test», complete: false, createdAt: «2015-11-01T12:31:56.451Z», updatedAt: «2015-11-01T12:31:56.451Z», UserId: 1 }, { id: 2, title: «test2», complete: false, createdAt: «2015-11-01T12:32:09.000Z», updatedAt: «2015-11-01T12:32:09.000Z», UserId: 1 }
]

GET single todo

How about getting a single todo?

// get single todo
router.get('/todo/:id', function(req, res) { models.Todo.find({ where: { id: req.params.id } }).then(function(todo) { res.json(todo); });
});

Navigate to http://localhost:3000/todo/1 in your browser. You should the single todo.

PUT

Need to update a todo?

// update single todo
router.put('/todo/:id', function(req, res) { models.Todo.find({ where: { id: req.params.id } }).then(function(todo) { if(todo){ todo.updateAttributes({ title: req.body.title, complete: req.body.complete }).then(function(todo) { res.send(todo); }); } });
});

And now for a test, of course:

$ curl -X PUT —data «complete=true» http://127.0.0.1:3000/todo/2

DELETE

Want to delete a todo?

// delete a single todo
router.delete('/todo/:id', function(req, res) { models.Todo.destroy({ where: { id: req.params.id } }).then(function(todo) { res.json(todo); });
});

Test:

$ curl -X DELETE http://127.0.0.1:3000/todo/1

Again, navigate to http://localhost:3000/todos in your browser. You should now only see one todo.

Conclusion

That’s it for the basic server-side code. You now have a database, models, and migrations set up. Whenever you want to update the state of your database, just add additional migrations and then run them as necessary.

Grab the code from the Github repo. Comment below with questions. Cheers!

Getting Started with Sequelize and Postgres

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

An ORM library is a completely ordinary library written in your language of choice that encapsulates the code needed to manipulate the data, so you don't use SQL anymore; you interact directly with an object in the same language you're using -stackoverflow

Though Sequelize supports several other database clients, this article focuses on using Sequelize with Postgres. We will be building a simple blog where users can create posts, view posts and add comments to a post.

This article is divided into two parts:

• Part One — Sequelize setup, configuration, migration and seeding.
• Part Two — Performing CRUD with Sequelize.

Requirements

• NodeJs installed
• npm or yarn installed

Part One

If you have not created an express project, quickly do so and open the project in a terminal. At the root folder of the project, run the following commands to install the necessary dependencies:

npm install sequelize sequelize-cli pg pg-hstore

• A brief explanation of the libraries installed in the command above:
• sequelize is the sequelize library itself.
• sequelize-cli is a package that enables us interact with the database through sequelize from the CLI.
• pg short for postgres is a Postgres client for Node.js
• pg-hstore is a node package for serializing and deserializing JSON data to hstore format.
• Next up, let's create sequelize config, to do that, run command below to create a file called .sequelizerc
• Copy the code below into the .seqluelizerc file:

const path = require('path') module.exports = { config: path.resolve('./database/config', 'config.js'), 'models-path': path.resolve('./database/models'), 'seeders-path': path.resolve('./database/seeders'), 'migrations-path': path.resolve('./database/migrations'),
}

Sequelize uses the .sequelizerc file to generate the config and the model using the specified path.

1. Next up, we generate the config by running the command below:
2. Now, you should have a new directory called database with a scaffolding as shown below:

Next up, let's edit the database/config/config.js.
Replace the content of the database/config/config.js with the code below:

require('dotenv').config() module.exports = { development: { url: process.env.DEV_DATABASE_URL, dialect: 'postgres', }, test: { url: process.env.TEST_DATABASE_URL, dialect: 'postgres', }, production: { url: process.env.DATABASE_URL, dialect: 'postgres', },
}

Since our focus is on using Sequelize with Postgres, we've streamlined the config to be Postgres specific.

Since we are using Postgres, we'll be using connection string to connect to the database. Create two Postgres databases one for development and one for test (if you need test for your project).

How to create a Postgres database connection string

• You can jump to the next part if you've already created database.
• Method 1
• If you have Postgres installed locally follow the steps below to create a database and generate connection string. Open a terminal and run the command below:

createdb dev_db -U
createdb test_db -U

The connection strings for the databases above will be:

postgres://:@127.0.0.1:5432/dev_db
postgres://:@127.0.0.1:5432/test_db

Now, create a .env file and copy the snippet below into it.

DATABASE_URL=
DEV_DATABASE_URL=postgres://:@127.0.0.1:5432/dev_db
TEST_DATABASE_URL=postgres://:@127.0.0.1:5432/test_db

1. Note that if you're using Heroku for production, Heroku will generate a connection string and inject into the environment variable DATABASE_URL once you add the Postgres add-on.
2. Method 2
3. If you don't have Postgres installed locally, you can make use of ElephantSQL to create the databases.

Creating Models and Migrations

We need to create the User, Post and Comment models. To do that run the following commands:

sequelize model:generate —name User —attributes name:string,email:string

sequelize model:generate —name Post —attributes title:string,content:text,userId:integer

sequelize model:generate —name Comment —attributes postId:integer,comment:text,userId:integer

• Each of the commands above will generate a migration and a model in /database/migrations and database/models directory respectively.
• Note, ensure there's no space between —attributes definition.
• For example, —attributes postId:integer, comment:text, userId:integer will throw an error ERROR: Attribute '' cannot be parsed: Cannot read property 'dataType' of undefined because of the whitespace between attributes.

Next up, we need to make a few changes on the migrations and models.
First we need to add NOT NULL constraints to the FOREIGN_KEY attributes (userId, postId). The first time I worked with Sequelize, I didn't know about this and the model eager loading wasn't working. In the migrations edit the FOREIGN_KEY attributes as shown below:

userId: { type: Sequelize.INTEGER, allowNull: false, }, postId: { type: Sequelize.INTEGER, allowNull: false, },

Edit the models/index.js file as follows:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const envConfigs = require('../config/config'); const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = envConfigs[env];
const db = {}; let sequelize;
if (config.url) { sequelize = new Sequelize(config.url, config);
} else { sequelize = new Sequelize(config.database, config.username, config.password, config);
} fs .readdirSync(__dirname) .filter(file => { return (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; //models/index.js

Defining the model relationships

We have three models that are interrelated as follows

• a user has many posts and a post belongs to a user (1:n)
• a user has many comments and a comment belongs to a user (1:n)
• a post has many comments and a comment belongs to a post (1:n)

To achieve establish the relationships above programmatically, let's edit the models as follows:

module.exports = (sequelize, DataTypes) => { const User = sequelize.define('User', { name: DataTypes.STRING, email: DataTypes.STRING }, {}); User.associate = function(models) { // associations can be defined here User.hasMany(models.Post, { foreignKey: 'userId', as: 'posts', onDelete: 'CASCADE', }); User.hasMany(models.Comment, { foreignKey: 'userId', as: 'comments', onDelete: 'CASCADE', }); }; return User;
}; // database/models/user.js

module.exports = (sequelize, DataTypes) => { const Post = sequelize.define('Post', { title: DataTypes.STRING, content: DataTypes.TEXT, userId: DataTypes.INTEGER }, {}); Post.associate = function(models) { // associations can be defined here Post.hasMany(models.Comment, { foreignKey: 'postId', as: 'comments', onDelete: 'CASCADE', }); Post.belongsTo(models.User, { foreignKey: 'userId', as: 'author', onDelete: 'CASCADE', }) }; return Post;
}; // database/models/post.js

module.exports = (sequelize, DataTypes) => { const Comment = sequelize.define('Comment', { postId: DataTypes.INTEGER, comment: DataTypes.TEXT, userId: DataTypes.INTEGER }, {}); Comment.associate = function(models) { // associations can be defined here Comment.belongsTo(models.User, { foreignKey: 'userId', as: 'author' }); Comment.belongsTo(models.Post, { foreignKey: 'postId', as: 'post' }); }; return Comment;
}; // database/models/comment.js

It's time to run the migrations which will translate the migrations into tables in the database. Run

If everything went well, the tables would be generated and we are ready to start shoving data into the database.

Seeding data to the database

Let's populate the database with some dummy data. Run the commands below to generate the seed files for the models.

sequelize seed:generate —name User

sequelize seed:generate —name Post

sequelize seed:generate —name Comment

The commands above will generate three files xxxx-User.js, xxxx-Post.js, and xxxx-Comment.js for User, Post and Comment models respectively.

Edit the seed files as follows:

module.exports = { up: (queryInterface, Sequelize) => queryInterface.bulkInsert( 'Users', [ { name: 'Jane Doe', email: 'janedoe@example.com', createdAt: new Date(), updatedAt: new Date(), }, { name: 'Jon Doe', email: 'jondoe@example.com', createdAt: new Date(), updatedAt: new Date(), }, ], {}, ), down: (queryInterface, Sequelize) => queryInterface.bulkDelete('Users', null, {}),
}; // database/seeds/xxxx-User.js

module.exports = { up: (queryInterface, Sequelize) => queryInterface.bulkInsert( «Posts», [ { userId: 1, title: «hispotan de nu», content: «Nulla mollis molestie lorem. Quisque ut erat. Curabitur gravida nisi at nibh.», createdAt: new Date(), updatedAt: new Date() }, { userId: 2, title: 'some dummy title', content: «Maecenas tincidunt lacus at velit. Vivamus vel nulla eget eros elementum pellentesque. Quisque porta volutpat erat.», createdAt: new Date(), updatedAt: new Date() } ], {} ), down: (queryInterface, Sequelize) => queryInterface.bulkDelete(«Posts», null, {})
};
// database/seeds/xxxx-Post.js

module.exports = { up: (queryInterface, Sequelize) => queryInterface.bulkInsert( «Comments», [ { userId: 1, postId: 2, comment: «Nulla mollis molestie lorem. Quisque ut erat. Curabitur gravida nisi at nibh.», createdAt: new Date(), updatedAt: new Date() }, { userId: 2, postId: 1, comment: «Maecenas tincidunt lacus at velit. Vivamus vel nulla eget eros elementum pellentesque. Quisque porta volutpat erat.», createdAt: new Date(), updatedAt: new Date() } ], {} ), down: (queryInterface, Sequelize) => queryInterface.bulkDelete(«Comments», null, {})
}; // database/seeds/xxxx-Comment.js

Now, run the command below to seed the database:

You can clone the complete code for this article here

Yo! that is it for now. In the part two of this article, I will be building on this article to implement CRUD for the blog. Stay tuned! ????

Suggested resources

• Sequelize Docs
• What is an ORM and Why You Should Use it

This article was originally published on my blog

Adaeze N. Festus — Oct 10

Once suspended, nedsoft will not be able to comment or publish posts until their suspension is removed.

Once unsuspended, nedsoft will be able to comment and publish posts again.

1. Once unpublished, all posts by nedsoft will become hidden and only accessible to themselves.
2. If nedsoft is not suspended, they can still re-publish their posts from their dashboard.
3. Note:

• Thanks for keeping DEV Community safe. Here is what you can do to flag nedsoft:
• Make all posts by nedsoft less visible
• nedsoft consistently posts content that violates DEV Community's code of conduct because it is harassing, offensive or spammy.

Report other inappropriate conduct

Unflagging nedsoft will restore default visibility to their posts.

How to Use PostgreSQL With Sequelize in Node.js

Sequelize is a Node.js object-relational mapper (ORM) that provides an easy-to-use API for relational databases such as Oracle, PostgreSQL, MySQL, MariaDB, SQLite, and more.

PostgreSQL is a widely-used open-source relational database management system (RDBMS) renowned for its extensive features, excellent security, and scalability.

PostgreSQL is compatible with multiple programming languages, including JavaScript, making it an ideal choice for web and data-driven applications. Here you will learn to set up a Postgres database in Node.js using Sequelize.

Step 1: Installing Dependencies

Before you can use PostgreSQL, you need to install it on your system. To download PostgreSQL, navigate to the PostgreSQL website and choose your operating system. If you run into issues during this process on macOS, check out installing PostgreSQL on macOS.

Alternatively, you can use PostgreSQL on the cloud by leveraging platforms such as ElephantSQL that offer PostgreSQL as a service.

Next, install sequelize by running the following command:

npm install sequelize

Then, install the PostgreSQL database driver by running the command below:

npm install pg pg-hstore

In your project’s source directory, create a config folder.

In the config folder, create a db.js file.

This file will contain all the code connecting your Node.js application to a PostgreSQL database.

Next, in your db.js file, import Sequelize from sequelize.

const { Sequelize } = require(«sequelize»);

Next, you need to create a Sequelize instance. This instance takes connection parameters such as the database name, username, and password as arguments. Alternatively, it takes a single connection URI as an argument.

For example:

const sequelize = new Sequelize('database', 'username', 'password')

const sequelize = new Sequelize(process.env.POSTGRESQL_DB_URI)

Additionally, this constructor takes a configuration object as an optional argument.

Next, test your sequelize connection by adding the code below your db.js file:

const testDbConnection = async () => {  try {    await sequelize.authenticate();    console.log(«Connection has been established successfully.»);  } catch (error) {    console.error(«Unable to connect to the database:», error);  }};

The code block above calls the sequelize.

authenticate() method to test if the connection is OK and prints «Connection has been established successfully.» to console if it is. If there's a connection error, it prints «Unable to connect to the database:” along with the error.

Finally, export the sequelize instance and the testDbConnection function.

module.exports = { sq: sequelize, testDbConnection };

In your project’s source directory, create a models folder. This folder will contain all your sequelize models. Next, create a model file. The name you give the file should provide information about the model's representation.

In your model file, import the sequelize instance.

const { sq } = require(«../config/db»);

Next, import DataTypes from sequelize.

const { DataTypes } = require(«sequelize»);

DataTypes lets you set the required data type for each property on your model.

You can create a new sequelize model representing a table in your database by calling the define method on your sequelize instance.

The define method takes two arguments: The model name and an attributes object. The model name represents the name of the model. The attributes object represents the columns of the database, with each property representing a column.

Here’s an example of a sequelize model:

const User = sq.define(«user», {  email: {    type: DataTypes.STRING,

    allowNull: false,

    primaryKey: true,  },  fullName: {    type: DataTypes.STRING,  },    age: {    type: DataTypes.INTEGER,  },  employed: {    type: DataTypes.BOOLEAN,

    defaultValue: false,

  },});

The code block above defines a User model with an email, fullName, age, and employed properties.

The email property is a string type that cannot be empty (allowNull: false) and also acts as the primary key for the User table (primaryKey: true). The fullName and the age property are a string (DataTypes.STRING) and integer type (DataTypes.INTEGER), respectively. The employed property is a boolean type with a default value of false, which means that if it is not specified, it'll be automatically set to false in the database.

Next, call the sync method on your model. This method takes a configuration object as an argument. Depending on the options in the configuration object, the sync method can:

• User.sync(): Create the table if it doesn't exist and does nothing if it exists.
• User.sync({ force: true }): Create the table and drop the first if it already exists.
• User.sync({ alter: true }): Check the current state of the table in the database and perform necessary changes in the table to make it match the model.

For example:

User.sync().then(() => {  console.log(«User Model synced»);});

The code block above creates the database table for the User model if it doesn't exist and does nothing if it exists.

Finally, export your model:

module.exports = User;

Sequelize provides various methods that allow you to interact with your PostgreSQL database without writing raw SQL commands.

Saving Data to PostgreSQL

To save data to a PostgreSQL database, call the create method on your model and pass an object that implements the PostgreSQL model as an argument.

For example:

const mike = User.create({  email: «mike@example.com»,  fullName: «Mike Smith»,  age: 30,  employed: true,});

The code block above creates a User instance of mike in your database and autogenerates a unique id.

Retrieving Data From PostgreSQL

Data can be retrieved in several ways from a PostgreSQL database using sequelize, but it depends on the nature of the data you want to receive.

The most common methods to retrieve data are the findOne and findAll methods.

The findAll returns all data instances that satisfy a given query, while findOne returns the first instance that satisfies the query.

For example:

const users = await User.findAll();

The above code will return all the User instances in the database.

You can filter through the returned data using the where statement. This statement allows you to add certain conditions to the query. Your query will only return instances that meet those conditions.

For example:

User.findAll({  where: {

    employed: false

  }});

The above code will return all the User instances with their employed property set to false in the database.

Updating Data on PostgreSQL

You can update data on the PostgreSQL database by calling the update method and passing a filter as an argument.

For example:

await User.update({ employed: true }, {  where: {

    employed: false

  }});

• The above code changes all the User instances with an employed value of false to true.
• Alternatively, you can update data by manually modifying the properties you want to change and calling the save method on the instance.
• For example:

const userMike = await User.findOne({ where: { email: «mike@example.com» } });

if(userMike !== null) {

  userMike.email = «mike@example.org»  await userMike.save()}

The above code queries the database for a user having the email “mike@example.

com” using the findOne method.

If the user is found, it reassigns the email property to “mike@example.org” and calls the save method to update the database.

Deleting Data on PostgreSQL

You can delete data on PostgreSQL by calling the destroy method and passing a filter as an argument.

For example:

await User.destroy({  where: {

    email: «mike@example.org»

  }});

The above code queries the database for a user having the email “mike@example.

org” and deletes the user from the database.

Writing raw SQL queries to interact with a PostgreSQL database can be a hassle. With Sequelize, you can easily define models, create associations between them, and query the database with a simple and intuitive API. Alternatively, you can use other ORMs, such as TypeORM, to interact with a PostgreSQL database without the hassle of writing raw SQL queries.

Setup PostgreSQL with Sequelize in Express

Eventually every Node.js project running with Express.js as web application will need a database. Since most server applications are stateless, in order to scale them horizontally with multiple server instances, there is no way to persist data without another third-party (e.g. database).

That's why it is fine to develop an initial application with sample data, where it is possible to read and write data without a database, but at some point you want to introduce a database to manage the data.

The database would keep the data persistence across servers or even though one of your servers is not running.

The following sections will show you how to connect your Express application to a PostgreSQL database with Sequelize as ORM.

If you haven't installed PostgreSQL on your machine yet, head over to this guide on how to install PostgreSQL for your machine. It comes with a MacOS and a Windows setup guide.

Afterward come back to the next section of this guide to learn more about using PostgreSQL in Express.

PostgreSQL with Sequelize in Express Installation

To connect PostgreSQL to your Express application, we will use an ORM to convert information from the database to a JavaScript application without SQL statements. ORM is short for Object Related Mapping, a technique that programmers use to convert data among incompatible types.

More specifically, ORMs mimic the actual database so a developer can operate within a programming language (e.g. JavaScript) without using a database query language (e.g. SQL) to interact with the database.

The downside is the extra code abstraction, that's why there are developers who advocate against an ORM, but this shouldn't be a problem for many JavaScript applications without complex database queries.

For this application, we'll use Sequelize as ORM, as it supports multiple dialects, one of which is PostgreSQL. Sequelize provides a comfortable API to work with PostgreSQL databases from setup to execution, but there are many ORMs (e.g. TypeORM, Objection.js) to choose from for a Node.js application if you want to expand your toolbelt.

Before you can implement database usage in your Node.js application, install sequelize and pg, which is the postgres client for Node.js, on the command line for your Node.js application:

npm install pg sequelize —save

After you have installed both libraries as node packages, we'll plan and implement our database entities with models and schemas.

Database Models, Schemas and Entities

The following case implements a database for your application with two database entities: User and Message. Often a database entity is called database schema or database model as well. You can distinguish them the following way:

Sequelize

Последнее обновление: 02.05.2019

Sequelize — это ORM-библиотека для приложений на Node.js, которая осуществляет сопоставление таблиц в бд и отношений между ними с классами. При использовании Sequelize мы можем не писать SQL-запросы, а работать с данными как с обычными объектами. Причем Sequelize может работать с рядом СУБД — MySQL, Postgres, MariaDB, SQLite, MS SQL Server.

Более подробно про работу sequelize можно посмотреть в официальной документации. А в данном случае мы сосредаточимся на основных моментах работы с данными на примере взаимодействия с БД MySQL.

Вначале установим пакет sequelize.

npm install —save sequelize

Подключение к базе данных

Для подключения к базе данных прежде всего необходимо создать объект Sequelize:

const Sequelize = require(«sequelize»);
const sequelize = new Sequelize(«usersdb2», «root», «123456», {
dialect: «mysql»,
host: «localhost»
});

Для создания объекта sequelize используется функция Sequelize, которая принимает ряд параметров. Первый параметр — имя базы данных. Второй параметр — логин к бд, третий параметр — пароль. Это обязательные параметры.

Кроме того, с помощью четвертого параметра мы можем задать ряд дополнительных опций конфигурации. Четвертый параметр представляет объект, который имеет множество свойств. В данном случае используются только два.

Первое свойство — dialect указывает на диалект языка SQL, который используется в запросах — в данном случае «mysql». Второе свойство host представляет адрес, по которому запущен сервер.

По умолчанию host имеет значение «localhost», поэтому для подключения к локальной базе данных это свойство в принципе можно не указывать.

Для свойства dialect в зависимости от того, с какой СУБД предстоит работать, могут использоваться следующие значения:

• mysql
• mariadb
• sqlite
• postgres
• mssql

Подключение к mysql

Для работы с MySQL также должен быть установлен пакет mysql2, работа с котором разбиралась в прошлой главе:

npm install —save mysql2

И при создании объекта Sequelize в параметрах указывается dialect: «mysql»:

const Sequelize = require(«sequelize»);
const sequelize = new Sequelize(«usersdb2», «root», «123456», {
dialect: «mysql»,
host: «localhost»
});

Подключение к mssql

Для работы с MS SQL Server в проекте должен быть установлен пакет tedious:

npm install —save tedious

И при создании объекта Sequelize в параметрах указывается dialect: «mssql»:

const Sequelize = require(«sequelize»);
const sequelize = new Sequelize(«usersdb2», «sa», «123456», {
dialect: «mssql»,
host: «localhost»,
port: «1433»
});

Кроме диалекта в настройках конфигурации также указывается параметр host — адрес сервера и port. Если сервер запущен на порту 1433, то в принципе его необязательно указывать, так как этот порт Sequelize использует по умолчанию. Любой другой порт необходимо указывать.

При создании подключения к MSSQL Server мы можем столкнуться с рядом сложностей, которые вытекают в основном из конфигурации самого сервера. Прежде всего, нам надо убедиться, что запущенный экземпляр сервера принимает запросы по TCP. Для этого перейдем в панели управления к пункту Администрирование и затем выберем пункт Управление компьютером

В открывшейся вкладке перейдем к пункту Службы и приложения -> SQL Server Configuration Manager -> SQL Server Network Configuration и найдем нужный нам экземпляр сервера. Убедимся, что для включен параметр TCP/IP (то есть имеет значение «Enabled»).

Для использования логина и пароля в MS SQL Server убедимся, что он настроен на смешанную аутентификацию. Для этого подключимся к серверу через SQL Server Managemen Studio и перейдем к свойствам сервера:

Затем в открывшемся окне на вкладке Security выбрем режим SQL Server and Windows Authentication mode.

После применения всех настроек перезапустим сервер.

И естественно перед подключением необходимо создать базу данных, с которой будет идти взаимодействие.

Подключение к postgres

Для работы с СУБД Postgres также необходимо установить пакет pg:

И при создании объекта Sequelize в параметрах указывается dialect: «postgres»:

const Sequelize = require(«sequelize»);
const sequelize = new Sequelize(«usersdb2», «root», «123456», {
dialect: «postgres»
});

Все остальное взаимодействие с базой данных в большинстве случаев за некоторыми исключениями практически не зависит от конкретной СУБД. А это значит, что большую часть кода (если не весь код) мы можем использовать для подключения к разным СУБД.