Umbraco CMS
CloudHeartcoreDXPMarketplace
13.latest (LTS)
13.latest (LTS)
  • Umbraco CMS Documentation
  • Legacy Documentation
    • Umbraco 11 Documentation
    • Umbraco 8 Documentation
    • Umbraco 7 Documentation
  • Release Notes
  • Contribute
  • Sustainability Best Practices
  • Fundamentals
    • Get to know Umbraco
    • Setup
      • Requirements
      • Installation
        • Install using .NET CLI
        • Install using Visual Studio
        • Local IIS With Umbraco
        • Install using Visual Studio Code
        • Installing Nightly Builds
        • Running Umbraco on Linux/macOS
        • Unattended Installs
      • Upgrade your project
        • Version Specific Upgrades
          • Upgrade from Umbraco 8 to the latest version
          • Migrate content to Umbraco 8
          • Minor upgrades for Umbraco 8
          • Upgrade to Umbraco 7
          • Minor upgrades for Umbraco 7
      • Server setup
        • Running Umbraco On Azure Web Apps
        • Hosting Umbraco in IIS
        • File And Folder Permissions
        • Runtime Modes
        • Umbraco in Load Balanced Environments
          • Load Balancing Azure Web Apps
          • Standalone File System
          • Advanced Techniques With Flexible Load Balancing
          • Logging With Load Balancing
    • Backoffice
      • Sections
      • Property Editors
        • Built-in Property Editors
          • Checkbox List
          • Color Picker
          • Content Picker
          • DateTime
          • Date
          • Decimal
          • Email Address
          • Eye Dropper Color Picker
          • File Upload
          • Image Cropper
          • Label
          • List View
          • Markdown Editor
          • Media Picker
          • Media Picker (Legacy)
          • Member Group Picker
          • Member Picker
          • Multi Url Picker
          • Multinode Treepicker
          • Repeatable Textstrings
          • Numeric
          • Radiobutton List
          • Slider
          • Tags
          • Textarea
          • Textbox
          • Toggle
          • User Picker
          • Block Editors
            • Block Grid
            • Block List
            • Build a Custom View for a Block
            • Configuring Block Editor Label Properties
          • Dropdown
          • Grid Layout (Legacy)
            • What Are Grid Layouts?
            • Configuring The Grid Layout
            • Settings And Styling
            • Grid Editors
            • Build Your Own Editor
            • Rendering Grid In a Template
            • Grid Layout Best Practices
            • Add Values Programmatically
          • Rich Text Editor
            • Rich Text Editor Configuration
            • Rich Text Editor Styles
            • Rich Text Editor Plugins
            • Blocks in Rich Text Editor
      • Login
      • Content Templates
      • Infinite Editing
      • Log Viewer
      • Language Variants
      • Settings Dashboards
    • Data
      • Defining Content
        • Default Document Types
        • Document Type Localization
      • Creating Media
        • Default Data/Media Types
      • Members
      • Data Types
        • Default Data Types
      • Scheduled Publishing
      • Using Tabs
      • Users
      • Relations
      • Dictionary Items
      • Content Version Cleanup
    • Design
      • Templates
        • Basic Razor Syntax
        • Named Sections
        • Razor Cheatsheet
      • Rendering Content
      • Rendering Media
      • Partial Views
      • Partial View Macro Files
      • Stylesheets And JavaScript
    • Code
      • Service APIs
      • Subscribing To Notifications
      • Creating Forms
      • Debugging
        • Logging
      • Source Control
  • Implementation
    • Learn how Umbraco works
    • Routing
      • Controller & Action Selection
      • Execute Request
      • Request Pipeline
    • Custom Routing
      • Adding a hub with SignalR and Umbraco
    • Controllers
    • Data Persistence (CRUD)
    • Composing
    • Integration Testing
    • Nullable Reference Types
    • Services and Helpers
      • Circular Dependencies
    • Unit Testing
  • Extending
    • Customize the editing experience
    • Dashboards
    • Sections & Trees
      • Sections
      • Trees
        • Tree Actions
      • Searchable Trees (ISearchableTree)
    • Property Editors
      • Property Value Converters
      • Property Actions
      • Tracking References
      • Declaring your property editor
      • Content Picker Value Converter Example
    • Package Manifest
    • Macro Parameter Editors
    • Health Check
      • Health Check Guides
        • Click-Jacking Protection
        • Content/MIME Sniffing Protection
        • Cross-site scripting Protection (X-XSS-Protection header)
        • Debug Compilation Mode
        • Excessive Headers
        • Fixed Application Url
        • Folder & File Permissions
        • HTTPS Configuration
        • Macro Errors
        • Notification Email Settings
        • SMTP
        • Strict-Transport-Security Header
    • Language Files & Localization
    • Backoffice Search
    • Backoffice Tours
    • Backoffice UI API Documentation
    • Content Apps
    • Creating a Custom Database Table
    • Embedded Media Providers
    • Custom File Systems (IFileSystem)
      • Using Azure Blob Storage for Media and ImageSharp Cache
    • Configuring Azure Key Vault
    • Packages
      • Creating a Package
      • Language file for packages
      • Listing a Package on the Umbraco Marketplace
      • Good practice and defaults
      • Packages on Umbraco Cloud
      • Installing and Uninstalling Packages
      • Maintaining packages
      • Create accessible Umbraco packages
      • Example Package Repository
    • UI Library
  • Reference
    • Dive into the code
    • Configuration
      • Basic Authentication Settings
      • Connection strings settings
      • Content Dashboard Settings
      • Content Settings
      • Data Types Settings
      • Debug settings
      • Examine settings
      • Exception filter settings
      • FileSystemProviders Configuration
      • Global Settings
      • Health checks
      • Hosting settings
      • Imaging settings
      • Indexing settings
      • Install Default Data Settings
      • Keep alive settings
      • Logging settings
      • Maximum Upload Size Settings
      • Models builder settings
      • NuCache Settings
      • Package Migration
      • Plugins settings
      • Request handler settings
      • Rich text editor settings
      • Runtime minification settings
      • Runtime settings
      • Security Settings
      • Serilog settings
      • Tours settings
      • Type finder settings
      • Unattended
      • Web routing
    • Templating
      • Macros
        • Managing macros
        • Partial View Macros
      • Models Builder
        • Introduction
        • Configuration
        • Builder Modes
        • Understand and Extend
        • Using Interfaces
        • Tips and Tricks
      • Working with MVC
        • Working with MVC Views in Umbraco
        • View/Razor Examples
        • Using MVC Partial Views in Umbraco
        • Using View Components in Umbraco
        • Querying & Traversal
        • Creating Forms
    • Querying & Models
      • IMemberManager
      • IPublishedContentQuery
      • ITagQuery
      • UDI Identifiers
      • UmbracoContext helper
      • UmbracoHelper
      • IPublishedContent
        • IPublishedContent Collections
        • IPublishedContent IsHelpers
        • IPublishedContent Property Access & Extension Methods
    • Routing & Controllers
      • Routing requirements for backoffice authentication
      • Custom MVC controllers (Umbraco Route Hijacking)
      • Custom MVC Routes
      • Custom Middleware
      • URL Rewrites in Umbraco
      • Special Property Type aliases for routing
      • URL Redirect Management
      • Routing in Umbraco
        • FindPublishedContentAndTemplate()
        • IContentFinder
        • Inbound request pipeline
        • Outbound request pipeline
        • Published Content Request Preparation
      • Surface controllers
        • Surface controller actions
      • Umbraco API Controllers
        • Umbraco Api - Authorization
        • Umbraco Api - Routing & Urls
    • Content Delivery API
      • Custom property editors support
      • Extension API for querying
      • Media Delivery API
      • Protected content in the Delivery API
      • Output caching
      • Property expansion and limiting
      • Additional preview environments support
    • Webhooks
      • Expanding Webhook Events
    • API versioning and OpenAPI
    • Searching
      • Examine
        • Examine Management
        • Examine Manager
        • Custom indexing
        • PDF indexes and multisearchers
        • Quick-start
    • Using Notifications
      • Notification Handler
      • CacheRefresher Notifications Example
      • ContentService Notifications Example
      • Creating And Publishing Notifications
      • Determining if an entity is new
      • MediaService Notifications Example
      • MemberService Notifications Example
      • Sending Allowed Children Notification
      • Umbraco Application Lifetime Notifications
      • EditorModel Notifications
        • Customizing the "Links" box
      • Hot vs. cold restarts
    • Inversion of Control / Dependency injection
    • Management
      • Models Reference
        • Content
        • ContentType
        • DataType
        • DictionaryItem
        • Language
        • Media
        • MediaType
        • Relation
        • RelationType
        • ServerRegistration
        • Template
      • Services Reference
        • AuditService
        • ConsentService
        • DataTypeService
        • DomainService
        • EntityService
        • ExternalLoginService
        • FileService
        • MacroService
        • MediaService
        • MemberGroupService
        • MemberService
        • MemberTypeService
        • NotificationService
        • PackagingService
        • PublicAccessService
        • RedirectUrlService
        • RelationService
        • ServerRegistrationService
        • TagService
        • TextService
        • ContentService
          • Create content programmatically
          • Publish content programmatically
        • ContentTypeService
          • Retrieving content types
          • Retrieving content types
        • LocalizationService
          • Retrieving languages
        • UserService
          • Creating a user
    • Plugins
      • Creating Resolvers
      • Finding types
    • Cache & Distributed Cache
      • Accessing the cache
      • ICacheRefresher
      • IServerMessenger
      • Getting/Adding/Updating/Inserting Into Cache
      • Examples
        • Working with caching
    • Response Caching
    • Security
      • API rate limiting
      • BackOfficeUserManager and Events
      • Cookies
      • Replacing the basic username/password check
      • External login providers
      • Locking of Users and password reset
      • Reset admin password
      • Umbraco Security Hardening
      • Umbraco Security Settings
      • Sensitive data
      • Sanitizing the Rich Text Editor
      • Setup Umbraco for a FIPS Compliant Server
      • HTTPS
      • Two-factor Authentication
      • Server-side file validation
    • Scheduling
    • Common Pitfalls & Anti-Patterns
    • API Documentation
    • Debugging with SourceLink
    • Language Variation
    • UmbracoMapper
    • Distributed Locks
    • AngularJS
      • Directives
        • umbLayoutSelector
        • umbLoadIndicator
        • umbProperty
      • Services
        • Editor Service
        • Events Service
          • changeTitle
  • Tutorials
    • Overview
    • Creating a Basic Website
      • Getting Started
      • Document Types
      • Creating Your First Template
      • CSS and Images
      • Displaying the Document Type Properties
      • Creating a Master Template
      • Creating Pages and Using the Master Template
      • Setting the Navigation Menu
      • Articles and Article Items
      • Adding Language Variants
      • Conclusions
    • Creating a Custom Dashboard
      • Extending the Dashboard using the Umbraco UI library
    • Creating a Property Editor
      • Adding configuration to a property editor
      • Integrating services with a property editor
      • Adding server-side data to a property editor
    • Creating a Multilingual Site
    • Add Google Authentication (Users)
    • Add Microsoft Entra ID authentication (Members)
    • Creating a Backoffice Tour
    • Creating Custom Database Tables with Entity Framework
    • The Starter Kit
      • Lessons
        • Customize the Starter Kit
        • Add a Blog Post Publication Date
          • Add a Blog Post Publication Date
          • Add a Blog Post Publication Date
        • Add Open Graph
          • Add Open Graph - Step 1
          • Add Open Graph - Step 2
          • Add Open Graph - Step 3
          • Add Open Graph - Step 4
          • Add Open Graph - Summary
        • Ask For Help and Join the Community
    • Editor's Manual
      • Getting Started
        • Logging In and Out
        • Umbraco Interface
        • Creating, Saving and Publishing Content Options
        • Finding Content
        • Editing Existing Content
        • Sorting Pages
        • Moving a Page
        • Copying a Page
        • Deleting and Restoring Pages
      • Working with Rich Text Editor
      • Version Management
        • Comparing Versions
        • Rollback to a Previous Version
      • Media Management
        • Working with Folders
        • Working with Media Types
        • Cropping Images
      • Tips & Tricks
        • Refreshing the Tree View
        • Audit Trail
        • Notifications
        • Preview Pane Responsive View
        • Session Timeout
    • Multisite Setup
    • Member Registration and Login
    • Custom Views for Block List
    • Connecting Umbraco Forms and Zapier
    • Creating an XML Sitemap
    • Implementing Custom Error Pages
