Databases, DevOps, Software development

JS on Backend in 2018. Tutorial. Part 2. Adding a database and Sequelize ORM.

After the previous post, our application server works, can receive requests and even return some kind of responses, which is already great and you can go into production with it! But probably it would make sense to add some more functionality to it. And in this post, we will integrate a database and implement a RESTful API.

As a database, I picked PostgreSQL, because for the app I’m building SQL solution is more suitable than NoSQL and among others, PostgreSQL is the best choice by mine opinion. Take it with a grain of salt, because your cases could be different and f.e. MongoDB is almost standard de-facto for Node.js apps. And again, don’t take Mongo just because it’s an almost standard de-facto 🙂

Basically, when you want to use a database for your application you always have the choice: ORM vs plain adapter. If you are the type of person, who wants to build all the repository/entity structure by his own – you probably would choose Adapter approach, and for you, there is a solution: https://github.com/vitaly-t/pg-promise. This library not just a plain adapter, but makes it easier to connect to a database and provides a promise-based interface for queries. But for most cases picking the right ORM is the way to go. And I found that Sequelize is really useful ORM. It provides you comprehensive query API, migrations and even basic generators, which could be useful at the start (btw don’t misuse them). Just to mention, Sequelize also supports managing read replicas, which can be quite useful if you develop your app database layer with a distributed nature.

Let’s start with adding it as a dependency.

npm install sequelize --savenpm install --save pg pg-hstore

First command will install the Sequelize package and the second one will add you PostgreSQL provider and HStore package, which is necessary if you would want to use hstore data type in postgres. If you’re not familiar with this data type – you can check it here: https://www.postgresql.org/docs/current/static/hstore.html. It is a data type, which gives you kind of a schemaless data format.

Next we can setup everything manually, or use the generators! I will give a preference to generators in this case, but will explain you everything step by step. First, let’s install the command line utility from Sequelize:

here we have only development section, which means we don’t have any config for test or production. I believe it makes sense to create them only when you’ll need them.

username is a name of your database user, you plan to access with.

password is obviously his password, which is empty for me.

database is the database name. If you don’t have it yet – no worries, we’ll create it in the next step.

host is your localhost (127.0.0.1). At least for development environment.

dialect can be one of postgres, mysql, sqlite or mssql. So for us it should be postgres.

This command will create the database with the name you specified above:

node_modules/.bin/sequelize db:create

If you already have it – you’ll get an error.

Now when you have everything configured, it’s time to create your first model! Wiki quote:

“The model is the central component of the <…MVC…> pattern. It expresses the application’s behavior in terms of the problem domain, independent of the user interface. It directly manages the data, logic and rules of the application.”

The command should be clear for you if you came from any of plenty MVC frameworks. Here we generate a model, named Book and having an attribute title with a type of string. At the same time, this command creates a migration for us, which then can be executed by:

node_modules/.bin/sequelize db:migrate

and the database table books will be added as well.

Let’s take a look at what did these commands created for us. First thing is the migration file.

It is using the createTable command of Sequelize to create a table “Books” with set of default parameters (auto incrementable ID, and auto settable Updated At and Created At) and your specified attribute, “title”.
You may also notice that it has up and down blocks. The first one specifies what will happen when you execute the migration, the second one, what will happen if you “rollback” the migration (db:rollback task). And on your migration filename, you will notice a long prefix-number, which is a unique timestamp of your migration, which is needed to distinguish the order of migrations.

I found this to be quite enough for one post because our next topic will require some explanation as well and I don’t want to have one huge post, which none of you will be able to finish 🙂
BTW, I promise you to publish it asap.