How can I run a SQL query from a file?

Question

I have a module that will be used as a location finder, and it needs to create a table on install that will hold zip codes and their location. That's easy to do. However, I also need to populate that table. Seeing as it's quite a large dataset, and I already have it in a .sql file, I would like to be able to run that file instead of embed the query into the module_install function.

I looked over the documentation for db_query(), but I don't see a way to run a file.

Is this doable, or do I need to pull the database username and password out and run it manually?

Answer 1

You can use drush sql-connect for this, so you don't need to enter any credentials and/or use mysql's command line:

`drush sql-connect` < my-sql-file.sql

The tildes tell your shell to replace the command drush sql-connect with the command's output. Therefore, when you run this line in your terminal, bash will actually see something like:

mysql --database=dbname --host=localhost --user=username --password=password < my-sql-file.sql

Check out Drush's documentation for much, much more.

Answer 2

I'm not aware of any way to "run a file", but you could edit your .sql file to only contain INSERT statements, then add functionality in your module_install() function to execute your statements, rather than embedding all of the statements in the module.install file.

Maybe something like this would work (having not seen how your SQL file is formatted:

function my_module_install() {
  // Get the contents of the .sql file.
  $sql = file_get_contents(drupal_get_path('module', 'my_module') . '/module.sql');
  // Split into individual statements based on new lines.
  $statements = preg_split('/[\n\r]+/', $sql);

  foreach ($statements as $query) {
    db_query($query);
  }
}

You will of course still have to define the schema (which you should do anyway) in the .install file, but this would at least allow you to keep the data out of your code.

Answer 3

I think a better long term solution would be to divorce the act of schema creation with populating the data. For one thing, zip codes do change, and you may want to eventually update the module to support other postal code systems.

I would create your schema in the install file, and then implement a hook_requirements() that checks whether the table has any data in it, and outputs a warning if there are no rows.

Then, make a settings form for your module. Add a $form['zipcodes']['#type']['file'] to it. In the submit handler, you can process the file to populate the zipcode table. This will likely need to use batch processing, though. Inserting nearly 100,000 records will take a while.

You could also make a custom drush command to do the same thing, perhaps even downloading it from a remote location automagically.

Answer 4

The Drupal Database class extends PHP PDO class, so you can use Database::getConnection()->exec(file_get_contents('/path/to/my/file.sql)); to execute arbitrary SQL file, despite the exec method not being in the corresponding Drupal documentation.

Answer 5

Here is what worked for me in Drupal 9:

function MY_MODULE_install() {
  \Drupal::messenger()->addStatus(__FUNCTION__);
  $container = \Drupal::getContainer();
  /** @var \Drupal\Core\Database\Connection $database */
  $database = $container->get('database');
  $database = \Drupal::database();
  // Get and insert the contents of the CSV file.
  $modulePath = \Drupal::service('module_handler')->getModule('MY_MODULE')->getPath();
  $csv = file_get_contents($modulePath . '/data/my.csv');
  $statements = preg_split('/[\n\r]+/', $csv);
  array_pop($statements); // remove that last pesky EOL
  foreach ($statements as $query) {
    list($field1, $field2, $field3) = explode(',', $query);
    $anItem = [
      'field1' => trim($field1,"'"),
      'field2' => trim($field2, "'"),
      'field3' => trim($field3, "'"),
    ];
    $database->insert('MY_TABLE')->fields($anItem)->execute();
  }
}