September 1, 2020
Mickey Martinez
_urlIf you’ve landed here, hopefully you’re looking for help getting data flowing from a Node.js and Express web application, to a PostgreSQL database with Sequelize as the O.R.M. This guide will assist you with setting up a Node.js project, and installing Sequelize to allow for object relational database mapping between a Postgres database.
The simplest way to think of Object Relational Mapping is as a process for accessing a relational database — Postgres in our instance — from an object-oriented language like JavaScript. Sequelize allows us to interact with a Postgres database using JavaScript instead of SQL.
“Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.” — sequelize.org
Before we get moving, let me lay out the tools we’ll be using. This way you have a high-level sense of what tools you should be knowledgeable of, and what you can expect to extract from this guide.
Project Dependencies
To have the most success using this guide you should have a general command of JavaScript. You also need to have Node.js and PostgreSQL installed on your machine.
💡 Postgres and PostgreSQL are the same thing. This confused me at first, but it’s just industry semantics — although PostgreSQL is the official name.
While my goal is to help you configure Sequelize with Postgres, in the process we will be building a RESTful API. Once we have data flowing properly, you can expand on this app’s build however you’d like — adding more routes or developing the front-end.
Those of us who have only dabbled with the back-end, may have done a few tutorials using a non-relational database like MongoDB. And I can attest I’ve learned a ton by using MongoDB with Node.js — seeing data flowing and testing endpoints with Insomnia.
I quickly realized when landing my first engineering gig that it’s imperative to command knowledge of relational DBs (databases). Postgres itself is an “object-relational database system that uses and extends the SQL language,” according to the official website. Its language is designed to perform CRUD (Create, Read, Update, Delete) operations on a database, it scales vertically and can perform complex queries across multiple tables of data.
Without Sequelize, we’d have to execute SQL statements in order for us to interact with Postgres and perform CRUD on our databases. Sequelize handles this translation for us, allowing us to interact with a Postgres database using JavaScript instead of SQL.
Now that we have some context established, let’s get to the code!
For this guide, I’ll be using yarn 🧶. The official Sequelize documentation uses npx. We'll create yarn scripts along the way for future developers to join the project. For a full working example, click here (Link to spike with yarn scripts).
Let’s start by creating a new directory for our project, and setting it as our working directory.
Install the dependencies for this project by running the commands:
From the terminal, now open your project in VS Code.
💡 Sequelize can use a full DB_URL string, other config options can be found here.
🧰 Optional step if pushing to GitHub 🧰
🧰 — — — — — — — — — — — — — —— — — — — — — — — — — — — — — 🧰
💡 This set up below mimics what Express refers to as the “simplest app you can create.” Read more here.
💡 If you’re using VS Code, you can type control + ` on your keyboard to open the terminal and run the command from there.
Mission control, we have liftoff 🚀 — everything is working as expected! Our app is listening 👂🏼 for connections at http://localhost:3000. If you follow that link and open a new webpage, you should see the ‘Hello World!’ message.
Let’s install our remaining project dependencies and transition to Sequelize and Postgres.
💡 To kill your server’s connection and input commands again, open the terminal the server is running on and press control + c on your keyboard.
Install Postgres — our database of choice — and Sequelize
Install Sequelize CLI for command-line interface migrations and project bootstrapping.
🥳 Celebrate, you’re halfway there! Here’s how your dependencies should look at this step:
This will automatically create the following folders within your root directory:
Now that we have a new Sequelize project created, let’s get it configured correctly.
🤹🏻♂ Optional step — It’s good practice to rename the databases, replacing the default names with unique enough titles that will avoid future confusion between your databases. I’ll be using:
💡 For a production configuration you may need to update with SSL and other config options found here.
We’ve done all the hard work to properly configure our database, so let’s give Sequelize the O.K. to create it.
In your terminal, run the following command:
Boooom 💣💥 ….. We’ve got some legendary users in our database!!
Once you’re finished updating your migration file, you should migrate the database again.
If you rode the wave 🌊🏄🏻♂️ to this point, then you’ve certainly expanded your developer toolkit! Take a moment and let all this settle in, we’ve covered a lot of information.
You’ve created a functional RESTful API using Node.js and Express, which can transmit data to a Postgres database by utilizing Sequelize to translate JavaScript into equatable SQL commands.
From here, maybe you create more tables to really see how relational DB’s work, or build out pages that have forms gathering or verifying data. Imagine a form for creating a new account and another for logging in, that way you’ll be able to test the two-way process of storing and accessing data.
I wrote this guide to be verbose, because I struggled to locate documentation that put all the pieces together in a way I understood. My hope is that this guide benefits both newcomers and Sr. engineers. We all learn and grow uniquely from the wisdom our teachers & mentors provide. What’s important is that we continue ‘forever learning’.
Mickey is a Software Engineer at Echobind, with a knack for problem solving with innovative solutions. A former bootcamp grad, he’s developed an avid interest in teaching, mentoring, and advocating for those just starting in software development. As a React.js to mobile convert, he primarily utilizes React Native as his paintbrush to go full Bob Ross on an empty canvas. Beyond coding, Mickey enjoys studying ancient history, world travel, tacos de asada, and bumpin’ some G-funk classics down the PCH.
