How to get raw sql for migrations

[Alksar]

Hi, thanks for the project.

How to properly get raw sql for migrations in fluent style?

[ademchenko]

Hello, @Alksar , your question is unclear. Please, give an example of what you want.

[jzabroski]

Hi @Alksar ,

If you want to insert custom raw sql into an expression, you can use Raw.Sql(string) signature. This is used for escaping data within insert, update and delete expressions.

If you want to get the raw sql generated for a migration, then we call that a script. There is a command line option to generate a script via Preview Mode.

If you want something else, please explain like @ademchenko suggested. I'm going to move this Issue to a Discussion since it's not really an issue.

[jzabroski]

@GoodSimonVM This is covered in the documentation.

[rklec]

@jzabroski Same idea here, I want to test in an assertion that my migration produces actually the same SQL as some other tooling.

I did not found that in the documentation. Could you point me to a solution?

[jzabroski]

@rklec How will you account for subtle nuances like whitespace, etc?

If you want to know how to unit test Fluentmigrator, then did you consider reading a few of the thousands of tests? Tongue in cheek, but serious suggestion.

Other than that, I would just create two databases and run all your migrations against one, and your other tool against the other, and use a Schema Compare tool like DevArt for the last bits. I understand this isn't automated, but depends on your exact needs. I find it hard to imagine you want to continuously regress both tools, as that's twice the maintenance cost.

[rklec]

Well... whitespace ignoring is easy.

Other than that, I would just create two databases and run all your migrations against one, and your other tool against the other, and use a Schema Compare tool like DevArt for the last bits. I understand this isn't automated, but depends on your exact needs.

Yeah, actually it could be automated. I would also be able to use the raw SQL to create the DB schema and run integration tests against it. Or use FluentMigrator as usual, of course.
And I guess this is the idea, I'll be following. As a real integration test running against a real DB may be more useful, actually.

BTW here is me arguing and fiddling with ChatGPT to ask it how to get the RAW SQL for further reference.

[jzabroski]

It is possible ChatGPT is confused by Umbraco CMS code it may be aware of in its ll, which may have taken the Apache 2.0 license we offer to its extreme and created a non attributed fork.

Mildly cool, but Fluent NHibernate and other hallucinations suggest it would need fine tuning to be more powerful. Hopefully in ten years I don't get github issues from people complaining about how stupid Copilot is and they don't actually want to read code. Or maybe open source will twist and become trendy to support due to people not knowing otherwise.