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 as an order attribute, eh?

To add a column to sales_order, declare it in your own module's etc/db_schema.xml under <table name="sales_order">, run setup:db-declaration:generate-whitelist and setup:upgrade, and you're done. If the data should stay out of the core table, store it in your own table and expose it on the order as an extension attribute, as shown below.

There are two routes here, and both are legit. Let's start with the quick one.

Route 1: add the column to sales_order directly

You've probably heard that you Never Modify The Core™️. That still holds, but it's about core files. Declarative schema lets any module add columns to a core table from its own db_schema.xml. You aren't editing core files, and Magento merges your declaration with the core one. This is Adobe's documented way to extend a core table.

<?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="sales_order">
        <column xsi:type="varchar" name="po_number" length="255" nullable="true" comment="PO Number"/>
    </table>
</schema>

Then generate the whitelist and apply it:

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

That's the whole thing. $order->getData('po_number') and $order->setData('po_number', ...) now work, and the value saves with the order.

Two follow-ups depending on where the value comes from and where it shows up:

• Collected at checkout? Add the same column to the quote table, and copy it across when the quote converts to an order with an etc/fieldset.xml:

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:DataObject/etc/fieldset.xsd">
    <scope id="global">
        <fieldset id="sales_convert_quote">
            <field name="po_number">
                <aspect name="to_order"/>
            </field>
        </fieldset>
    </scope>
</config>

• Shown in the admin order grid? The grid reads from sales_order_grid, not sales_order. Add the column to that table too, and add it to the columns argument of the Magento\Sales\Model\ResourceModel\Order\Grid virtual type in your di.xml so Magento copies it over when it refreshes the grid row.

Should I add a column to sales_order or use an extension attribute?

A column on sales_order is the right call for simple scalar data (a string, a number, a flag) that belongs to the order. It's less code and it's fast to query.

Use your own table plus an extension attribute when you want the data decoupled from the core table, when it's more than one scalar, or when you need it to show up cleanly in the REST and GraphQL order APIs as part of extension_attributes. The rest of this article walks through that route.

Route 2: a custom table linked to sales_order

The clean way is to create your own table and relate it to the core one.

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 varchar field for a PO Number. -->
        <column xsi:type="varchar" name="po_number" length="255" nullable="true" 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 constraint. -->
        <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 add a test row so there's something to read back:

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 */
    public const MAIN_TABLE = 'acme_ponumber_sales_order';
 
    /** @var string Main table primary key field name */
    public 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\CollectionFactory;
use Magento\Sales\Api\Data\OrderExtensionFactory;
use Magento\Sales\Api\Data\OrderInterface;
use Magento\Sales\Api\Data\OrderSearchResultInterface;
use Magento\Sales\Api\OrderRepositoryInterface;
 
class AddPoNumberToSalesOrder
{
    public function __construct(
        private readonly CollectionFactory $acmeSalesOrderCollectionFactory,
        private readonly OrderExtensionFactory $orderExtensionFactory,
    ) {
    }
 
    public function afterGet(
        OrderRepositoryInterface $subject,
        OrderInterface $result,
    ): OrderInterface {
        // Grab the record from our custom database table by the order id.
        $acmeSalesOrder = $this->acmeSalesOrderCollectionFactory->create()
            ->addFieldToFilter('order_id', $result->getEntityId())
            ->getFirstItem();
 
        $this->setPoNumber($result, $acmeSalesOrder->getData('po_number'));
 
        return $result;
    }
 
    public function afterGetList(
        OrderRepositoryInterface $subject,
        OrderSearchResultInterface $result,
    ): OrderSearchResultInterface {
        $orders = $result->getItems();
        if (!$orders) {
            return $result;
        }
 
        // One query for every order in the list, rather than one query per order.
        $orderIds = array_values(array_map(
            static fn (OrderInterface $order) => $order->getEntityId(),
            $orders
        ));
        $acmeSalesOrders = $this->acmeSalesOrderCollectionFactory->create()
            ->addFieldToFilter('order_id', ['in' => $orderIds]);
 
        $poNumbers = [];
        foreach ($acmeSalesOrders as $acmeSalesOrder) {
            $poNumbers[$acmeSalesOrder->getData('order_id')] = $acmeSalesOrder->getData('po_number');
        }
 
        foreach ($orders as $order) {
            $this->setPoNumber($order, $poNumbers[$order->getEntityId()] ?? null);
        }
 
        return $result;
    }
 
