Packages for Store Routines in MariaDB 11.4

MariaDB 11.4 introduced many advanced features. One that grabbed my attention is the general support of packages for stored routines. Although this was previously available by activating the Oracle compatibility mode, now the feature is available generally out-of-the-box. This will help you to significantly enhance the organization of database development within a MariaDB environment. Packages provide a modular approach to managing database logic. This addition aligns MariaDB more closely with other advanced database systems that have long utilized packages, such as Oracle, and sets it apart in that regard from other open-source relational databases that don't support packages.

Packages in MariaDB allow you to group related stored procedures, functions, variables, and other elements together into a single unit. This structure provides several benefits, including improved code organization, enhanced reusability, and simplified maintenance. Prior to this update, each stored procedure and function in MariaDB existed independently, which could lead to a cluttered schema and more complicated management of complex business logic when implemented in the database. Packages address this issue by providing a way to logically group related routines.

Again, the primary advantage of using packages is the encapsulation of related routines. For example, in an e-commerce application, operations related to order processing can be grouped into a single package called OrderProcessing. This package might include procedures like PlaceOrder, CancelOrder, and UpdateOrderStatus, as well as functions such as GetOrderDetails. This logical grouping makes the database schema more organized and the codebase easier to navigate and hence to maintain.

Creating packages

Creating and using packages in MariaDB 11.4 and later is straightforward. Simply use the CREATE PACKAGE statement to define a package and the CREATE PACKAGE BODY statement to implement the package’s routines. Here is a simplified example without the actual business logic implementation:

DELIMITER $$

CREATE OR REPLACE PACKAGE OrderProcessing
  PROCEDURE PlaceOrder(customer_id INT, product_id INT, quantity INT);
  PROCEDURE CancelOrder(order_id INT);
  FUNCTION GetOrderDetails(order_id INT) RETURNS JSON;
END;

CREATE OR REPLACE PACKAGE BODY OrderProcessing
  PROCEDURE PlaceOrder(customer_id INT, product_id INT, quantity INT)
  BEGIN
    -- Implementation code here
  END;

  PROCEDURE CancelOrder(order_id INT)
  BEGIN
    -- Implementation code here
  END;

  FUNCTION GetOrderDetails(order_id INT) RETURNS JSON
  BEGIN
    -- Implementation code here
  END;
END;

$$
DELIMITER ;

In this example, the OrderProcessing package is defined with two procedures and one function. The package body provides the implementation for these routines, encapsulating the logic related to order processing within a single package.

Calling packaged stored routines

To call a procedure or function that was defined in a package, you use the "dot notation". Here's an example of how to call the CancelOrder procedure in the OrderProcessing package:

CALL OrderProcessing.CancelOrder(7);

The same applies to functions in packages:

SELECT OrderProcessing.GetOrderDetails(7);

You can also define package-level variables and constants, which are accessible to all routines within the package. This way you can share common data without relying on global variables or passing around parameters between routines. By centrally managing shared data within the package, you reduce code duplication and minimize the risk of errors. The following is an example (don't take the variable names too seriously in this snippet of code):

DELIMITER $$

CREATE OR REPLACE PACKAGE OrderProcessing
  -- procedure list (see previous example)
END;

CREATE OR REPLACE PACKAGE BODY OrderProcessing
  -- variable declarations
  DECLARE some_count INT DEFAULT 1;
  DECLARE some_total INT DEFAULT 0;

  -- procedure definitions (see previous example)
END;

$$
DELIMITER ;

Other benefits of packages

The introduction of packages in MariaDB 11.4 also brings the possibility of better version control and modularization of code. You can now manage stored routines more effectively, making it easier to track changes and updates. This modularization is particularly beneficial in large projects, where multiple developers might be working on different parts of the database logic simultaneously. By isolating different functionalities into packages, conflicts and overlaps can be minimized, leading to a smoother development process.

Moreover, packages support forward declarations, which means that the routines can be defined before their actual implementation. This feature allows for a more flexible and structured approach to coding, where developers can outline the package interface first and then fill in the details. This separation of interface and implementation can lead to cleaner, more understandable code, facilitating collaboration and reducing the learning curve for new developers joining a project.

For developers accustomed to working with Oracle databases, the inclusion of packages in MariaDB 11.4 will feel familiar and welcome. It bridges a functional gap between MariaDB and Oracle, making it easier to transition between these platforms.

Packages vs multiple schemas

It's important to note the distinction between packages and merely using multiple database schemas. While multiple schemas can help segregate different parts of a database, they do not offer the same level of organization and encapsulation as packages. Schemas are useful for dividing distinct areas of data and logic, but they do not inherently group related procedures and functions together in a way that enhances modularity and maintainability. Packages, on the other hand, allow for a more granular and cohesive approach, grouping related logic together within the same schema. This not only simplifies the management of routines but also improves the clarity and maintainability of the code.

Try it out

As always, MariaDB continues to evolve. Try it out today by downloading the latest version of MariaDB, or if you have Docker running:

docker run --name mariadb-11.4 -e MARIADB_ROOT_PASSWORD=my-secret-pw -e MARIADB_DATABASE=mydb -e MARIADB_USER=myuser -e MARIADB_PASSWORD=mypassword -d mariadb:11.4

docker exec -it mariadb-11.4 mariadb -p"mypassword"