Escaping values in migrations

[pigulla]

Is your feature request related to a problem? Please describe.
When writing migrations, it is not clear how the queries can be constructed safely. While a migration obviously does not contain user input and escaping might not be strictly necessary, it is arguably good form to do so anyway.

Describe the solution you'd like
Provide a built-in solution to escape values. Ideally the knex instance could be used but it is my understanding that this is not feasible due to its lack of support for MongoDB.

Describe alternatives you've considered
Currently I am using pg-format which works well enough, but that means tying my migrations to a specific database, which is not ideal.

Additional context
I've read this issue which asks a related question.

[B4nan]

In Migration class you have access to the AbstractSqlDriver (via this.driver), where you can either use the MikroORM query builder, or get the configured knex instance via this.driver.getConnection().getKnex().

[pigulla]

Thanks for the super quick reply.

It is my understanding that the Knex instance can not be used until the next RC (as mentioned here) - is that correct?

When playing around with this this.driver.getConnection threw TypeError: this.driver.getConnection is not a function during runtime, but that's most likely due to me not using the latest RC (my bad). Now I ran into the escaya issue, but I will just hold still until RC5 is out :)

Thanks a lot for your work, looking forward to using it!

[B4nan]

It is my understanding that the Knex instance can not be used until the next RC (as mentioned here) - is that correct?

That is only if you want to execute a query, not if you use it to build a query for this.addSql().

[pigulla]

How do I do that? As far as I know Knex' toSQL() or (toSQL().toNative()) returns the statement and its (unescaped) bindings as separate values so it is not a query I can pass to addSql.

[B4nan]

You could use toString(), and we could easily allow passing params to addSql() too I guess.

[pigulla]

Maybe I just need some more coffee, but I don't think this is how Knex works:

knex
    .insert({
        id: 42,
        foo: 'bar',
    })
    .into('mytable')
    .toSQL()
    .toNative() // or without this, no difference
    .toString()

just returns [object Object]. Apparently there are only pretty ugly workarounds for this (admittedly, an old ticket - couldn't find anything more recent regarding this issue).

[B4nan]

knex
    .insert({
        id: 42,
        foo: 'bar',
    })
    .into('mytable')
    .toString()

[B4nan]

But we could easily allow to pass the knex qb instance to addSql() too. I feel like the toString() method is more of an approximation - I am using that in the query logging and I know about some differences when it comes to object handling (Dates in particular are printed with lower precision, without timezone, and in sqlite it prints the date string while in fact the query is using int/timestamps under the hood (that is how sqlite3 does things internally afaik).

So I would definitely not suggest to do this. Personally I would just write the SQL myself, it's easier and you have absolute control over everything.

[pigulla]

I'm fine with that, if that's the "official" way to do this. Thanks for the feedback!

[B4nan]

rc.5 will allow to use knex in both execute and addSql methods, there is also a new shortcut to get knex.

[pigulla]

Fantastic!

Do you have an ETA for rc.5 by any chance?