Read and Write Objects to Relational Database Using ORM Workflow

Open Live Script

This example shows the basic operations for reading and writing objects to a relational database using Object Relational Mapping (ORM). This example depends on the Product class that maps to a database table. This class contains several properties that map to the database, as well as some methods that alter those properties.

classdef (TableName = "products") Product < database.orm.mixin.Mappable

    properties(PrimaryKey,ColumnName = "ProductNumber")
        ID int32
    end
    
    properties
        Name string
        Description string
        Quantity int32
    end

    properties(ColumnName = "UnitCost")
        CostPerItem double
    end

    properties(ColumnName = "Manufacturer")
        Supplier string
    end

    properties(ColumnType = "date")
        InventoryDate datetime
    end

    methods
        function obj = Product(id,name,description,supplier,cost,quantity,inventoryDate)
            if nargin ~= 0
                inputElements = numel(id);
                if numel(name) ~= inputElements || ...
                        numel(description) ~= inputElements || ...
                        numel(supplier) ~= inputElements || ...
                        numel(cost) ~= inputElements || ...
                        numel(quantity) ~= inputElements || ...
                        numel(inventoryDate) ~= inputElements
                    error('All inputs must have the same number of elements')
                end
    
                % Preallocate by creating the last object first
                obj(inputElements).ID = id(inputElements);
                obj(inputElements).Name = name(inputElements);
                obj(inputElements).Description = description(inputElements);
                obj(inputElements).Supplier = supplier(inputElements);
                obj(inputElements).CostPerItem = cost(inputElements);
                obj(inputElements).Quantity = quantity(inputElements);
                obj(inputElements).InventoryDate = inventoryDate(inputElements);

                for n = 1:inputElements-1
                    % Fill in the rest of the objects
                    obj(n).ID = id(n);
                    obj(n).Name = name(n);
                    obj(n).Description = description(n);
                    obj(n).Supplier = supplier(n);
                    obj(n).CostPerItem = cost(n);
                    obj(n).Quantity = quantity(n);
                    obj(n).InventoryDate = inventoryDate(n);
                end
            end
        end
        function obj = adjustPrice(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBeNumeric}
            end
           obj.CostPerItem = obj.CostPerItem + amount; 
        end

        function obj = shipProduct(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBePositive,mustBeInteger}
            end

            obj.Quantity = obj.Quantity - amount;
        end

        function obj = recieveProduct(obj,amount)
            arguments
                obj (1,1) Product
                amount (1,1) {mustBePositive,mustBeInteger}
            end

            obj.Quantity = obj.Quantity + amount;
            obj.InventoryDate = datetime('today');
        end

    end

end

Create a Database Connection

To read and write to a database using ORM, create a sqlite database file that does not require a connection to a live database.

filename = "orm_demo.db";
if exist(filename,"file")
    conn = sqlite(filename);
else
    conn = sqlite(filename,"create");
end

% Remove it to maintain consistency
execute(conn,"DROP TABLE IF EXISTS products");

Populate Database Table with Objects

The orm2sql function shows how a mapped MATLAB® class is respresented as a database table. Display the database column information based on the class defined in Product.m.

orm2sql(conn,"Product")

ans = 
    "CREATE TABLE products
     (ProductNumber double, 
     Name text, 
     Description text, 
     Quantity double, 
     UnitCost double, 
     Manufacturer text, 
     InventoryDate date, 
     PRIMARY KEY (ProductNumber))"

Use the sqlfind function to verify that the products table does not exist.

sqlfind(conn,"products")

ans =

  0×5 empty table

    Catalog    Schema    Table    Columns    Type
    _______    ______    _____    _______    ____

Insert a Scalar Object

Create a Product object and use it to create and populate a table.

toy = Product(1,"Toy1","Descr1","CompanyA",24.99,0,datetime(2023,1,1))

toy = 
  Product with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 0
      CostPerItem: 24.9900
         Supplier: "CompanyA"
    InventoryDate: 01-Jan-2023

Use the ormwrite function to populate the database with the data from toy, and use the sqlread function to read the table and verify the results.

ormwrite(conn,toy);
sqlread(conn,"products")

ans=1×7 table
    ProductNumber     Name     Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    ______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"     "Descr1"         0         24.99       "CompanyA"     "2023-01-01 00:00:00.000000"

Insert an Array of Objects

Instantiate a Product class with an array of objects.

productArray = Product(2:10,...
    ["Toy2","Toy3","Toy4","Toy5","Toy6","Toy7","Toy8",...
    "Toy9","Toy10"],...
    ["Descr2","Descr3","Descr4","Descr5","Descr6","Descr7",...
    "Descr8","Descr9","Descr10"],...
    ["CompanyB","CompanyA","CompanyC","CompanyB","CompanyA","CompanyD","CompanyE","CompanyF","CompanyG"],...
    [5.99,4.99,14.99,12.99,17.99,4.99,149.99,10.99,5.99],...
    [1000,350,225,25,600,300,50,100,1250],...
    repmat(datetime(2023,1,1),1,9))
% View the last object
productArray(end)

ans = 
  Product with properties:

               ID: 10
             Name: "Toy10"
      Description: "Descr10"
         Quantity: 1250
      CostPerItem: 5.9900
         Supplier: "CompanyG"
    InventoryDate: 01-Jan-2023

