Set up SQL Server (locally)

[ejacobg]

SQL Server Express LocalDB

LocalDB is a feature of SQL Server Express (not SQL Server Developer) that allows you to run lightweight SQL Server instances you can connect to.

Check whether you have LocalDB installed:

sqllocaldb versions

If not, see here: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/sql-server-express-localdb

Customers using Visual Studio 2019 and Visual Studio 2022 should install SQL Server 2019 Express edition.

Create Custom LocalDB Instance

I'll create a custom LocalDB instance for this project rather than using the automatic MSSQLLocalDB instance. This helps keep the automatic instance a little cleaner since several databases need to be created.

sqllocaldb create eShopOnContainers

Custom instances should automatically start when you connect to them, and will automatically stop when inactive, but you can always do this yourself:

sqllocaldb start eShopOnContainers
sqllocaldb stop eShopOnContainers
sqllocaldb info eShopOnContainers

Connecting to the Custom Instance

The connection string to your custom instance looks something like this:

Server=(localdb)\eShopOnContainers;Integrated Security=true
Server=(localdb)\eShopOnContainers;Integrated Security=true;Database=Catalog

If you're connecting via SSMS, you can use the following settings:

• Server type: Database Engine
• Server name: (localdb)\eShopOnContainers
• Authentication: Windows Authentication
• Encryption: Mandatory
• Trust server certificate: (unchecked)
• Host name in certificate: (empty)

[ejacobg]

Creating Your Database

Creating a database before applying your first migration is optional. EF Core will check to see if your database exists before continuing with the migration.

The Update-Database command creates the database and applies the new migration to it.

Creating Migrations

Create your entities and contexts as usual.

For reasons I don't entirely understand, it's not enough to just run your migration commands against your startup project. eShopOnContainers made use of the IDesignTimeDbContextFactory<T> to connect to the database. These context factories are also necessary in order to generate the migrations for the IntegrationEventLog.

• The IntegrationEventLogContext is in a different project from the Catalog API, but we still want the migrations to be made against the Catalog's database and for the migration files to be written to the Catalog.Api project.

Add-Migration -Name InitialCreate -Context IntegrationEventLogContext -OutputDir Infrastructure/IntegrationEventLogMigrations 

Applying Migrations

Use Update-Database.

Removing Migrations

If you added a migration but haven't applied it, you can remove it to make further changes.

Remove-Migration

This removes the last migration.

Avoid removing migrations that have already been applied. By deleting the migration file, you remove the option of migrating down, and can break assumptions made by future migrations.

Reset All Migrations

In extreme cases, may be easiest to remove all migrations and start over. This can easily be done by deleting your migrations folder and dropping your database (Drop-Database). You can then create a new initial migration which will contain your entire current schema.

Any custom migration code will be lost when your migrations folder is deleted. Any custom code needs to be applied again to the new initial migration.

Seeding Your Database

?