Using ROQ with MongoDB

This guide will provide instructions on integrating an application generated by ROQ with a NoSQL database, specifically MongoDB.

What is MongoDB?

MongoDB (opens in a new tab) is a type of database known as NoSQL. It stores data in BSON format, which is similar to JSON and is designed for storing data in key-value pairs. MongoDB is popular in JavaScript application development because its document model is easily mappable to objects in application code. Additionally, it includes built-in support for high availability and horizontal scaling, making it a reliable choice for large-scale applications.

MongoDB is a database that stores data in collections without requiring a schema to be defined beforehand, unlike in a relational database where you need to define tables. The structure of each collection can also be modified over time, making it easy to iterate your data model.

Transaction

The transaction feature from relational databases, such as PostgreSQL, is natively supported and used by ROQ's generated application. However, the transaction feature is not supported in NoSQL databases like MongoDB. MongoDB only allows the transaction to start on a replica set (opens in a new tab).

ROQ using Prisma to connect the database and Prisma uses transactions internally to avoid partial writes on nested queries. It means Prisma inherit the requirement of needing (opens in a new tab) a replica set configured.

⚠️

If you have a standalone MongoDB installation, you need to configure and run a replica set for the ROQ's generated application to work properly. Please read the MongoDB documentation about converting a standalone MongoDB into a replica set (opens in a new tab).

MongoDB Atlas

MongoDB Atlas (opens in a new tab) is a fully managed cloud service provided by MongoDB, Inc. It offers all the features of MongoDB without the operational overhead of setting up and maintaining on-premises or cloud infrastructure. The database supports the replica set feature by default.

Create Database

Create a new account (opens in a new tab) on MongoDB Atlas and create a new project. From the Deployment section, click the Database menu. To create the database, click the Build a Database.

MongoDB Atlas

MongoDB Atlas also provides a free database for development purposes. We can upgrade it to a production cluster anytime. When creating the database, select the M0 Free option and leave all other settings as default.

new mongodb database

Create User

To access the database, we need a user. Go to the Security section and select Quickstart. Choose Username and Password for authentication. Then, set a secure username and password and save the password securely. After that, click the Create User button.

mongod db atlas create user and password

IP Whitelist

To enable access from outside networks, such as local environments, we can configure specific IPs. For worldwide access, we can set it to 0.0.0.0/0. However, this is not recommended for production purposes.

mongodb ip whitelist

`DATABASE_URL`

From the MongoDB Atlas Overview, we can get the database connection information. There are many ways to connect to the database. We can use MongoDB native drivers or using tools such as Compass, Shell, MongoDB for VS Code, etc. We will use the connection information using drivers.

mongodb connection information

From MongoDB Atlas, we can copy the connection URI and add the initial database for our application, e.g., animefigure. Then, we can set the DATABASE_URL environment variable.

DATABASE_URL=mongodb+srv://postgres:<password>@cluster0.thmzdtv.mongodb.net/animefigure?retryWrites=true&w=majority

Please remember to update the password we set earlier. Later, we need to add this DATABASE_URL environment variable into the .env file on the project root of our application.

Prisma Schema Conversion

The default application generated by ROQ uses PostgreSQL as its database. Suppose we want to switch to another database, such as MongoDB. In that case, we need to configure the Prisma schema file at prisma\schema.prisma.

Learn more here about Prisma Schema (opens in a new tab) documentation and this Prisma documentation (opens in a new tab) explains the commonalities and differences between MongoDB and other database providers.

These are a few conversion notes for the Prisma schema if we want to use the MongoDB database instead of PostgreSQL.

Data Sources

This code is a data source taken from Prisma schema for the PostgreSQL database:

datasource db {
  provider    = "postgresql"
  url         = env("DATABASE_URL")
  extensions  = [pgcrypto]
}

The changes to the database require updating Prisma's data sources to MongoDB and removing extension support.

datasource db {
  provider    = "mongodb"
  url         = env("DATABASE_URL")
}

Generators

Since MongoDB doesn't support extensions, we should remove any PostgreSQL extensions.

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}

Data Model

MongoDB's document-based structure and flexible schemas mean that using Prisma with MongoDB differs from using it with a relational database in several ways.

For example, we can only reuse the existing Prisma schema with changes, especially in defining ID. Prisma does not support fields starting with _, which is common in MongoDB, so this needs to be mapped to a Prisma field using the @map attribute.

This code is a piece of PostgreSQL Prisma schema from a typical ROQ generated application:

