SQL files in migrations

[remiroyc]

Feature Request

I would like to develop a sql helper for migrate with sequelize-cli and run directly sql files (like a sql backup for example).

I don't know if it's a good idea so i post here for know if it was a common request.

In my mind, i would like a developer friendly syntax like : migrator.executeFile("") to integrate at your migration (on up and down functions).

Imagine, i would like to create a migration which add tables and execute a sql file (my backup) and two days later i would like to undo this. Actually it is too constraining...

If you are interested by this feature, i'll try to propose a pull request.

[felixfbecker]

You could do it like this:

function up() {
  return fs.readFile('up.sql').then(sql => sequelize.query(sql))
}
function down() {
  return fs.readFile('down.sql').then(sql => sequelize.query(sql))
}

I don't get 100% what benefit your migrator.executeFile() API provides

[remiroyc]

Some possible answers: check sql syntax, catch errors, add options for create a query for each line or execute all in one transaction, etc.

[felixfbecker]

check sql syntax

thats the job of the DB, I don't think this should belong inside the migration CLI

catch errors

in my example a failed query would result in a failed migration, as you would expect

add options for create a query for each line

I don't get this one, queries are seperated by semicolons?

execute all in one transaction

sequelize.query('BEGIN;' + sql + 'COMMIT;')

should do it. But that is dangerous because there are DBMS that don't support transactions for schema commands, like MySQL.

[sushantdhiman]

I agree with felix here, its just a matter of getting SQL, sequelize.query can execute it

[alatras]

@felixfbecker With your code I'm getting: ERROR: Callback must be a function UP: Can not migrate! Any idea?

[felixfbecker]

That was using a Promise version of fs. See https://nodejs.org/dist/latest-v10.x/docs/api/fs.html#fs_fs_promises_api or https://www.npmjs.com/package/mz

[alatras]

@felixfbecker Thanks!

[Graig123git]

how did you fix this ? am getting the same error call back must be a function

[AdamBilisic]

@Graig123git use const fs = require("fs").promises;

[Seth10001]

A few other notes here. You will need to use sql.toString() to convert the buffer returned from readFile to a string.

function up() {
  return fs.readFile('up.sql').then(sql => sequelize.query(sql.toString()))
}
function down() {
  return fs.readFile('down.sql').then(sql => sequelize.query(sql.toString()))
}

Also, if your sql file has multiple statements in it (for example a dump file) you will need to split the string up by semicolons.

function up() {
  return fs.readFile('up.sql').then(sql => 
  {
    var promises = []
    var statements = sql.toString().split(';')
    for (var statement of statements)
        if (statement.trim() != '') promises.push(sequelize.query(statement))
    return Promise.all(promises)
   })
}
function down() {
  return fs.readFile('down.sql').then(sql =>
  {
    var promises = []
    var statements = sql.toString().split(';')
    for (var statement of statements)
        if (statement.trim() != '') promises.push(sequelize.query(statement))
    return Promise.all(promises)
   })
}

[udovichenko]

But if I have the semicolons inside my data values, then I get an error after trying to split the dump file by semicolon symbol. Any idea how to distinguish semicolons inside values from statement separators?

[ephys]

umzug is able to execute raw sql files, I think we should support it: https://github.com/sequelize/umzug/tree/main/examples/3.raw-sql. Maybe with a different naming scheme: migrations/2020.11.24T18.00.40.users/up.sql & migrations/2020.11.24T18.00.40.users/down.sql?

[Seth10001]

@udovichenko

But if I have the semicolons inside my data values, then I get an error after trying to split the dump file by semicolon symbol. Any idea how to distinguish semicolons inside values from statement separators?

Ahh good point. In that case you want to ignore any semicolons inside single/double qoutes. You can do so with the following regex:

https://regexr.com/6u7h4

Modified answer:

function up() {
  return fs.readFile('up.sql').then(sql => 
  {
    var promises = []
    var statements = sql.toString().split(/(?!\B"[^"]*)(?!\B'[^']*);(?![^"]*"\B)(?![^']*'\B)/)
    for (var statement of statements)
        if (statement.trim() != '') promises.push(sequelize.query(statement))
    return Promise.all(promises)
   })
}
function down() {
  return fs.readFile('down.sql').then(sql =>
  {
    var promises = []
    var statements = sql.toString().split(/(?!\B"[^"]*)(?!\B'[^']*);(?![^"]*"\B)(?![^']*'\B)/)
    for (var statement of statements)
        if (statement.trim() != '') promises.push(sequelize.query(statement))
    return Promise.all(promises)
   })
}

Based my regex on this SO post: https://stackoverflow.com/a/21106122