    private function setPoNumber(OrderInterface $order, ?string $poNumber): void
    {
        // getExtensionAttributes() can return null, so fall back to a new extension object.
        $extensionAttributes = $order->getExtensionAttributes() ?? $this->orderExtensionFactory->create();
 
        // Call "setData" on the property we want to set, with the value from our custom table.
        $extensionAttributes->setData('po_number', $poNumber);
 
        // Then re-set the extension attributes containing the newly added data.
        $order->setExtensionAttributes($extensionAttributes);
    }
}

afterGetList() collects every order ID first and runs a single order_id IN (...) query. Calling afterGet() in a loop would work too, but it runs one query per order, which adds up fast on an order grid or a big API export.

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

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

Create an integration in the admin (System > Extensions > Integrations), activate it and copy its access token. To use that token as a bearer token, enable Stores > Configuration > Services > OAuth > Consumer Settings > "Allow OAuth Access Tokens to be used as standalone Bearer tokens" (it's off by default since 2.4.4).

Then get the order details by passing the token in an Authorization: Bearer <token> header to:

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

You can also request an admin token with POST /rest/V1/integration/admin/token and a JSON body of {"username": "...", "password": "..."}, but with Magento_TwoFactorAuth enabled (the default), that endpoint won't hand you a token without completing 2FA. See Adobe's token authentication docs for the details.

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, Magento generates a real setPoNumber() method on the order's extension object, so we can swap setData() for it if we wish. We don't need to, but it shows how the extensibility works in Magento:

$extensionAttributes->setPoNumber($poNumber);

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();

Saving the PO number

So far we only read the PO number. To store one, add an afterSave() method to the same plugin. It writes whatever is on the order's extension attributes into our table, creating the row if it doesn't exist yet. Inject the resource model alongside the collection factory:

use Acme\PoNumber\Model\ResourceModel\SalesOrder as SalesOrderResource;
 
// ...add to the constructor:
//     private readonly SalesOrderResource $salesOrderResource,
 
public function afterSave(
    OrderRepositoryInterface $subject,
    OrderInterface $result,
    OrderInterface $entity,
): OrderInterface {
    $poNumber = $entity->getExtensionAttributes()?->getPoNumber();
    if ($poNumber === null) {
        return $result;
    }
 
    // getFirstItem() returns a new, empty model when no row exists yet.
    $acmeSalesOrder = $this->acmeSalesOrderCollectionFactory->create()
        ->addFieldToFilter('order_id', $result->getEntityId())
        ->getFirstItem();
    $acmeSalesOrder->setData('order_id', (int) $result->getEntityId());
    $acmeSalesOrder->setData('po_number', $poNumber);
    $this->salesOrderResource->save($acmeSalesOrder);
 
    return $result;
}

Now $order->getExtensionAttributes()->setPoNumber('PO-1234') followed by $orderRepository->save($order) persists the value.

When the quick route is the right one

Route 2 is a lot of code for one string. That's why I don't treat a custom table as a rule. In my Customize the Magento 2 Checkout course, I add custom fields straight to the core quote_address and sales_order_address tables.

Is that bad practice? No. The data is simple scalar data (numbers, booleans, strings), and a custom pivot table for each of those tables would be a ridiculous amount of boilerplate for what it does.

For a single scalar value on the order, add the column with declarative schema. Reach for a custom table and extension attribute when the data is complex, shared, or should stay out of the core table. Use your judgement rather than following "best practices" blindly.

What next?

Now that you have your custom extension attribute set up, try experimenting. You can add a new column to an admin grid, create a custom admin grid and form that saves the value of this extension attribute, or create a repository for your data model and update your plugin to use it instead of the collection.

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