id       String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
user_id  String?  @db.Uuid

To change the schema for the MongoDB database, we need to map the Prisma id field to the _id column in the database, @map("_id") and specify that this field uses the ObjectId type in the database @db.ObjectId. The Prisma schema after conversion is like this:

id       String   @id @default(auto()) @map("_id") @db.ObjectId
user_id  String?  @db.ObjectId

For more information about the Prisma schema data model, please read their official documentation here (opens in a new tab) and the native type mapping from Prisma to MongoDB here (opens in a new tab).

Here are the full examples of Prisma schemas for MongoDB and PostgreSQL databases. The MongoDB schema has been converted from the PostgreSQL schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["clientExtensions"]
}
 
datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}
 
model collector_profile {
  id              String   @id @default(auto()) @map("_id") @db.ObjectId
  user_id         String?  @db.ObjectId
  collection_list String?
  wish_list       String?
  created_at      DateTime @default(now())
  updated_at      DateTime @default(now())
  user            user?    @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
 
model discussion_board {
  id         String   @id @default(auto()) @map("_id") @db.ObjectId
  topic      String
  user_id    String?  @db.ObjectId
  created_at DateTime @default(now())
  updated_at DateTime @default(now())
  user       user?    @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
 
model figure_inventory {
  id               String             @id @default(auto()) @map("_id") @db.ObjectId
  figure_name      String
  store_id         String?            @db.ObjectId
  created_at       DateTime           @default(now())
  updated_at       DateTime           @default(now())
  store            store?             @relation(fields: [store_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  purchase_history purchase_history[]
  sales_analytics  sales_analytics[]
}
 
model purchase_history {
  id               String            @id @default(auto()) @map("_id") @db.ObjectId
  user_id          String?           @db.ObjectId
  figure_id        String?           @db.ObjectId
  purchase_date    DateTime
  created_at       DateTime          @default(now())
  updated_at       DateTime          @default(now())
  figure_inventory figure_inventory? @relation(fields: [figure_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  user             user?             @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
 
model sales_analytics {
  id               String            @id @default(auto()) @map("_id") @db.ObjectId
  store_id         String?           @db.ObjectId
  figure_id        String?           @db.ObjectId
  sales_count      Int
  created_at       DateTime          @default(now())
  updated_at       DateTime          @default(now())
  figure_inventory figure_inventory? @relation(fields: [figure_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  store            store?            @relation(fields: [store_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
}
 
model store {
  id               String             @id @default(auto()) @map("_id") @db.ObjectId
  description      String?
  name             String
  created_at       DateTime           @default(now())
  updated_at       DateTime           @default(now())
  user_id          String             @db.ObjectId
  tenant_id        String
  user             user               @relation(fields: [user_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  figure_inventory figure_inventory[]
  sales_analytics  sales_analytics[]
}
 
model user {
  id                String              @id @default(auto()) @map("_id") @db.ObjectId
  email             String              @unique
  firstName         String?
  lastName          String?
  roq_user_id       String
  tenant_id         String
  created_at        DateTime            @default(now())
  updated_at        DateTime            @default(now())
  collector_profile collector_profile[]
  discussion_board  discussion_board[]
  purchase_history  purchase_history[]
  store             store[]
}

Prisma `regenerate`

Because of the Prisma changes, we should regenerate it to update the Prisma Client based on the Prisma schema (schema.prisma). The Prisma Client will update a set of CRUD (Create, Read, Update, Delete) operations for the models defined in the schema.

We need to regenerate the Prisma Client by running this command:

npx prisma regenerate

Prisma `db push`

The Prisma CLI has a dedicated command for prototyping schemas: db push. This command uses the same engine as Prisma Migrate (opens in a new tab) to synchronize the Prisma schema with the MongoDB database. For more information about db push. Please read the documentation here (opens in a new tab).

So instead of using the prisma migrate command for the MongoDB database, we will use:

npx prisma db push

The above command will synchronize MongoDB with our Prisma schema. Also, remember to set the DATABASE_URL in the .env file.

PostgreSQL Overview

Using ROQ with MongoDB

What is MongoDB?

Transaction

MongoDB Atlas

Create Database

Create User

IP Whitelist

DATABASE_URL

Prisma Schema Conversion

Data Sources

Generators

Data Model

Prisma regenerate

Prisma db push

`DATABASE_URL`

Prisma `regenerate`

Prisma `db push`