C#•2y ago

.NET EF - How to generate a new database in runtime (dynamically)?

Hello,
I have the following logic, a user can create multiple projects via an API call. For each project I would need to create a new database from a migration, so each project would have different database.

How is this achievable?

ben•4/4/24, 10:52 AM

In EF, databases are by default created at runtime anyway, when you start up the application, if they don't already exist. You can just go about it in the same manner, passing your dynamic database name into and ensure it's being created with

CoreOP•4/4/24, 10:52 AM

Thank you! I will look into this

ben•4/4/24, 10:59 AM

No problem!

ben•4/4/24, 11:00 AM

Bben Click to see attachment

Jimmacle•4/4/24, 2:26 PM

EnsureCreated shouldn't be used with migrations, and EF doesn't do any kind of migration or schema modification by default at runtime

Jimmacle•4/4/24, 2:27 PM

you have to pick one or the other

Jimmacle•4/4/24, 2:29 PM

specifically, it ignores migrations and creates tables based on the current model so trying to run migrations after that will almost definitely fail

Jimmacle•4/4/24, 2:38 PM

is the closest migration-compatible way to do this

Jimmacle•4/4/24, 2:47 PM

i'd just use a little raw ADO.NET to create the database before running the migrations

ben•4/4/24, 2:51 PM

You're assuming this is code first migrations?

Jimmacle•4/4/24, 2:51 PM

not assuming anything, he said he wants to create a brand new DB from migrations at runtime

Jimmacle•4/4/24, 2:53 PM

which would look roughly like

Jimmacle•4/4/24, 2:54 PM

definitely want to sanitize that database name if it's generated from user input

ben•4/4/24, 2:57 PM

That would run all migrations, including the initial one, database would already exist?

Jimmacle•4/4/24, 2:57 PM

the initial migration doesn't create the database

Jimmacle•4/4/24, 2:57 PM

and you can't write a connection string with an initial database that doesn't already exist anyway

ben•4/4/24, 2:59 PM

Huh? I think you can?

Jimmacle•4/4/24, 3:00 PM

i stand corrected, looks like EF core at least will try to create the database if you try to run migrations on one that doesn't exist

Jimmacle•4/4/24, 3:01 PM

so my code is useless, just is all you need

ben•4/4/24, 3:01 PM

Yep, I didn't really extend my answer to migrations, so thanks!

Angius•4/5/24, 7:49 AM

Why exactly do you want to use a separate database per project?

Angius•4/5/24, 7:49 AM

Instead of, you know, using the relational part of relational database and simply having a projects table related to the user?