Add a column to the Magento 2 sales order database table

So, you want to add a custom field to the sales_order database table now as an order attribute, eh? This can be a bit confusing to newcomers of Magento 2, because of the need to know multiple advanced concepts.

The answer lies with creating a custom database table, and then linking that table to the desired entity with extension attributes. Let's break this down from step 1 to make this as simple as possible.

The first rule of modifying

The first step of adding a field to a core database table? You don't.

This is because you Never Modify The Core™️, and this not only applies to code, but also to other areas of the platform including the database layer. So what is one to do?

Relationships to core tables

The only natural way is create your own custom database table is to create a relationship between it and the core table.

We will add the vendorname_modulename prefix to all of our database tables to avoid naming collisions with other tables. Since the table we want to create a relationship with is named sales_order, we will also suffix it with that name.

Given a vendor name of Acme and a module name of PoNumber, the name of our custom table becomes:

sales_order -> acme_ponumber_sales_order

Create a custom table

Now that we know the name of the table to create, let's go ahead and create it. The "attribute" we'd like to create will be for PO Numbers, or purchase order numbers, which are used by business customers for their internal purchase tracking needs.

This will be a 1:1 table, meaning that only one record is to be assigned per order. Put another way, every single order should only have one PO Number assigned to it. In order to enforce this 1:1 relation, we must also add a unique constraint to the order_id column. By doing this, if subsequent rows are added for an order_id that already exists in another row of the database, it will error out, which will enforce the integrity of our database.

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="acme_ponumber_sales_order" engine="innodb" comment="Sales orders">
        <!-- The standard auto-incrementing id of the table. -->
        <column xsi:type="int" name="id" padding="10" unsigned="true" nullable="false" identity="true" comment="Id"/>

        <!-- The reference to the order id. -->
        <column xsi:type="int" name="order_id" padding="10" unsigned="true" nullable="false" comment="Order id"/>

        <!-- The column we want to create is a free-form text field for a PO Number. -->
        <column xsi:type="text" name="po_number" comment="Po number"/>

        <!-- This is the primary key constraint for the database table. -->
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id"/>
        </constraint>
        
        <!-- In order to enforce a 1:1 relation, create a unique constaint. -->
        <constraint xsi:type="unique" referenceId="ACME_PONUMBER_SALES_ORDER_ORDER_ID">
            <column name="order_id"/>
        </constraint>

        <!-- Create a foreign key constraint to the related table to be related, which in this case is sales_order. -->
        <constraint xsi:type="foreign" referenceId="ACME_PONUMBER_SALES_ORDER_ORDER_ID_SALES_ORDER_ENTITY_ID" table="acme_ponumber_sales_order" column="order_id" referenceTable="sales_order" referenceColumn="entity_id" onDelete="CASCADE"/>
    </table>
</schema>

What's up with the long referenceId value of ACME_PONUMBER_SALES_ORDER_ORDER_ID_SALES_ORDER_ENTITY_ID?

• ACME_PONUMBER_SALES_ORDER = current table
• ORDER_ID = local column name of related foreign column
• SALES_ORDER = foreign table reference
• ENTITY_ID = foreign column to reference

This standard convention helps us find & debug indexes.

If we then inspect the table after running bin/magento setup:upgrade, we'll see the table has been successfully created with the proper indexes.

Let's also create some quick placeholder data here to test out our newly found functionality:

Remember to also generate your DB declaration whitelist file by running:

bin/magento setup:db-declaration:generate-whitelist --module-name=Acme_PoNumber

Running this command creates a file next to your db_schema.xml file named db_schema_whitelist.json. This JSON file ensures that whenever your db_schema.xml file is changed, the database schema will be dynamically updated along with it whenever bin/magento setup:upgrade is executed.

Create a model, resource model & collection

We have now populated the structure of our new database table, but the PHP files need to be setup so we can communicate with it. At the very least, this involves the creation of a model, resource model & collection.

Model

<?php declare(strict_types=1);

namespace Acme\PoNumber\Model;

use Magento\Framework\Model\AbstractModel;