Powered by GitBook
On this page
  • Using a Composer and Component
  • Using a Notification Handler
  • Which to use?
  • Schema Class and Migrations
  • Data stored in Custom Database Tables
  • Working with data in Custom Database Tables
Edit on GitHub
Export as PDF
  1. Extending

Creating a Custom Database Table

A guide to creating a custom Database table in Umbraco

PreviousContent AppsNextEmbedded Media Providers

Last updated 11 months ago

It is possible to add custom database tables to your site to store additional data that should not be stored as normal content nodes.

The end result looks like this:

Using a Composer and Component

The following code sample shows how this is done using a composer and component.

When migrating from version 8 there are a few changes to be aware of. The first change is that namespace updates are dependencies that need to be passed to the Upgrader.Execute() method. Another is a change to the access modifier of the Migrate() method.

using Microsoft.Extensions.Logging;
using NPoco;
using Umbraco.Cms.Core;
using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.Migrations;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;
using Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations;

namespace MyNamespace;

public class BlogCommentsComposer : ComponentComposer<BlogCommentsComponent>
{
}

public class BlogCommentsComponent : IComponent
{
    private readonly ICoreScopeProvider _coreScopeProvider;
    private readonly IMigrationPlanExecutor _migrationPlanExecutor;
    private readonly IKeyValueService _keyValueService;
    private readonly IRuntimeState _runtimeState;

