Within this tutorial, you will learn how you can create and connect a custom database with a Umbraco-powered website. As part of Umbraco v12, Umbraco now ships with improved support for entity framework. This added support should now mean that your life should be a little easier whenever you need to read or store data that does not make sense to live within the CMS itself.

If you are considering upgrading to v12+, now is an ideal time to read up on how you can integrate Entity Framework with Umbraco. Read on, to learn where you should store your data and how you can use Umbraco and entity to either read data from your custom tables or, use entity framework to generate custom database tables for you 🔥🔥🔥

Configuring Entity Framework with Umbraco v12

The first thing that you will need to do is make sure that you have the entity framework CLI tool installed locally as this will make your life easier. You can check what dotnet-related things you have installed locally using this command:

If you have the Entity Framework CLI tool installed locally expect to see an entry called dotnet-ef within your installed app. If you do not see this tool within that returned list, you can install it using this command:

After you have the CLI tool installed, you will also need to ensure that you have the correct entity-related Nuget packages installed within your project. To do that, you can install these packages using these commands using the Nuget terminal:

Just for shits and giggles within this article, I am going to add my custom database tables within SQL lite. For production, I recommend that you use SQL server and I only used SQL Lite to make uploading to GitHub easier 😊. Within this guide, the code I share will work for both, however, as there are more caveats and gotchas with SQLLite I think it is a better option to help you learn as much as possible. The only real difference to using SQL Lite is that you will need to additionally install Microsoft.EntityFrameworkCore.Sqlite.

In order to look at my data within SQLLite, write commands, and check my tables have been created I use DB Browser for SQLite. DB Browser for SQLite is a free UI tool for Windows that allows you to interact with SQL Lite databases. You simply point it at your SQLite database, and off you go. You can download DB Browser for SQLite from here. I use the portal app version! These are the core things you will need to be installed. The next step is to get down and dirty with some data 💾💾💾

CMS Database... Or Not?

The first question that you will need to answer is where you should store your data. In a perfect utopian world, I would personally prefer to use a separate database for storing any separate data rather than the CMS database. The reason why I like this approach is that in the future if you ever want to split up your custom application with the CMS, it is very simple.

This separation obviously has a big downside, cost. In the age of cloud computing creating a second database will come with extra costs. When you factor in scheduling, backups, and disaster recovery, in most instances it won't be worth the trade-off for extra complexity and costings, just to have some form of nice clean abstraction. Unless you have a SQL instance to work with, I tend to package everything within the CMS database, however, the choice is yours...

Code First Database Creation

When it comes to using Entity Framework, you have two options. One option is to use SQL and SSMS to create your database and then use Entity Framework to read data from these pre-existing tables and SPROCS. The alternative option is to define your database tables in code and then use Entity Framework as the tool to generate and create your tables and views for you.

In this article, we will be focusing on how to generate custom database tables from code. The reason for this is simply because this is where the new support released within Umbraco v12 is aimed!

Another thing to note is that the basis of some of the advice I'm providing is based on the official Umbraco/entity framework documentation which can be found here.

The benefit of this guide over that one is that I will be providing you with a more thorough step-by-step walkthrough that will include everything that you need to know in order to get this setup up and running, including hidden caveats and gotchas that you will encounter along the way!

To get started hooking everything up, the first thing to do is to add some code within either Program.cs if you are using minimal hosting, or Startup.cs if you are not. The code you need to add is connection string related. We need to allow the Umbraco to Entity Framework provider (IEFCoreScopeProvider ) to talk to SQL.

Within v12 there is a new extension that you can make use of called AddUmbracoEFCoreContext that will do this for us. You will need to declare this method in order to register the database connection string that the Entity related stuff will use.

The main caveat to be aware of when adding this code is its location. If you are using SQLite, you will need to add the connection command after your call toAddUmbraco(). Apparently, there's some Umbraco registration stuff that needs to happen otherwise the migration scripts you need to run later will not find the SQLite file database within the umbraco ➡ data directory later. The code you will need to add will look like this:

Note that in this code, instead of duplicating or hard-coding connection string settings within AddUmbraco(), it is better to use IConfiguration and read your Umbraco default connection string settings from asppsettings.json.

For this example, I am going to generate a very basic table called CustomUsers. The model that will represent this table in our C# code will look like this:

In order to create tables and talk to your database using Entity Framework you will need to create two files. The first file is the context file. This context class will need to derive from the Entity Framework DbContext class and will be used for accessing application data through Entity Framework. The code to create this context is shown below:

There are a few important things going on here, first, this class inherits from DbContext. The next important thing to note is that we override OnModelCreating. Here we add the code to build our database tables and amp the relations within SQL using ModelBuilder. You can find out more about what you can do with ModelBuilder here. The method you will likely use the most is Entity<T>. This is where you can map your custom C# class to a database table that you want created within SQL. In this example, I map my class CustomUsers to a database called customUsers using ToTable().

Using the Entity Framework Core Fluent API, you can use methods like HasKey to define primary keys, HasRequired to define relationships between tables, and property to define database fields. You can find more about what API options you can use here as well as defining relationships here

It might also be handy for some peeps to point out that if you were trying to hook up Entity Framework using SQLite within a regular vanilla .NET project you would likely need to update OnConfiguring() and add some code that told Entity Framework to use a database provider and not the default in-memory database provider, however, within an Umbraco set-up, this type of stuff is being abstracted for you!

After you have a context and the connection is sorted, it's time to generate a migration script. The migration script in .NET will be a class that contains all the instructions required to generate the stuff within SQL. You can generate these scripts from the terminal by running this command from your project root within a terminal:

If successful this command will add some files within a folder called Migrations. If you get an error, check your connection string in Program.cs is OK. It is also worth pointing out that if you want to update your migrations, or, manually regenerate these scripts you can run the update command within a terminal:

Obviously, auto-generating a script is great, however, you also need a way to run them, otherwise, they are a bit useless. We need a way to execute them. Assuming you will be updating and adding new migration as time progresses, the best way to ensure these scripts are run is on application start-up.

As we are working on a Umbraco project rather than vanilla .NET, you will need to write some code Umbraco-specific code that will run your migrations on website startup. When using Umbraco, you can get code to run on boot-up using a NoficationHandler. It is within this handler that you will add the code to trigger your migrations. Remember, these migrations will need to run before your database tables will get created within your database!

If you were using a vanilla .NET app, you could in theory run a command to generate these migrations for you. The code to create the notification handler would look like this:

After creating your notification handler, within Umbraco you will need to register it with Umbraco using a composer. Add this code within a new class:

You can then build and run your project and your database table should be created within SQL. After doing this open your SQLite database and you should now see a table called CustomUsers and __EFMigrationsHistory. __EFMigrationsHistory will be used by Entity Framework to store the name of the first migration script and each subsequent migration as they are applied to the database. The reason why CustomUsers is created should be obvious.

Reading Data From Existing Databases

With all this code added, you can then build your project and load Umbraco, and your database table should be created within SQL. To check, open your SQLite database and you should now see a table called CustomUsers and __EFMigrationsHistory. If this has not happened, put a breakpoint in your notification handler and made sure it is being triggered and not throwing an error!

Accessing Entity Framework Data Within Umbraco

You now have the mechanics of how to set up and create custom database tables nailed. The next step is to access and manipulate that data in your codebase. For testing purposes, we can add some data to our new table using a SQL statement.

With our data created, let us look at how we can access it. To read that data, I will create a simple REST API within my project to test my code works. This API will look like this:

You can now obviously expand this code to say write data back to the database:

Happy Coding 🤘