class SalesOrder extends AbstractModel
{
    protected function _construct(): void
    {
        $this->_init(ResourceModel\SalesOrder::class);
    }
}

Resource Model

<?php declare(strict_types=1);

namespace Acme\PoNumber\Model\ResourceModel;

use Magento\Framework\Model\ResourceModel\Db\AbstractDb;

class SalesOrder extends AbstractDb
{
    /** @var string Main table name */
    const MAIN_TABLE = 'acme_ponumber_sales_order';

    /** @var string Main table primary key field name */
    const ID_FIELD_NAME = 'id';

    protected function _construct(): void
    {
        $this->_init(self::MAIN_TABLE, self::ID_FIELD_NAME);
    }
}

Collection

<?php declare(strict_types=1);

namespace Acme\PoNumber\Model\ResourceModel\SalesOrder;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;
use Acme\PoNumber\Model\SalesOrder;

class Collection extends AbstractCollection
{
    protected function _construct(): void
    {
        $this->_init(SalesOrder::class, \Acme\PoNumber\Model\ResourceModel\SalesOrder::class);
    }
}

Now that our data model is complete, let's talk about how we can connect this database table to the core sales_order table.

About extension attributes

Now that we have created our custom table, we need a way to "link" the data within that table to the core table. This is done through a feature in Magento 2 known as extension attributes.

When we query the Magento API to get order details, we'll get something like this:

{
  "applied_rule_ids": "1",
  "base_currency_code": "USD",
  "base_discount_amount": 0,
  "base_discount_invoiced": 0,
  "base_grand_total": 36.39,
  "base_discount_tax_compensation_amount": 0,
  "base_discount_tax_compensation_invoiced": 0,
  "base_shipping_amount": 5,
  "base_shipping_discount_amount": 0,
  "base_shipping_discount_tax_compensation_amnt": 0,
  "base_shipping_incl_tax": 5,
  "base_shipping_invoiced": 5,
  ...
  "extension_attributes": {
    ...
    "payment_additional_info": [
      {
        "key": "method_title",
        "value": "Check \/ Money order"
      }
    ],
    "applied_taxes": [
      {
        "code": "US-MI-*-Rate 1",
        "title": "US-MI-*-Rate 1",
        "percent": 8.25,
        "amount": 2.39,
        "base_amount": 2.39
      }
    ],
    ...
  }
}

All of this info is in a basic structure which we do not want to tamper with, because there may be third-party tools that rely on this schema. That said, notice the last root property named extension_attributes. This is a place that Magento created for third-parties, including us, to add our own custom properties.

The internal Magento API also works similarly. Since the data will be placed within an extension_attributes property, rather than calling $order->getPoNumber() we would call $order->getExtensionAttributes()->getPoNumber().

In order to be able to make this work though, we must first add the PO Number data to the Order model by using a plugin.

Create a plugin

Let's create a plugin, which will allow us to easily add data to queries made to the order repository.

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Sales\Api\OrderRepositoryInterface">
        <plugin name="acme_ponumber_add_po_number_to_sales_order" type="Acme\PoNumber\Plugin\AddPoNumberToSalesOrder"/>
    </type>
</config>

<?php

declare(strict_types=1);

namespace Acme\PoNumber\Plugin;

use Acme\PoNumber\Model\ResourceModel\SalesOrder\Collection;
use Acme\PoNumber\Model\ResourceModel\SalesOrder\CollectionFactory;
use Magento\Sales\Api\OrderRepositoryInterface;

class AddPoNumberToSalesOrder
{
    private CollectionFactory $acmeSalesOrderCollectionFactory;

    /**
     * AddPoNumberToSalesOrder constructor.
     * @param CollectionFactory $acmeSalesOrderCollectionFactory
     */
    public function __construct(
        CollectionFactory $acmeSalesOrderCollectionFactory
    ) {
        $this->acmeSalesOrderCollectionFactory = $acmeSalesOrderCollectionFactory;
    }