    public BlogCommentsComponent(
        ICoreScopeProvider coreScopeProvider,
        IMigrationPlanExecutor migrationPlanExecutor,
        IKeyValueService keyValueService,
        IRuntimeState runtimeState)
    {
        _coreScopeProvider = coreScopeProvider;
        _migrationPlanExecutor = migrationPlanExecutor;
        _keyValueService = keyValueService;
        _runtimeState = runtimeState;
    }

    public void Initialize()
    {
        if (_runtimeState.Level < RuntimeLevel.Run)
        {
            return;
        }

        // Create a migration plan for a specific project/feature
        // We can then track that latest migration state/step for this project/feature
        var migrationPlan = new MigrationPlan("BlogComments");

        // This is the steps we need to take
        // Each step in the migration adds a unique value
        migrationPlan.From(string.Empty)
            .To<AddCommentsTable>("blogcomments-db");

        // Go and upgrade our site (Will check if it needs to do the work or not)
        // Based on the current/latest step
        var upgrader = new Upgrader(migrationPlan);
        upgrader.Execute(_migrationPlanExecutor, _coreScopeProvider, _keyValueService);
    }

    public void Terminate()
    {
    }
}

public class AddCommentsTable : MigrationBase
{
    public AddCommentsTable(IMigrationContext context) : base(context)
    {
    }
    protected override void Migrate()
    {
        Logger.LogDebug("Running migration {MigrationStep}", "AddCommentsTable");

        // Lots of methods available in the MigrationBase class - discover with this.
        if (TableExists("BlogComments") == false)
        {
            Create.Table<BlogCommentSchema>().Do();
        }
        else
        {
            Logger.LogDebug("The database table {DbTable} already exists, skipping", "BlogComments");
        }
    }

