Skip to main content

Migrations and SQL scripts

Choose one way to manage the plugin schema. Set Database:Mode to match.

WayUse whenDatabase:Mode
Automatic createYou are fine with the app creating its tables at startupAuto or Create
EF Core migrationsYou already ship EF Core migrationsAuto or Migrate (or None if CI applies them)
SQL scriptsA DBA reviews and runs every schema changeNone

EF Core migrations​

Keep the migrations in your app:

builder.Services.AddSuperAdmin(builder.Configuration.GetSection("SuperAdmin"),
db => db.UseSqlServer(cs, sql => sql.MigrationsAssembly(typeof(Program).Assembly.GetName().Name)));
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet ef migrations add SuperAdminInit --context SuperAdminDbContext --output-dir Data/SuperAdminMigrations
dotnet ef migrations add SuperAdminVNext --context SuperAdminDbContext --output-dir Data/SuperAdminMigrations # after package upgrades

When migrations exist, InitializeSuperAdminAsync() applies them. To apply them yourself, use Mode = None and run dotnet ef database update --context SuperAdminDbContext in CI/CD.

SQL scripts (no EF migrations)​

The package contains ready scripts for the default prefix sa_ and the default schema. They are in its sql folder (~/.nuget/packages/superadmin.rbac/<version>/sql/):

Folder0001_init.sql0002_user_attributes.sql
sqlserver, postgresql, mysql, oracle, sqliteCreates all tables and indexes, and adds columns of newer versions to existing tablesUpgrade from 0.1.x: adds sa_users.Attributes

Every script is safe to run again:

  • SQL Server wraps each statement in an existence check (and sets ANSI_NULLS / QUOTED_IDENTIFIER).
  • PostgreSQL and SQLite use IF NOT EXISTS.
  • MySQL checks information_schema.
  • Oracle ignores "already exists" errors (ORA-00955, ORA-01408, ORA-01430).
  • SQLite has no ADD COLUMN IF NOT EXISTS, so its upgrade statements are comments you run once.

Generate a script for your configuration​

For another prefix, schema or Oracle 19c, generate the script from your configuration. No database connection is opened:

var sql = app.GenerateSuperAdminSqlScript(); // or IServiceProvider.GenerateSuperAdminSqlScript()

The generated script always contains the upgrade statements too. After a package upgrade, regenerate it and run it again.

Run the script with your usual tooling, then set Database:Mode to None. With Mode = None the app stops at startup and lists any missing table or column.

Command line​

Add one line after builder.Build():

var app = builder.Build();
if (await app.RunSuperAdminCommandAsync(args)) return;
dotnet run -- superadmin sql-script --output superadmin.sql # the create script for this configuration
dotnet MyApp.dll superadmin seed-admin --user admin # create the schema (per Mode) and an admin user

seed-admin reads the password from the SUPERADMIN_ADMIN_PASSWORD environment variable, or asks for it without echoing it. The password is never written to the log.