3.3.8. Perform Database Operations in a Service

In this chapter, you'll learn how to perform database operations in a module's service.

Run Queries#

MikroORM's entity manager is a class that has methods to run queries on the database and perform operations.

Medusa provides an InjectManager decorator from the Modules SDK that injects a service's method with a forked entity manager.

So, to run database queries in a service:

1. Add the InjectManager decorator to the method.
2. Add as a last parameter an optional sharedContext parameter that has the MedusaContext decorator from the Modules SDK. This context holds database-related context, including the manager injected by InjectManager

For example, in your service, add the following methods:

Code
1// other imports...2import { 3  InjectManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8
9class BlogModuleService {10  // ...11
12  @InjectManager()13  async getCount(14    @MedusaContext() sharedContext?: Context<EntityManager>15  ): Promise<number | undefined> {16    return await sharedContext?.manager?.count("my_custom")17  }18  19  @InjectManager()20  async getCountSql(21    @MedusaContext() sharedContext?: Context<EntityManager>22  ): Promise<number> {23    const data = await sharedContext?.manager?.execute(24      "SELECT COUNT(*) as num FROM my_custom"25    ) 26    27    return parseInt(data?.[0].num || 0)28  }29}

You add two methods getCount and getCountSql that have the InjectManager decorator. Each of the methods also accept the sharedContext parameter which has the MedusaContext decorator.

The entity manager is injected to the sharedContext.manager property, which is an instance of EntityManager from the @mikro-orm/knex package.

You use the manager in the getCount method to retrieve the number of records in a table, and in the getCountSql to run a PostgreSQL query that retrieves the count.

Perform Database Operations#

There are two ways to perform database operations in transactional methods:

1. Using the data model repositories in your module.
2. Using the transactional entity manager injected into the method's shared context.

For both approaches, you must wrap the method performing the database operations in a transaction.

Wrap Database Operations in Transactions#

When performing database operations without using the Service Factory, you must wrap the method performing the database operations in a transaction.

To wrap database operations in a transaction, you create two methods:

1. A private or protected method that's wrapped in a transaction. To wrap it in a transaction, you use the InjectTransactionManager decorator from the Modules SDK.
2. A public method that calls the transactional method. You use on it the InjectManager decorator as explained in the previous section.

Both methods must accept as a last parameter an optional sharedContext parameter that has the MedusaContext decorator from the Modules SDK. It holds database-related contexts passed through the Medusa application.

For example:

Code
1import { 2  InjectManager,3  InjectTransactionManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8
9class BlogModuleService {10  // ...11  @InjectTransactionManager()12  protected async update_(13    input: {14      id: string,15      name: string16    },17    @MedusaContext() sharedContext?: Context<EntityManager>18  ): Promise<any> {19    const transactionManager = sharedContext?.transactionManager20    21    // TODO: update the record22  }23
24  @InjectManager()25  async update(26    input: {27      id: string,28      name: string29    },30    @MedusaContext() sharedContext?: Context<EntityManager>31  ) {32    return await this.update_(input, sharedContext)33  }34}

The BlogModuleService has two methods:

• A protected update_ that performs the database operations inside a transaction.
• A public update that executes the transactional protected method.

You can then perform in the transactional method the database operations either using the data model repository or the transactional entity manager.

Why Wrap a Transactional Method

The variables in the transactional method (such as update_) hold values that are uncommitted to the database. They're only committed once the method finishes execution.

So, if in your method you perform database operations, then use their result to perform other actions, such as connecting to a third-party service, you'll be working with uncommitted data.

By placing only the database operations in a method that has the InjectTransactionManager and using it in a wrapper method, the wrapper method receives the committed result of the transactional method.

For example, the update method may call other methods than update_ to perform other actions:

Code
1// other imports...2import { 3  InjectManager,4  InjectTransactionManager,5  MedusaContext,6} from "@medusajs/framework/utils"7import { Context } from "@medusajs/framework/types"8import { EntityManager } from "@mikro-orm/knex"9
10class BlogModuleService {11  // ...12  @InjectTransactionManager()13  protected async update_(14    // ...15  ): Promise<any> {16    // ...17  }18  @InjectManager()19  async update(20    input: {21      id: string,22      name: string23    },24    @MedusaContext() sharedContext?: Context<EntityManager>25  ) {26    const newData = await this.update_(input, sharedContext)27
28    // example method that sends data to another system29    await this.sendNewDataToSystem(newData)30
31    return newData32  }33}

