3.2.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 { SqlEntityManager } from "@mikro-orm/knex"7
8class HelloModuleService {9  // ...10
11  @InjectManager()12  async getCount(13    @MedusaContext() sharedContext?: Context<EntityManager>14  ): Promise<number> {15    return await sharedContext.manager.count("my_custom")16  }17  18  @InjectManager()19  async getCountSql(20    @MedusaContext() sharedContext?: Context<EntityManager>21  ): Promise<number> {22    const data = await sharedContext.manager.execute(23      "SELECT COUNT(*) as num FROM my_custom"24    ) 25    26    return parseInt(data[0].num)27  }28}

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.

Execute Operations in Transactions#

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 HelloModuleService {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 HelloModuleService has two methods:

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

The shared context's transactionManager property holds the transactional entity manager (injected by InjectTransactionManager) that you use to perform database operations.

Why Wrap a Transactional Method#

The variables in the transactional method (for example, 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 could be changed to the following:

Code
1// other imports...2import { EntityManager } from "@mikro-orm/knex"3
4class HelloModuleService {5  // ...6  @InjectManager()7  async update(8    input: {9      id: string,10      name: string11    },12    @MedusaContext() sharedContext?: Context<EntityManager>13  ) {14    const newData = await this.update_(input, sharedContext)15
16    await sendNewDataToSystem(newData)17
18    return newData19  }20}

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 transactional method uses other methods that accept a Medusa context, pass the shared context to those methods.

For example:

Code
1// other imports...2import { EntityManager } from "@mikro-orm/knex"3
4class HelloModuleService {5  // ...6  @InjectTransactionManager()7  protected async anotherMethod(8    @MedusaContext() sharedContext?: Context<EntityManager>9  ) {10    // ...11  }12  13  @InjectTransactionManager()14  protected async update_(15    input: {16      id: string,17      name: string18    },19    @MedusaContext() sharedContext?: Context<EntityManager>20  ): Promise<any> {21    anotherMethod(sharedContext)22  }23}

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.

Configure Transactions#

To configure the transaction, such as its isolation level, use the baseRepository dependency 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, add the following method that uses it:

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 HelloModuleService {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"3
4class HelloModuleService {5  // ...6  @InjectTransactionManager()7  async update_(8    input: {9      id: string,10      name: string11    },12    @MedusaContext() sharedContext?: Context<EntityManager>13  ): Promise<any> {14    return await this.baseRepository_.transaction<EntityManager>(15      async (transactionManager) => {16        // ...17      },18      {19        transaction: sharedContext.transactionManager,20      }21    )22  }23}

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 { IsolationLevel } from "@mikro-orm/core"3
4class HelloModuleService {5  // ...6  @InjectTransactionManager()7  async update_(8    input: {9      id: string,10      name: string11    },12    @MedusaContext() sharedContext?: Context<EntityManager>13  ): Promise<any> {14    return await this.baseRepository_.transaction<EntityManager>(15      async (transactionManager) => {16        // ...17      },18      {19        isolationLevel: IsolationLevel.READ_COMMITTED,20      }21    )22  }23}

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
1class HelloModuleService {2  // ...3  @InjectTransactionManager()4  async update_(5    input: {6      id: string,7      name: string8    },9    @MedusaContext() sharedContext?: Context<EntityManager>10  ): Promise<any> {11    return await this.baseRepository_.transaction<EntityManager>(12      async (transactionManager) => {13        // ...14      },15      {16        enableNestedTransactions: false,17      }18    )19  }20}