Generate SQL scripts to create the entire DB (not only tables)

[piskov]

Form the docs:

EnsureCreated will create the database if it doesn't exist and initialize the database schema.

…and below that:

To get the SQL used by EnsureCreated, you can use the GenerateCreateScript method.

One would think that GenerateCreateScript will get you the SQL to create the entire DB but it’s not — only SQL for tables. If EnsureCreated generates some CREATE DATABASE %your db name% SQL under the hood, it would be nice to have it as well.

Use case

When migrating between the DB engines (e.g. SQL Server → PostreSQL) one would appreciate to have a DB creation SQL as well (as a separate script or same like SQL Management Studio generates in Tasks → Generate Scripts… → Script entire database and all database objects)

[roji]

As I wrote in #10551 (comment), at least for PostgreSQL that's not technically possible, since creating the database must occur when connected to some administrative database, the script itself (table creation) must run on the database (and switch the database for a connection is impossible).

In addition, there are various cloud database systems where the database cannot be created in this way, but rather must be created via the UI or a separate API call. Creating a database also frequently requires a different set of permissions than creating tables in a database that's already created (and presumably owned by the user applying the migrations). All this is to say that if we generated the CREATE DATABASE inside the migration SQL script, that would cause lots of issues for various other users, and for very little gain.

Finally, this really affects only migration SQL scripts. If you use e.g. EF's migration bundle feature, it can do everything for you without any issue. I highly recommend looking at that.

[piskov]

EF's migration bundle feature, it can do everything for you without any issue. I highly recommend looking at that

Will do, thank you