In this case, only the update_ method is wrapped in a transaction. The returned value newData holds the committed result, which can be used for other operations, such as passed to a sendNewDataToSystem method.

Using Methods in Transactional Methods

If your protected transactional method uses other methods that accept a Medusa context, pass the shared context to those methods.

For example:

Code
1// other imports...2import { 3  InjectTransactionManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8
9class BlogModuleService {10  // ...11  @InjectTransactionManager()12  protected async anotherMethod(13    @MedusaContext() sharedContext?: Context<EntityManager>14  ) {15    // ...16  }17  18  @InjectTransactionManager()19  protected async update_(20    input: {21      id: string,22      name: string23    },24    @MedusaContext() sharedContext?: Context<EntityManager>25  ): Promise<any> {26    this.anotherMethod(sharedContext)27  }28}

You use the anotherMethod transactional method in the update_ transactional method, so you pass it the shared context.

The anotherMethod now runs in the same transaction as the update_ method.

Perform Database Operations with Data Model Repositories#

For every data model in your module, Medusa generates a data model repository that has methods to perform database operations.

For example, if your module has a Post model, it has a postRepository in the container.

The data model repository is a wrapper around the entity manager that provides a higher-level API for performing database operations.

Resolve Data Model Repository

When the Medusa application injects a data model repository into a module's container, it formats the registration name by:

• Taking the data model's name that's passed as the first parameter of model.define
• Lower-casing the first character
• Suffixing the result with Repository.

For example:

• Post model: postRepository
• My_Custom model: my_CustomRepository

So, to resolve a data model repository from a module's container, pass the expected registration name of the repository in the first parameter of the module's constructor (the container).

For example:

You can then use the data model repository in your service to perform database operations.

Data Model Repository Methods

A data model repository has methods that allows you to create, update, and delete records, among other operations.

To learn about the methods available in a data model repository, refer to the Data Model Repository reference.

Perform Database Operations with the Transactional Entity Manager#

Your transactional method can use the transactional entity manager injected into the method's shared context to perform database operations. It's an instance of the MikroORM EntityManager class.

For example:

Code
1import { 2  InjectManager,3  InjectTransactionManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8
9class BlogModuleService {10  // ...11  @InjectTransactionManager()12  protected async update_(13    input: {14      id: string,15      name: string16    },17    @MedusaContext() sharedContext?: Context<EntityManager>18  ): Promise<any> {19    const transactionManager = sharedContext?.transactionManager20    await transactionManager?.nativeUpdate(21      "my_custom",22      {23        id: input.id,24      },25      {26        name: input.name,27      }28    )29
30    // retrieve again31    const updatedRecord = await transactionManager?.execute(32      `SELECT * FROM my_custom WHERE id = '${input.id}'`33    )34
35    return updatedRecord36  }37
38  @InjectManager()39  async update(40    input: {41      id: string,42      name: string43    },44    @MedusaContext() sharedContext?: Context<EntityManager>45  ) {46    return await this.update_(input, sharedContext)47  }48}

The update_ method uses the transactional entity manager injected into the sharedContext.transactionManager property to perform the database operations.

Find all available methods in the MikroORM EntityManager reference.

Configure Transactions with the Base Repository#

To configure the transaction, such as its isolation level, use the baseRepository class registered in your module's container.

The baseRepository is an instance of a repository class that provides methods to create transactions, run database operations, and more.

The baseRepository has a transaction method that allows you to run a function within a transaction and configure that transaction.

For example, resolve the baseRepository in your service's constructor:

Then, use it in the service's transactional methods:

Code
1// ...2import { 3  InjectManager,4  InjectTransactionManager,5  MedusaContext,6} from "@medusajs/framework/utils"7import { Context } from "@medusajs/framework/types"8import { EntityManager } from "@mikro-orm/knex"9
10class BlogModuleService {11  // ...12  @InjectTransactionManager()13  protected async update_(14    input: {15      id: string,16      name: string17    },18    @MedusaContext() sharedContext?: Context<EntityManager>19  ): Promise<any> {20    return await this.baseRepository_.transaction(21      async (transactionManager) => {22        await transactionManager.nativeUpdate(23          "my_custom",24          {25            id: input.id,26          },27          {28            name: input.name,29          }30        )31
32        // retrieve again33        const updatedRecord = await transactionManager.execute(34          `SELECT * FROM my_custom WHERE id = '${input.id}'`35        )36
37        return updatedRecord38      },39      {40        transaction: sharedContext?.transactionManager,41      }42    )43  }44
45  @InjectManager()46  async update(47    input: {48      id: string,49      name: string50    },51    @MedusaContext() sharedContext?: Context<EntityManager>52  ) {53    return await this.update_(input, sharedContext)54  }55}