    [TableName("BlogComments")]
    [PrimaryKey("Id", AutoIncrement = true)]
    [ExplicitColumns]
    public class BlogCommentSchema
    {
        [PrimaryKeyColumn(AutoIncrement = true, IdentitySeed = 1)]
        [Column("Id")]
        public int Id { get; set; }

        [Column("BlogPostUmbracoId")]
        public int BlogPostUmbracoId { get; set; }

        [Column("Name")]
        public required string Name { get; set; }

        [Column("Email")]
        public required string Email { get; set; }

        [Column("Website")]
        public required string Website { get; set; }

        [Column("Message")]
        [SpecialDbType(SpecialDbTypes.NVARCHARMAX)]
        public string Message { get; set; }
    }
}

Using a Notification Handler

If building a new solution, you can adopt a new pattern. With this pattern you create and run a similar migration but trigger it in response to a notification handler.

The code for this approach is as follows:

using Microsoft.Extensions.Logging;
using NPoco;
using Umbraco.Cms.Core;
using Umbraco.Cms.Core.Events;
using Umbraco.Cms.Core.Migrations;
using Umbraco.Cms.Core.Notifications;
using Umbraco.Cms.Core.Scoping;
using Umbraco.Cms.Core.Services;
using Umbraco.Cms.Infrastructure.Migrations;
using Umbraco.Cms.Infrastructure.Migrations.Upgrade;
using Umbraco.Cms.Infrastructure.Persistence.DatabaseAnnotations;

