Stored Procedures

In this guide, you'll learn how to interact with your database using stored procedures.

Stored procedures are similar to transaction functions. They are used to perform operations on your application database. However, stored procedure functions run inside the database where transaction functions run on the application server. Running your business logic inside the database improves both performance and scalability. Transaction functions require at least three network round trips to the database in addition to any queries executed by the transaction function itself. Stored procedure functions only require a single network round trip to the database, while still providing the same behavior and guarantees as transaction functions.

While most database management systems provide support for stored procedures, they are often avoided because they are hard to use. They typically need to be written in a custom language such as PL/pgSQL. Additionally, they are not usually integrated into the application development process. DBOS stored procedure functions, in contrast, are written in TypeScript like the rest of your DBOS application. The DBOS Compiler deploys the stored procedure functions from your application to your application database.

Here's an example of a stored procedure function. You'll notice it is similar to the example transaction function from the transaction tutorial.

warning

Because stored procedures run inside the database, only raw database queries are supported. Query builders like Knex.js and ORMs like TypeORM and Prisma are not supported in stored procedure functions.

export class Greetings {
  @StoredProcedure()
  static async InsertGreeting(ctxt: StoredProcedureContext, friend: string, note: string) {
    await ctxt.query('INSERT INTO greetings (name, note) VALUES (?, ?)', [friend, note]);
  }
}

Deploying DBOS Stored Procedures

DBOS Stored procedure functions depend on PLV8, a trusted JavaScript language extension for PostgresSQL. PLV8 is supported out of the box on DBOS Cloud and several major cloud database providers. For running locally, we recommend using the sibedge/postgres-plv8 Docker image provided by Sibedge. For using DBOS Stored Procedures on your own PostgreSQL server, please see the official PLV8 documentation for installation instructions.

info

As of version 1.17, the @dbos-inc/create templates have been updated to use sibedge/postgres-plv8 in the start_postgres_docker.js script. Older DBOS applications using Docker will need to switch their PostgreSQL image from postgres:16.1 to sibedge/postgres-plv8 manually to support Stored Procedures.

Before running your DBOS application that uses stored procedures, you need to deploy those stored procedures to the database. To deploy your stored procedure functions to the database, you need the DBOS Compiler. Add the DBOS Compiler package as a devDependency of your app via NPM:

npm install --save-dev @dbos-inc/dbos-compiler

Once the DBOS Compiler is installed, you can use it to deploy the stored procedures to the database server specified in the dbos-config.yaml file with the following command:

npx dbosc deploy

info

For information about all of the compiler's command line options, please see the DBOS Compiler reference page

You can add npx dbosc deploy to your database migration commands to run it alongside other schema migrations. Deploying your app's stored procedures via Database Schema Management is required for DBOS Cloud deployment.

database:
  migrate:
    - npx knex migrate:latest
    - npx dbosc deploy