Use ormwrite to insert multiple objects at the same time.

% Insert the object array into the database and view the results
ormwrite(conn,productArray);
sqlread(conn,"products")

ans=10×7 table
    ProductNumber     Name      Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    _______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"      "Descr1"           0        24.99      "CompanyA"     "2023-01-01 00:00:00.000000"
          2          "Toy2"      "Descr2"        1000         5.99      "CompanyB"     "2023-01-01 00:00:00.000000"
          3          "Toy3"      "Descr3"         350         4.99      "CompanyA"     "2023-01-01 00:00:00.000000"
          4          "Toy4"      "Descr4"         225        14.99      "CompanyC"     "2023-01-01 00:00:00.000000"
          5          "Toy5"      "Descr5"          25        12.99      "CompanyB"     "2023-01-01 00:00:00.000000"
          6          "Toy6"      "Descr6"         600        17.99      "CompanyA"     "2023-01-01 00:00:00.000000"
          7          "Toy7"      "Descr7"         300         4.99      "CompanyD"     "2023-01-01 00:00:00.000000"
          8          "Toy8"      "Descr8"          50       149.99      "CompanyE"     "2023-01-01 00:00:00.000000"
          9          "Toy9"      "Descr9"         100        10.99      "CompanyF"     "2023-01-01 00:00:00.000000"
         10          "Toy10"     "Descr10"       1250         5.99      "CompanyG"     "2023-01-01 00:00:00.000000"

Read Objects from a Database

Once a class has been mapped to an existing database table, objects of that class can be constructed by reading data from the database.

Use the ormread method to read data from the database. This method uses the mapping to determine which tables to read, and also determines how the column values correspond to the properties.

% Clear all Product objects from the workspace
clear toy productArray

% Recreate the objects by reading from the database and view the first and
% last
allProducts = ormread(conn,"Product")
allProducts(1)

ans = 
  Product with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 0
      CostPerItem: 24.9900
         Supplier: "CompanyA"
    InventoryDate: 01-Jan-2023

allProducts(end)

ans = 
  Product with properties:

               ID: 10
             Name: "Toy10"
      Description: "Descr10"
         Quantity: 1250
      CostPerItem: 5.9900
         Supplier: "CompanyG"
    InventoryDate: 01-Jan-2023

Read in a Subset of Objects

Use the ormread method with the RowFilter name-value argument to import a subset of the objects in the database.

Filter the items where CostPerItem is less than $10.

rf = rowfilter("CostPerItem");
rf = rf.CostPerItem < 10;
inexpensiveItems = ormread(conn,"Product",RowFilter=rf)

% Verify by checking the properties of one of the objects
inexpensiveItems(1)

ans = 
  Product with properties:

               ID: 2
             Name: "Toy2"
      Description: "Descr2"
         Quantity: 1000
      CostPerItem: 5.9900
         Supplier: "CompanyB"
    InventoryDate: 01-Jan-2023

Update the Database with Objects

The ormupdate method updates existing rows in a database table based on changes to one or more mapped MATLAB® objects.

Use the receiveProduct method of the Product class to increase the inventory of Toy1.

% Find the Toy1 product on the database
rf = rowfilter("Name");
rf = rf.Name == "Toy1";
toy = ormread(conn,"Product",RowFilter=rf)

toy = 
  Product with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 0
      CostPerItem: 24.9900
         Supplier: "CompanyA"
    InventoryDate: 01-Jan-2023

% Use the receiveProduct method of Product to increase the amount of
% products in the inventory
toy = recieveProduct(toy,500)

toy = 
  Product with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 500
      CostPerItem: 24.9900
         Supplier: "CompanyA"
    InventoryDate: 14-Dec-2023

Use the fetch function to see that these changes are not reflected in the database.

fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")

ans=1×7 table
    ProductNumber     Name     Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    ______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"     "Descr1"         0         24.99       "CompanyA"     "2023-01-01 00:00:00.000000"

Use the ormupdate method to push the changes made in MATLAB® to the database. Then, use the fetch function to verify that Quantity and InventoryDate are updated in the database.

ormupdate(conn,toy);
fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")

ans=1×7 table
    ProductNumber     Name     Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    ______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"     "Descr1"        500        24.99       "CompanyA"     "2023-12-14 00:00:00.000000"

Refresh Objects to Match the Database

You can refresh an object in MATLAB® to reflect the current state of the database. First, change the quantity of Toy1 to 1000 and view the result using the fetch function.

execute(conn,"UPDATE products SET Quantity = 1000 WHERE Name = 'Toy1'");
fetch(conn,"SELECT * FROM products WHERE Name = 'Toy1'")

ans=1×7 table
    ProductNumber     Name     Description    Quantity    UnitCost    Manufacturer           InventoryDate        
    _____________    ______    ___________    ________    ________    ____________    ____________________________

          1          "Toy1"     "Descr1"        1000       24.99       "CompanyA"     "2023-12-14 00:00:00.000000"

Use the ormread method to refresh the properties of the object.

toy = ormread(conn,toy)

toy = 
  Product with properties:

               ID: 1
             Name: "Toy1"
      Description: "Descr1"
         Quantity: 1000
      CostPerItem: 24.9900
         Supplier: "CompanyA"
    InventoryDate: 14-Dec-2023

clear allProducts inexpensiveItems toy
close(conn)