EF Core - create database programmatically

Question

What's the best approach to create a database in EF core programmatically and then run the migrations to make sure the new database has all database objects?

We're building a SaaS based application where subscribers can signup on the website themselves and when they signup providing correct payment details, the application needs to create a new database (in SQL Azure) for the subscriber automatically and execute all migrations on the new database.

I have looked at this question here: auto create database in Entity Framework Core It does not give details on the following: 1) how to specify the name of the new database 2) create a new database login 3) add that new database login to the database as a user with dbo permissions

score 0 · Answer 1 · answered Mar 19 '19 at 18:37

Create an Azure SQL Database Basic tier (for example, it cost $5 a month, you can also create a free one available for a year) with all the objects needed on the database, which you can copy asynchronously with a statement like below or using PowerShell.

CREATE DATABASE db_copy   
    AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

You can then monitor when the copy finishes with below statement:

   Select
     [sys].[databases].[name], 
       [sys].[databases].[state_desc], 
       [sys].[dm_database_copies].[start_date], 
       [sys].[dm_database_copies].[modify_date], 
       [sys].[dm_database_copies].[percent_complete],
             [sys].[dm_database_copies].[error_code], 
       [sys].[dm_database_copies].[error_desc], 
       [sys].[dm_database_copies].[error_severity], 
       [sys].[dm_database_copies].[error_state]
   From
      [sys].[databases]
   Left
 Outer
 Join
 [sys].[dm_database_copies] 
   On
    [sys].[databases].[database_id] = [sys].[dm_database_copies].[database_id]
   Where
   [sys].[databases].[name] = 'db_copy'

EF Core - create database programmatically

1 Answers1