How to Migrate Your Node.js App from SQL to MongoDB
_Spot.png&w=3840&q=75)
Rate this tutorial
With the rapid adoption of the Agile methodology in software development, developers now seek to migrate from SQL to NoSQL databases for more flexibility and horizontal scaling which is cheaper and more efficient for software development.
Migrating from a SQL database to a NoSQL database can be challenging because of key factors like data model differences, query language, access patterns, and query optimization.
In this article, we'll explore key MongoDB concepts like collections, documents, and BSON and the differences in data modeling between SQL and NoSQL databases. We'll also migrate from a relational database (Postgres) to MongoDB.
Below are a few prerequisites that you'll need before moving forward with this article:
- Knowledge of JavaScript
- Experience building Node.js applications
- Experience with SQL and Postgres database
- Node.js ≥v20 installed
In this section, we'll migrate the database and queries of a Node.js backend application from Postgres to MongoDB. The application will be a public phonebook app with the following features:
- User registration and login: Passwords are hashed using bcrypt, and JWT is used for authentication.
- CRUD operations:
- Create contact: Authenticated users can create contacts.
- Get contacts: Get contacts with call rate count and user details, filtered by date range and ordered by call rate count.
- Get contact by ID: Retrieve a single contact by ID for the authenticated user.
- Update contact: Authenticated users can update their contacts.
- Delete contact: Authenticated users can delete their contacts.
Open
psql and run the following command to create the required tables for the application:
Create a Node.js project and install the following dependencies:
You can install all the required packages with the following command:
Create a
.env.postgres file and add the following environment variables. You will need to change the values to use the credentials to connect to your database.Create a
postgres.js file in the project root directory and add the following:Ensure your package.json includes
"type": "module".We've successfully set up a Nodejs backend application and configured the Postgres database using the Express framework.
You can start your server by running:
Using the
--env-file parameter will inject the environment variables, and you'll be able to use them through process.env.Let's proceed with implementing the features of the application.
User register:
The user register endpoint ensures secure handling of passwords using
bcrypt for hashing the password and executes a SQL query to insert the new user into the users table in the PostgreSQL database via the connection pool.User login:
The user login endpoint ensures secure handling of user authentication using bcrypt for password validation and JWT for session management.
Middleware to authenticate users:
This middleware function ensures that only requests with valid JWT tokens can access protected routes, enhancing the application's security.
Create contact:
The create contact endpoint creates new contacts while ensuring only authenticated users can perform this action.
Get contact by ID:
The get contact by ID endpoint allows only authenticated users to fetch a specific contact by its ID and checks that it belongs to the authenticated user.
Update contact:
The update contact endpoint allows only authenticated users to access and update their contacts.
Delete contact:
The delete contact endpoint allows only authenticated users to delete their contacts.
Add call rate:
The add call rate endpoint allows only authenticated users to rate a specific contact by its ID.
The rates are associated with contacts in a one-to-many relationship. The
contact_id column in the rates table is a foreign key that references the id column in the contacts table.This foreign key establishes a relationship where each rate is linked to a specific contact.
Having a defined relationship allows for efficient queries that can join two or more tables.
For example, you can quickly fetch a contact along with all its associated rates or aggregate data, like the number of rates per contact.
Get contacts:
The get contacts endpoint retrieves and aggregates contact data, ensuring that only contacts within the specified date range are included in the results. It also provides useful information about the number of calls associated with each contact, ordered by the number of call ratings.
SELECT clause: The SELECT clause selects the specified column in the table to select data from.
FROM clause: The FROM clause specifies the table from which to select data.
LEFT JOIN users ON contacts.user_id = users.id: This joins the contacts table with the users table on the condition that the user_id in the contacts table matches the id in the users table.
LEFT JOIN rates ON contacts.id = rates.contact_id: This joins the contacts table with the rates table on the condition that the id in the contacts table matches the contact_id in the rates table.
WHERE contacts.created_at BETWEEN $1 AND $2: This filters the contacts to only include those created between the dates specified by $1 (startDate) and $2 (endDate).
GROUP BY contacts.id, users.username: This groups the results by contacts.id and users.username. This is necessary because we are using an aggregate function (COUNT(rates.id)), and SQL requires that all non-aggregated columns in the SELECT clause be included in the GROUP BY clause.
ORDER BY call_rate DESC: This orders the results by call_rate in descending order (DESC). This means that contacts with the highest number of rates (or calls) will appear first in the results.
To test out your implementation, don't forget to stop and restart your server.
As we can see, PostgreSQL follows a relational data model. At the same time, MongoDB is a NoSQL document-oriented database so we need to redesign our schema to fit MongoDB's document-based model. In the SQL migration section, we'll identify the entities and relationships in our PostgreSQL schema and map them to MongoDB's collections and documents.
To set up MongoDB for use with our Node.js application, we'll use MongoDB Atlas, a multi-cloud database service that simplifies deploying and managing your databases with just a few clicks.
It also offers a free-forever tier with no credit card requirements. How cool is that?
To create a database cluster, follow the official docs.
Choose the shared cluster option for a free-forever tier. The default configuration is sufficient for our demo application.
When you create your database cluster, you'll be prompted to add a database user and password for your database cluster. Make sure you save the username and password where you can reference as you'll need them to connect to the database cluster.
Create a Node.js project and install the following dependencies:
You can install the libraries with:
Create a
.env.mongodb file and add the following environment variables:Create a
mongodb.js file in the project root directory and add the following:To use top-level await, you will need to enable ES module support and ensure that your package.json has
"type": "module".The above code allows your Node app to connect to your database cluster.
Run the following command to start your Node server:
There are two steps here:
- Translate the SQL queries to the MongoDB Query API.
- Use the MongoDB Query Converter to migrate your SQL queries to the MongoDB Query API.
The MongoDB Node.js driver is a package that allows you to connect to MongoDB, execute database operations, and manage collections and documents. It provides a straightforward, schemaless solution to model your application data in a flexible manner.
The first thing to consider for database migration is the data models. Remember how we create tables and relationships for the Postgres database by running the following SQL in the
psql terminal:Here is the good part: Using the MongoDB Node.js driver, you do not have to define schemas. When you insert a document into a collection, it is created with the fields you provided with the insert method.
Register:
This endpoint is the MongoDB equivalent of the previous
register endpoint designed for PostgreSQL. It uses MongoDB's driver insertOne methods to interact with the database. While the PostgreSQL implementation uses an SQL INSERT statement to insert new data, MongoDB's implementation uses the insertOne method to insert new data in a collection.Login:
This endpoint is the MongoDB equivalent of the previous
/login endpoint designed for PostgreSQL. It uses MongoDB's driver findOne methods to interact with the database. While the PostgreSQL implementation uses pool.query to execute a SQL SELECT statement to find a user row in the table, MongoDB's implementation uses the usersCollection.findOne to find a user document in the collection.In MongoDB, the findOne method uses a JSON object to specify the query condition, which is analogous to the WHERE clause in SQL. In SQL, users is the name of the table being queried, similar to a collection in MongoDB.
Middleware to authenticate users:
Here, we're using the same function as we did in the previous example.
Create contact:
This endpoint is the MongoDB version of the previous POST
/contact endpoint we designed for PostgreSQL. Instead of using SQL commands like pool.query to add a contact row to a table, it uses MongoDB's contactsCollection.insertOne method to add a contact document to a collection.Converting
req.user.id to ObjectId ensures that the user ID is correctly formatted and can be efficiently queried and indexed in MongoDB. This is important as we'll need to perform operations that involve matching or referencing this ID within the database.Get contact by ID:
This endpoint is the MongoDB equivalent of the previous GET
/contacts/:id endpoint designed for PostgreSQL. While the PostgreSQL implementation uses pool.query to execute an SQL SELECT statement and the WHERE clause to find a contact row in the table, the MongoDB implementation uses the contactsCollection.findOne to find a contact document in the collection and returns the found document.PostgreSQL directly uses the
id and user_id parameters in the SQL query while MongoDB converts id and user_id to ObjectId to match MongoDB's document ID format.Update contact:
This endpoint is the MongoDB equivalent of the previous PUT
/contacts endpoint designed for PostgreSQL. While the PostgreSQL implementation uses pool.query to execute a SQL UPDATE statement and a WHERE clause to update the contact in the contacts table, the MongoDB implementation uses the findOneAndUpdate method with $set to update the document in the contacts collection.Delete contact:
This endpoint is the MongoDB equivalent of the previous DELETE
/contact endpoint designed for PostgreSQL. While the PostgreSQL implementation uses pool.query to execute an SQL DELETE statement and a WHERE clause to delete the contact in the contacts table, the MongoDB implementation uses the deleteOne method to delete the document in the contacts collection.In MongoDB, querying is done using JSON-like objects. The equivalent of the WHERE clause is the query object passed to methods like findOne, deleteOne, etc.
Add call rate:
This endpoint is the MongoDB equivalent of the previous POST
/contacts/:id/rates endpoint designed for PostgreSQL. While the PostgreSQL implementation uses pool.query to execute a SQL INSERT INTO statement to insert a rate row in the table, MongoDB's implementation uses the contactsCollection.updateOne to add a rate to a contact, which will now update the contact document by pushing a new rate into the embedded rates array.By embedding rates within contacts, you avoid the need for joins (like
$lookup), which can improve query performance, especially for read-heavy operations. Now, all relevant information about a contact, including its rates, is stored together in a single document.Get contacts:
This endpoint is the MongoDB equivalent of the previous GET
/contacts endpoint designed for PostgreSQL. While the PostgreSQL implementation uses a SQL query with JOIN statements, WHERE clause, GROUP BY, and ORDER BY to perform (matching, joining, adding fields, sorting) operations on the contacts table, MongoDB's implementation uses the aggregate method to perform similar operations on the contacts collection.An aggregation pipeline in MongoDB is a framework that passes documents into a multi-stage pipeline that can transform them and output aggregated results. Each stage in the pipeline performs an operation on the input documents and passes the result to the next stage.
This aggregation pipeline filters contacts based on their creation date and the authenticated user's ID, joins the contacts collection with the users collection to fetch user details, joins the contacts collection with the rates collection to fetch rate details, and then adds a new field --- call_rate --- to count the number of rates per contact and sorts the contacts by call_rate in descending order.
Compared to the SQL statement, you don't need to use two JOINs. Therefore, the performance is better.
Now, let's talk about using the MongoDB Query Converter to migrate your SQL queries to the MongoDB Query API.
MongoDB provides a tool called Relational Migrator which allows you to migrate your SQL queries to the MongoDB Query API with ease. It also addresses common migration and data modeling challenges to provide a smooth transition to MongoDB.
Download Relational Migrator to try it out yourself.
Before using the query converter, you must sign in to your Atlas account in Relational Migrator. For details, see Log In with Atlas.
- Open the Relational Migrator app on your PC and log in with Atlas.
- Connect to your Postgres database.
- Select the tables that you want to migrate.
- Define your schema as follows:
Then, name your project.
Generate the MongoDB schema from the imported Postgres database table as follows:
From the Code Generation tab, click the Query Converter pane.Click the Paste SQL query button to access the query converter editor.
Here, you can enter the SQL queries you want to convert and click Convert to convert them to MongoDB queries.
MongoDB Compass is a graphical user interface for MongoDB and it's completely free to install on Mac, Windows, and Linux. You can import JSON or CSV data to MongoDB via MongoDB Compass.
To import data to a collection, connect to your database cluster using the connection string from Atlas, create a database and collection, and navigate to the detailed collection view by selecting the collection from the Databases tab or clicking the collection in the left-side navigation.
Next, click the Add Data dropdown, select Import JSON or CSV file, select the appropriate file type, and click Import. After successful import, the dialog closes and Compass displays the collection page containing the newly imported documents.
To import data using
mongoimport, install the mongoDB database tools, a suite of command-line utilities for working with MongoDB, and then run mongoimport from the system command line.Here is an example of importing data via
mongoimport:Here, we use
mongoimport to import the JSON data from the users.json file into the users collection in the Node-API database.To import data using the VS Code extension, go to the extensions tab in VS Code and search for MongoDB. You should see the MongoDB for VS Code extension.
Once you successfully install it, you'll see a leaf icon in your VS Code sidebar. Click on it and under connections, add a new connection. Then, click Connect with connection string and pass in your database cluster connection string.
You should see your cluster. Hover on your cluster and click the plus icon to create a new database. This will generate a playground file where you can add the following script to create a database, collection and import data:
Use Postman or any API testing tool to test all the updated endpoints with the migrated queries to ensure the migration works as expected.
Migrating a Node.js application from a SQL database to MongoDB can be seamless and efficient when approached with a well-structured resource. In this tutorial, we took a look at setting up MongoDB, migrating the SQL queries, and performing data migration with MongoDB Compass, MongoImport, and the MongoDB for VS Code extension.
By following the structured approach in the tutorial, you can successfully migrate your Node.js application from a SQL database to MongoDB.
Top Comments in Forums
There are no comments on this article yet.
Rate this tutorial