How to Write Data to Custom Database Table and Create a Custom Collection in Magento 2

Recently I created a Magento 2 module which imports data from a custom source, writes it to a custom database table and updates existing entities using a Custom Collection. Because I thought it might come in handy for other beginning Magento 2 developers, I’ll share it with you today.

This tutorial consists of a few parts:

  1. Create a Custom Database Table using an install- or upgradeSchema,
  2. Create a Model and Resource Model,
  3. Write data using that Resource Model,
  4. Generate a Custom Collection using a Factory to read/use the written data.


  1. You’re familiar with the basic requirements of a Magento 2-module (e.g. registration.php and etc/module.xml) and have them in place already,
  2. You have a basic understanding of the programming fundamentals for Magento 2-modules (e.g. dependency injection, namespaces, OOP and installSchemas).

Create a Custom Database Table in Magento 2 with install/upgrade schemas

Before we can write data to it, we must create a custom database table. That way, we can create a collection that exactly suits our needs.

In this example we are creating a module, which will create a table containing a list of Product SKU’s and quantities.

Assuming you’ve already created your module’s registration.php and etc/module.xml we must do the following:

Create an Install/Upgrade Schema

InstallSchemas run once upon install. UpgradeSchemas runs every time bin/magento setup:upgrade runs. That’s why in this example I’ll create an UpgradeSchema-class. The build up of an InstallSchema is basically the same.

namespace DaanvdB\ProductQty\Setup;
use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\SchemaSetupInterface;
use Magento\Framework\Setup\ModuleContextInterface;
class UpgradeSchema implements UpgradeSchemaInterface {
public function upgrade( SchemaSetupInterface $setup, ModuleContextInterface $context ) {
// Create table and columns.
$table = $setup->getConnection()->newTable(
$setup->getTable( 'daanvdb_product_qty' )
'nullable' => false,
'primary' => false
'Product SKU'
'nullable' => false,
'primary' => false
// UPDATE OCT 4TH, 2018: And if you want to add a Foreign Key Constraint
$setup->getFkName( 'daanvdb_product_qty', 'quote_id', 'other_table', 'other_column' ),
$setup->getTable( 'other_table' ),
\Magento\Framework\DB\Ddl\Table::ACTION_CASCADE //onDelete
$setup->getConnection()->createTable( $table );
// And if you want to add a column to an existing table...
if ( version_compare( $context->getVersion(), '2.0' < 0 ) ) {
$table = $setup->getTable( 'daanvdb_product_qty' );
'type' => \Magento\Framework\DB\Ddl\Table::TYPE_BOOLEAN,
'nullable' => false,
'comment' => 'Custom Column'

Now you can run bin/magento setup:upgrade and Magento 2 will automatically create a custom database table, containing two columns (sku and qty). Now let’s fill it with data…

❤️ it? Share it!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Shopping Cart
  • Your cart is empty.