Can I use a .sql file in my Magento 2 module installer?

Question

I have this large file as an example data.sql and what it does is create table and insert the data when I run import from phpMyAdmin.

How could one use this file in a module installer?

I've found something but it's for Magento 1, here is the link

Thank you in advance.

Answer 1

I've spent about a week looking for a nice and clean solution to this, but haven't had any success with anything that Magento provides so I settled on a quite hacky way, but it easy to implement and it works.

Basically you use Magento\Framework\App\DeploymentConfig to read the database credentials from your env.php and create a mysqli connection to your database. Then read the content of your .sql file into and pass it to mysqli::multi_query().

Here is some code to do that in your InstallSchema.php which assumes that you have a file called install.sql in the setup folder of your module:

<?php

namespace Vendor\Module\Setup;

class InstallSchema implements \Magento\Framework\Setup\InstallSchemaInterface
{

    private $deploymentConfig;
    private $moduleReader;

    public function __construct(
        \Magento\Framework\App\DeploymentConfig $deploymentConfig,
        \Magento\Framework\Module\Dir\Reader $moduleReader
    )
    {
        $this->deploymentConfig = $deploymentConfig;
        $this->moduleReader = $moduleReader;
    }

    public function install(
        \Magento\Framework\Setup\SchemaSetupInterface $setup,
        \Magento\Framework\Setup\ModuleContextInterface $context
    ) {
        $setup->startSetup();


        $setupDir = $this->moduleReader->getModuleDir(
            \Magento\Framework\Module\Dir::MODULE_SETUP_DIR,
            'Vendor_Module'
        );

        $sql = \file_get_contents($setupDir . '/install.sql');

        $dbConnection = new \mysqli(
            $this->deploymentConfig->get('db/connection/default/host'),
            $this->deploymentConfig->get('db/connection/default/username'),
            $this->deploymentConfig->get('db/connection/default/password'),
            $this->deploymentConfig->get('db/connection/default/dbname')
        );

        $dbConnection->multi_query($sql);

        $dbConnection->close();

        $setup->endSetup();
    }
}

Other things I've tried for reference

Reading the sql file line by line and executing them one by one

This doesn't work on all file for obvious reasons. If a query or a comment spreads across multiple lines it doesn't work. If your file is one line per query you could use this approach and use Magento\Framework\DB\Adapter\Pdo\Mysql::query() to execute them. In InstallSchema.php you can get a Magento\Framework\DB\Adapter\Pdo\Mysql object by $setup->getConnection()

Reading the sql file query by query

This adds the complexity of detecting where a query ends and a new one starts, I've not been able to make this work for my specific sql file

Using Magento\Framework\DB\Adapter\Pdo\Mysql::multiQuery()

This method can be used to execute multiple query at once, so theoretically it should work to pass the content of a sql to that method. BUT Magento\Framework\DB\Adapter\Pdo\Mysql::multiQuery() is marked as deprecated since Magento 2.2. Also it didn't work for me. I've tried several sql files which were created by phpMyAdmin with this method and got a lot of syntax errors, although the files were completely valid and importable via other ways (i.e. the way I posted above, mysql cli and phpMyAdmin)