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.

Prerequisites

  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).
  • The Wait is Over.

    Get the Newsletter you've always wanted, now!

    Sign up to receive Biweekly, Free Optimization Tips for WordPress.

    No spam. I promise.

  • 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.

    DaanvdB/ProductQty/Setup/UpgradeSchema.php
    <?php
    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 ) {
    $setup->startSetup();
    // Create table and columns.
    $table = $setup->getConnection()->newTable(
    $setup->getTable( 'daanvdb_product_qty' )
    )->addColumn(
    'sku',
    \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
    null,
    [
    'nullable' => false,
    'primary' => false
    ],
    'Product SKU'
    )->addColumn(
    'qty',
    \Magento\Framework\DB\Ddl\Table::TYPE_INTEGER,
    null,
    [
    'nullable' => false,
    'primary' => false
    ]
    );
    // UPDATE OCT 4TH, 2018: And if you want to add a Foreign Key Constraint
    $setup->addForeignKey(
    $setup->getFkName( 'daanvdb_product_qty', 'quote_id', 'other_table', 'other_column' ),
    'quote_id',
    $setup->getTable( 'other_table' ),
    'other_column',
    \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' );
    $setup->getConnection()->addColumn(
    $table,
    'custom_column',
    [
    'type' => \Magento\Framework\DB\Ddl\Table::TYPE_BOOLEAN,
    'nullable' => false,
    'comment' => 'Custom Column'
    ]
    );
    }
    $setup->endSetup();
    }
    }

    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…

    Daan van den Bergh

    Daan van den Bergh is a carefully seasoned web developer. His methods consist of thinly slicing your website’s beef and serve you with a platter of the best performance carpaccio on a bed of rocket — the only thing he’s more passionate about is food. Hire him at ffwp.dev.

    Leave a Comment

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