namespace MyNamespace;

public class RunBlogCommentsMigration : INotificationHandler<UmbracoApplicationStartingNotification>
{
    private readonly IMigrationPlanExecutor _migrationPlanExecutor;
    private readonly ICoreScopeProvider _coreScopeProvider;
    private readonly IKeyValueService _keyValueService;
    private readonly IRuntimeState _runtimeState;

    public RunBlogCommentsMigration(
        ICoreScopeProvider coreScopeProvider,
        IMigrationPlanExecutor migrationPlanExecutor,
        IKeyValueService keyValueService,
        IRuntimeState runtimeState)
    {
        _migrationPlanExecutor = migrationPlanExecutor;
        _coreScopeProvider = coreScopeProvider;
        _keyValueService = keyValueService;
        _runtimeState = runtimeState;
    }

    public void Handle(UmbracoApplicationStartingNotification notification)
    {
        if (_runtimeState.Level < RuntimeLevel.Run)
        {
            return;
        }

        // Create a migration plan for a specific project/feature
        // We can then track that latest migration state/step for this project/feature
        var migrationPlan = new MigrationPlan("BlogComments");

        // This is the steps we need to take
        // Each step in the migration adds a unique value
        migrationPlan.From(string.Empty)
            .To<AddCommentsTable>("blogcomments-db");

        // Go and upgrade our site (Will check if it needs to do the work or not)
        // Based on the current/latest step
        var upgrader = new Upgrader(migrationPlan);
        upgrader.Execute(
            _migrationPlanExecutor,
            _coreScopeProvider,
            _keyValueService);
    }
}

// Migration and schema defined as in the previous code sample.

The notification handler can be registered in a composer:

using Umbraco.Cms.Core.Composing;
using Umbraco.Cms.Core.DependencyInjection;
using Umbraco.Cms.Core.Notifications;

namespace TableMigrationTest;

public class BlogCommentsComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddNotificationHandler<UmbracoApplicationStartingNotification, RunBlogCommentsMigration>();
    }
}

Which to use?

In short, it's up to you. If you are migrating from version 8 and want the quickest route to getting running with the latest version, then using a component makes sense.

