Database migrations in Thinktecture.IdentityServer
This is post 2 in a short 3 part series on describing the database support in v2 of Thinktecture.IdentityServer. The parts of this series are:
- Database support in Thinktecture IdentityServer.
- EF migrations in Thinktecture IdentityServer (this post).
- Integrating Thinktecture IdentityServer database with an existing database.
In IdentityServer we use EF as the data access technology. Since we anticipate the possibility of database schema changes in the future, we’ve also then started using EF’s code first migrations feature to try to provide a smooth upgrade path for anyone that needs to upgrade to future versions.
As I pointed out in the previous post, we also support any EF compatible database. The unfortunate aspect of this is that code first migrations are database/provider specific. So this means we need a migration for each possible database that would be used. This is unfortunate and tedious for us since we don’t know all of the database providers that might be used, but we made some safe assumptions that SqlCe 4.0 and SqlServer would be the main ones so those are the migrations we have checked into the source code repository. We’re assuming/hoping that AzureSql will be compatible with SqlServer and thus don’t have a specific one for SqlAzure. If SqlAzure is in fact different than SqlServer or if you have other database providers then we’d be happy for you to contribute those migrations.
To see the migrations you will need to open the solution and navigate to the “Repositories” project. It contains directories for the two supported database providers (SqlCe and SqlServer). Checked in is the actual code migration (.cs file) and also the equivalent SQL (.sql file). This way you can run the migration either from within Visual Studio or you can use the SQL file and run it directly against your database.
To run the migration from Visual Studio you need to run the package manager console by choosing the menu from Tools –> Library Package Manager –> Package Manage Console.
And this is what the console window looks like. You’ll need to pick the “Repository” project as the Default Project (notice the drop-down in the top right corner of the window):
When you run commands since we have different database providers we’ll need to specify which one we want to use. This is why the connection strings configuration in the WebSite project has multiple entries (~/Configuration/connectionStrings.config):
<connectionStrings> <!-- configuration data like endpoints, protocol config, relying parties etc... --> <add name="IdentityServerConfiguration" connectionString="server=localhost;database=IdentityServerConfiguration;trusted_connection=yes;" providerName="System.Data.SqlClient" /> <add name="SqlServer" connectionString="server=localhost;database=IdentityServerConfiguration;trusted_connection=yes;" providerName="System.Data.SqlClient" /> <add name="SqlCe" connectionString="Data Source=|DataDirectory|\IdentityServerConfiguration.sdf" providerName="System.Data.SqlServerCe.4.0" /> </connectionStrings>
The IdentityServerConfiguration entry is for runtime and the SqlServer and SqlCe entries are for running migrations from within the package manager console.
Once the console is up and you know which database you’d like to configure, you can then create and/or upgrade it to a particular migration (which usually would be the latest one). To do so run the command:
Update-Database -TargetMigration:InitialMigration -ConnectionStringName:SqlServer -ConfigurationTypeName:SqlServerConfiguration
TargetMigration indicates the name of the migration (which is the class name inside the .cs migration file). ConnectionStringName is which connection and database provider to use from the .config file. ConfigurationTypeName is which migration to use (SqlCeConfiguration or SqlServerConfiguration). You should get output like this:
You could use the code first approach for auto-creating the database, but using the migrations is the preferred way of creating and managing the database for IdentityServer, mainly because the migrations allow more control over the schema (including indexes, etc.).