    /**
     * @param OrderRepositoryInterface $subject
     * @param $result
     * @return mixed
     */
    public function afterGet(
        OrderRepositoryInterface $subject,
        $result
    ) {
        // We must first grab the record from our custom database table by the order id.

        /** @var Collection $acmeSalesOrder */
        $acmeSalesOrderCollection = $this->acmeSalesOrderCollectionFactory->create();
        $acmeSalesOrder = $acmeSalesOrderCollection
            ->addFieldToFilter('order_id', $result->getId())
            ->getFirstItem();

        // Then, we get the extension attributes that are currently assigned to this order.
        $extensionAttributes = $result->getExtensionAttributes();

        // We then call "setData" on the property we want to set, wtih the value from our custom table.
        $extensionAttributes->setData('po_number', $acmeSalesOrder->getData('po_number'));

        // Then, just re-set the extension attributes containing the newly added data...
        $result->setExtensionAttributes($extensionAttributes);

        // ...and finally, return the result.
        return $result;
    }

    /**
     * @param OrderRepositoryInterface $subject
     * @param $result
     * @return mixed
     */
    public function afterGetList(
        OrderRepositoryInterface $subject,
        $result
    ) {
        // We do the same thing here, and can save some time by passing the logic to afterGet.
        foreach ($result->getItems() as $order) {
            $this->afterGet($subject, $order);
        }

        return $result;
    }
}

After setting this up, let's make another curl request.

We didn't touch base on how to make CURL requests to the Magento API, but put simply:

Get a token by passing in your username/password as Basic Auth:

• POST https://magento.test/rest/V1/integration/admin/token

Then get the order details by passing in the Bearer Token to:

• GET https://magento.test/rest/V1/orders/1

The call will succeed, but the po_number will still not show in the resulting JSON object. Why? This happens because data added to extension attributes must also be defined within an XML file.

Create an extension_attributes.xml file

We're nearing the last step here, and that is to define our newly created attribute inside an extension_attributes.xml file.

This is quite simple. The value for "for" will be the interface we wish to add data to, in this case OrderInterface, and then we define the attribute code and type of attribute to add:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Api/etc/extension_attributes.xsd">
    <extension_attributes for="Magento\Sales\Api\Data\OrderInterface">
        <attribute code="po_number" type="string"/>
    </extension_attributes>
</config>

Upon adding this file, we will now see the po_number added to the resulting JSON object after making our API call.

Now that the extension_attributes.xml has been created, we can also change setData() to use a magic method if we wish, which would be setPoNumber(). We don't need to do this, but this is also to show how the extensibility works in Magento.

$extensionAttributes->setPoNumber($acmeSalesOrder->getData('po_number');

You will now be able to use the following line in any other custom code as you wish, and the related PO Number will always be returned.

$order->getExtensionAttributes()->getPoNumber();

Exceptions?

While I suggested never modifying the core above... there are exceptions. In certain situations, it's perfectly acceptable to add columns to existing database tables when the amount of boilerplate code is disproportionate to the output of what you are trying to do.

For example, in my Customize the Magento 2 Checkout course, I do explain how to add custom fields to the core quote_address and sales_order_address database tables.

Is this bad practice? Not necessarily, because in this case, the data stored was simpler scalar data (number, booleans, strings, etc.) and not complex objects. Additionally, creating custom pivot tables for these two database tables would create a ridiculous amount of boilerplate code which would far surpass the output of what we are trying to accomplish.

In any case, be sure to use your best judgement rather than blindly following suggestions for "best practices". No best practice is written in stone, so it's ok to deviate from them when situations present themselves, and when it makes sense to do so.

What next?

Now that you have your custom extension attribute setup, try experimenting with new things. For example, you can add a new column to an admin grid, create an admin form which saves the value of this extension attribute, or create a data repository for your data model and update your plugin to use that repository instead.

Want to reference the code in this article? Check it out on GitHub.

If you were having a bit of trouble following along, here are a few resources that may help:

1. Explore Magento 2 fundamentals & best practices course (1,000+ students)
2. Grow your Magento expertise with all courses & lessons (700+ students)
3. Learn visually with blocks of code & inline comments (3,000+ students)