The update_ method uses the baseRepository_.transaction method to wrap a function in a transaction.

The function parameter receives a transactional entity manager as a parameter. Use it to perform the database operations.

The baseRepository_.transaction method also receives as a second parameter an object of options. You must pass in it the transaction property and set its value to the sharedContext.transactionManager property so that the function wrapped in the transaction uses the injected transaction manager.

Transaction Options#

The second parameter of the baseRepository_.transaction method is an object of options that accepts the following properties:

1. transaction: Set the transactional entity manager passed to the function. You must provide this option as explained in the previous section.

Code
1// other imports...2import { EntityManager } from "@mikro-orm/knex"3import { 4  InjectTransactionManager,5  MedusaContext,6} from "@medusajs/framework/utils"7import { Context } from "@medusajs/framework/types"8
9class BlogModuleService {10  // ...11  @InjectTransactionManager()12  async update_(13    input: {14      id: string,15      name: string16    },17    @MedusaContext() sharedContext?: Context<EntityManager>18  ): Promise<any> {19    return await this.baseRepository_.transaction<EntityManager>(20      async (transactionManager) => {21        // ...22      },23      {24        transaction: sharedContext?.transactionManager,25      }26    )27  }28}

2. isolationLevel: Sets the transaction's isolation level. Its values can be:
   • read committed
   • read uncommitted
   • snapshot
   • repeatable read
   • serializable

Code
1// other imports...2import { 3  InjectTransactionManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8import { IsolationLevel } from "@mikro-orm/core"9
10class BlogModuleService {11  // ...12  @InjectTransactionManager()13  async update_(14    input: {15      id: string,16      name: string17    },18    @MedusaContext() sharedContext?: Context<EntityManager>19  ): Promise<any> {20    return await this.baseRepository_.transaction<EntityManager>(21      async (transactionManager) => {22        // ...23      },24      {25        isolationLevel: IsolationLevel.READ_COMMITTED,26      }27    )28  }29}

3. enableNestedTransactions: (default: false) whether to allow using nested transactions.
   • If transaction is provided and this is disabled, the manager in transaction is re-used.

Code
1// other imports...2import { 3  InjectTransactionManager,4  MedusaContext,5} from "@medusajs/framework/utils"6import { Context } from "@medusajs/framework/types"7import { EntityManager } from "@mikro-orm/knex"8
9class BlogModuleService {10  // ...11  @InjectTransactionManager()12  async update_(13    input: {14      id: string,15      name: string16    },17    @MedusaContext() sharedContext?: Context<EntityManager>18  ): Promise<any> {19    return await this.baseRepository_.transaction<EntityManager>(20      async (transactionManager) => {21        // ...22      },23      {24        enableNestedTransactions: false,25      }26    )27  }28}