Schema migration with Neon Postgres and Sequelize
Set up Neon Postgres and run migrations for your Javascript project using Sequelize ORM
Sequelize is a promise-based Node.js ORM that supports multiple relational databases. In this guide, we'll explore how to use Sequelize
ORM with a Neon Postgres database in a JavaScript project.
We'll create a Node.js application, configure Sequelize
, and show how to set up and run migrations with Sequelize
.
Prerequisites
To follow along with this guide, you will need:
- A Neon account. If you do not have one, sign up at Neon. Your Neon project comes with a ready-to-use Postgres database named
neondb
. We'll use this database in the following examples. - Node.js and npm installed on your local machine. We'll use Node.js to build and test the application locally.
Setting up your Neon database
Initialize a new project
- Log in to the Neon Console and navigate to the Projects section.
- Select an existing project or click the
New Project
button to create a new one.
Retrieve your Neon database connection string
Navigate to the Connection Details section to find your database connection string. It should look similar to this:
Keep your connection string handy for later use.
note
Neon supports both direct and pooled database connection strings, which can be copied from the Connection Details widget on your Neon Project Dashboard. A pooled connection string connects your application to the database via a PgBouncer connection pool, allowing for a higher number of concurrent connections. However, using a pooled connection string for migrations can be prone to errors. For this reason, we recommend using a direct (non-pooled) connection when performing migrations. For more information about direct and pooled connections, see Connection pooling.
Setting Up the Node application
Create a new Node project
We'll create a simple catalog with API endpoints that query the database for authors and a list of their books. Run the following commands in your terminal to set up a new project using Express.js
:
Add the DATABASE_URL
environment variable to the .env
file, which you'll use to connect to your Neon database. Use the connection string that you obtained from the Neon Console earlier:
To use the Sequelize
ORM to run queries, we need to install the sequelize
package and the pg
driver to connect to Postgres from Node.js. We also need to install the sequelize-cli
package to manage data models and run migrations. Run the following commands to install the required packages:
Configure Sequelize
Run the following command to initialize the sequelize
configuration:
This command creates config
, migrations
, models
, and seeders
directories at the project root.
The config
directory contains the config.json
file, which holds the database configuration. We want to have the database URL read as an environment variable, so we replace it with a config.js
file. Create a config.js
file in your config/
directory and add the following code:
To make the sequelize
CLI aware of the path to the new configuration file, we need to create a .sequelizerc
file at the project root and add the following code:
Create models and set up migrations
We'll create an Author
and a Book
model to represent the tables in our database. Run the following commands to create the models:
Sequelize creates a new file for each model in the models/
directory and a corresponding migration file in the migrations/
directory. Sequelize automatically adds an id
field as the primary key for each model, and createdAt
and updatedAt
fields to track the creation and update times of each record.
We still need to define the relationships between the Author
and Book
models. Update the book.js
file with the following code:
Sequelize does not automatically regenerate the migration files when you update the models. So, we need to manually update the migration files to add the foreign key constraint.
Update the migration file corresponding to the Book
model with the following code:
Run the following command to apply the migrations and create the tables in the database:
If Sequlize
successfully connects to the database and runs the migrations, you should see a success message in the terminal.
Add sample data to the database
We'll add some sample data to the database using the Sequelize
ORM. Create a new file named seed.js
at the project root and add the following code:
Run the following command to seed the database with the sample data:
Sequelize will print logs to the terminal as it connects to the database and adds the sample data.
Create API endpoints
Now that the database is set up and populated with data, we can implement the API to query the authors and their books. We'll use Express, which is a minimal web application framework for Node.js.
Create an index.js
file at the project root, and add the following code to set up your Express server:
This code sets up a simple API with two endpoints: /authors
and /books/:authorId
. The /authors
endpoint returns a list of all the authors, and the /books/:authorId
endpoint returns a list of books written by the specific author for the given authorId
.
Run the application using the following command:
This will start the server at http://localhost:3000
. Navigate to http://localhost:3000/authors
and http://localhost:3000/books/1
in your browser to check that the API works as expected.
Conclusion
In this guide, we set up a new Javascript project using Express.js
and the Sequelize
ORM, and connected it to a Neon
Postgres database. We created a schema for the database, generated and ran migrations, and implemented API endpoints to query the database.
Source code
You can find the source code for the application described in this guide on GitHub.
Resources
For more information on the tools used in this guide, refer to the following resources:
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.