You will be using the notification pattern elsewhere. This could be when responding to Umbraco events that run many times in the lifetime of the application, like when content is saved. And so you may also prefer to align with that pattern for start-up events.

It is also worth noting that components offer both Initialize and Terminate methods. With these you will need to handle two notifications to do the same with the notification handler approach (UmbracoApplicationStartingNotification and UmbracoApplicationStoppingNotification). A single handler class can be used for both notifications though.

Schema Class and Migrations

Important! The BlogCommentSchema class nested inside the migration is purely used as a database schema representation class. It should not be used as a Data Transfer Object (DTO) to access the table data. Equally, you shouldn't use your DTO classes to define the schema used by your migration. Instead, you should create a duplicate snapshot for the purpose of creating or working with your database tables in the current migration. The name of the class is not important as you will be overriding it using the TableName attribute. You should choose a name that makes it clear that this class is purely for defining the schema in this migration.

Whilst this adds a level of duplication, it is important that migrations and the code/classes within a migration remain immutable. If the DTO was to be used for both, it could cause unexpected behaviour. Should you later modify your DTO used in your application but you have previous migrations expecting the DTO to be in its unmodified state.

Once a snapshot has been created, and once your code has been deployed, the snapshot should never be changed directly. Instead, you should use further migrations to alter the database table into the state you require. This ensures that migrations can be run in sequence and that each migration can expect the database to be in a known state before executing.

When adding further migrations and if you need to reuse the schema class, it is a good idea to duplicate this in those particular migrations. You want the migrations to be immutable. Having separate classes in separate namespaces, reduces the risk of modifying a schema class from your initial migration.

Data stored in Custom Database Tables

When storing data in custom database tables, this is by default not manageable by Umbraco at all. This can be great for many purposes such as storing massive amounts of data that you do not need to edit from the backoffice. Decoupling part of your data from being managed by Umbraco as content can be a way of achieving better performance for your site. It will no longer take up space in indexes and caches, and the Umbraco database.

This also means that if you do need to edit or display this data, you need to implement the underlying functionality to support this. The same if the case if you need this data to be transferred or kept synchronized between multiple sites or environments. Data stored in custom tables are not supported by default by add-ons such as Umbraco Deploy and will not be deployable by default.

Figuring out how to manage data across multiple environments can be different between individual sites and there is not one solution that fits all. Some sites may have automated database synchronization set up to ensure specific tables in multiple databases are always kept in sync. Other sites may be better off with scripts moving data around manually on demand.

Working with data in Custom Database Tables

To create, read, update or delete data from your custom database tables, you can use the IScopeProvider to get access to the database operations.

The following example creates an UmbracoApiController to be able to fetch and insert blog comments.

This example does not use the aforementioned BlogCommentSchema class but rather a separate (yet duplicate) class that is not part of the example. Also, be aware that things like error handling and data validation have been omitted for brevity.

using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using Umbraco.Cms.Infrastructure.Scoping;
using Umbraco.Cms.Web.Common.Controllers;

namespace MyNamespace;

public class BlogCommentsApiController : UmbracoApiController
{
    private readonly IScopeProvider _scopeProvider;
    public BlogCommentsApiController(IScopeProvider scopeProvider)
    {
        _scopeProvider = scopeProvider;
    }
    [HttpGet]
    public IEnumerable<BlogComment> GetComments(int umbracoNodeId)
    {
        using var scope = _scopeProvider.CreateScope();
        var queryResults = scope.Database.Fetch<BlogComment>("SELECT * FROM BlogComments WHERE BlogPostUmbracoId = @0", umbracoNodeId);
        scope.Complete();
        return queryResults;
    }
    [HttpPost]
    public void InsertComment(BlogComment comment)
    {
        using var scope = _scopeProvider.CreateScope();
        scope.Database.Insert<BlogComment>(comment);
        scope.Complete();
    }
}
Database result of a migration