# Node, Postgres, and Sequelize

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

This a follow-up to PostgreSQL and NodeJS.

## Getting Started

Grab the initial boilerplate and install the dependencies:

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):

## Migrations

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

### Init

Start by installing the package:

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

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

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

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:

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:

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

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:

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:

user.js:

### Sync

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

Run the migration to create the tables:

## 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:

Then add a route for creating a new user:

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

You should see:

Now we can add the todo routes…

### GET all 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

Now let’s test:

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

### GET single todo

How about getting a single todo?

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

### PUT

Need to update a todo?

And now for a test, of course:

### DELETE

Want to delete a todo?

Test:

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!