Database Operations

Learn how to work with databases in your nopCommerce plugins, including creating custom tables, migrations, and data access.

Overview

nopCommerce uses Entity Framework Core for data access. Plugins can:

• Create custom database tables
• Use the repository pattern for data access
• Apply database migrations automatically
• Work with existing nopCommerce entities

Creating a Custom Entity

Step 1: Define the Entity

// Domain/MyRecord.cs
using Nop.Core;

namespace Nop.Plugin.Widgets.MyPlugin.Domain;

public class MyRecord : BaseEntity
{
    public string Name { get; set; }
    public string Description { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedOnUtc { get; set; }
    public DateTime? UpdatedOnUtc { get; set; }
}

BaseEntity

All entities must inherit from BaseEntity which provides the Id property.

Step 2: Create Entity Mapping

// Data/Mapping/MyRecordBuilder.cs
using FluentMigrator.Builders.Create.Table;
using Nop.Data.Mapping.Builders;
using Nop.Plugin.Widgets.MyPlugin.Domain;

namespace Nop.Plugin.Widgets.MyPlugin.Data.Mapping;

public class MyRecordBuilder : NopEntityBuilder<MyRecord>
{
    public override void MapEntity(CreateTableExpressionBuilder table)
    {
        table
            .WithColumn(nameof(MyRecord.Name)).AsString(400).NotNullable()
            .WithColumn(nameof(MyRecord.Description)).AsString(int.MaxValue).Nullable()
            .WithColumn(nameof(MyRecord.IsActive)).AsBoolean().NotNullable()
            .WithColumn(nameof(MyRecord.CreatedOnUtc)).AsDateTime().NotNullable()
            .WithColumn(nameof(MyRecord.UpdatedOnUtc)).AsDateTime().Nullable();
    }
}

Step 3: Create Schema Migration

nopCommerce uses FluentMigrator for database migrations. There are three types of migrations you can use:

Migration Types

Type	Use Case	Rollback
AutoReversingMigration	Simple schema changes (create table, add column, add index)	Automatic
ForwardOnlyMigration	Data transformations, complex operations that can't be reversed	None
Migration	Full control with explicit Up() and Down() methods	Manual

AutoReversingMigration (Recommended for Schema)

Use for simple, reversible schema changes. FluentMigrator automatically generates the Down() method.

// Data/SchemaMigration.cs
using FluentMigrator;
using Nop.Data.Extensions;
using Nop.Data.Migrations;
using Nop.Plugin.Widgets.MyPlugin.Domain;

namespace Nop.Plugin.Widgets.MyPlugin.Data;

[NopMigration("2025/01/01 12:00:00", "Widgets.MyPlugin base schema", MigrationProcessType.Installation)]
public class SchemaMigration : AutoReversingMigration
{
    public override void Up()
    {
        Create.TableFor<MyRecord>();
    }
}

When to Use AutoReversingMigration

• Creating new tables
• Adding columns
• Creating indexes
• Adding foreign keys

ForwardOnlyMigration (Data Transformations)

Use when the operation cannot be reversed (e.g., data migrations, splitting columns).

[NopMigration("2025/02/01 12:00:00", "Migrate legacy data", MigrationProcessType.Update)]
public class DataMigration : ForwardOnlyMigration
{
    public override void Up()
    {
        // Example: Copy data from old column to new column
        Execute.Sql(@"
            UPDATE MyRecord 
            SET NewColumn = OldColumn 
            WHERE NewColumn IS NULL
        ");
    }
}

ForwardOnlyMigration

These migrations cannot be rolled back. Use carefully and ensure you have backups before running in production.

Migration (Full Control)

Use when you need explicit control over both Up() and Down() operations.

[NopMigration("2025/03/01 12:00:00", "Add computed column", MigrationProcessType.Update)]
public class ComputedColumnMigration : Migration
{
    public override void Up()
    {
        // Add a column with complex logic
        Alter.Table("MyRecord")
            .AddColumn("FullName").AsString(500).Nullable();
            
        // Populate the new column
        Execute.Sql(@"
            UPDATE MyRecord 
            SET FullName = FirstName + ' ' + LastName
        ");
    }

    public override void Down()
    {
        // Explicitly define how to reverse
        Delete.Column("FullName").FromTable("MyRecord");
    }
}

Repository Pattern

Basic CRUD Operations

using Nop.Data;

public class MyRecordService : IMyRecordService
{
    #region Fields

    private readonly IRepository<MyRecord> _repository;

    #endregion

    #region Ctor

    public MyRecordService(IRepository<MyRecord> repository)
    {
        _repository = repository;
    }

    #endregion

    #region Methods

    // Create
    public async Task InsertAsync(MyRecord record)
    {
        await _repository.InsertAsync(record);
    }

    // Read - single
    public async Task<MyRecord> GetByIdAsync(int id)
    {
        return await _repository.GetByIdAsync(id);
    }

    // Read - all
    public async Task<IList<MyRecord>> GetAllAsync()
    {
        return await _repository.GetAllAsync(query => query);
    }

    // Read - with filter
    public async Task<IList<MyRecord>> GetActiveRecordsAsync()
    {
        return await _repository.GetAllAsync(query => 
            query.Where(r => r.IsActive).OrderByDescending(r => r.CreatedOnUtc));
    }

    // Update
    public async Task UpdateAsync(MyRecord record)
    {
        await _repository.UpdateAsync(record);
    }

    // Delete
    public async Task DeleteAsync(MyRecord record)
    {
        await _repository.DeleteAsync(record);
    }

    #endregion
}

Paged Queries

public async Task<IPagedList<MyRecord>> GetPagedRecordsAsync(
    string searchName = null,
    bool? isActive = null,
    int pageIndex = 0,
    int pageSize = 10)
{
    return await _repository.GetAllPagedAsync(query =>
    {
        if (!string.IsNullOrEmpty(searchName))
            query = query.Where(r => r.Name.Contains(searchName));
        
        if (isActive.HasValue)
            query = query.Where(r => r.IsActive == isActive.Value);
        
        return query.OrderByDescending(r => r.CreatedOnUtc);
    }, pageIndex, pageSize);
}

Working with Existing Entities

You can query nopCommerce core entities:

public class OrderReportService
{
    private readonly IRepository<Order> _orderRepository;
    private readonly IRepository<Customer> _customerRepository;

    public OrderReportService(
        IRepository<Order> orderRepository,
        IRepository<Customer> customerRepository)
    {
        _orderRepository = orderRepository;
        _customerRepository = customerRepository;
    }

    public async Task<IList<Order>> GetRecentOrdersAsync(int days = 7)
    {
        var dateFrom = DateTime.UtcNow.AddDays(-days);
        
        return await _orderRepository.GetAllAsync(query =>
            query.Where(o => o.CreatedOnUtc >= dateFrom)
                 .OrderByDescending(o => o.CreatedOnUtc));
    }
}

Caching Data

Combine repository with caching for better performance:

private readonly IStaticCacheManager _cacheManager;

public async Task<IList<MyRecord>> GetActiveRecordsAsync()
{
    var cacheKey = _cacheManager.PrepareKeyForDefaultCache(
        new CacheKey("MyPlugin.Record.Active"));

    return await _cacheManager.GetAsync(cacheKey, async () =>
    {
        return await _repository.GetAllAsync(query => 
            query.Where(r => r.IsActive));
    });
}

// Invalidate cache on changes
public async Task UpdateAsync(MyRecord record)
{
    await _repository.UpdateAsync(record);
    await _cacheManager.RemoveByPrefixAsync("MyPlugin.Record");
}

Database Migrations

Adding a Column

[NopMigration("2025/02/01 12:00:00", "Add Priority column", MigrationProcessType.Update)]
public class AddPriorityColumn : AutoReversingMigration
{
    public override void Up()
    {
        Alter.Table(nameof(MyRecord))
            .AddColumn("Priority").AsInt32().WithDefaultValue(0);
    }
}

Adding an Index

[NopMigration("2025/02/15 12:00:00", "Add index on Name", MigrationProcessType.Update)]
public class AddNameIndex : AutoReversingMigration
{
    public override void Up()
    {
        Create.Index("IX_MyRecord_Name")
            .OnTable(nameof(MyRecord))
            .OnColumn("Name");
    }
}

Renaming a Column (ForwardOnly)

[NopMigration("2025/03/01 12:00:00", "Rename Title to Name", MigrationProcessType.Update)]
public class RenameColumn : ForwardOnlyMigration
{
    public override void Up()
    {
        Rename.Column("Title").OnTable(nameof(MyRecord)).To("Name");
    }
}

Cleanup on Uninstall

Remove tables when plugin is uninstalled:

public override async Task UninstallAsync()
{
    // Drop the custom table
    var tableNameManager = EngineContext.Current.Resolve<INopDataProvider>();
    await tableNameManager.DropTableIfExistsAsync<MyRecord>();
    
    await base.UninstallAsync();
}

Best Practices

1. Use async methods - All repository methods have async versions
2. Use caching - Cache frequently accessed data
3. Use paging - Never load unbounded result sets
4. Clean up on uninstall - Remove custom tables
5. Version migrations - Use descriptive timestamps

Next Steps

• Admin UI - Build admin interfaces for your data
• Events - React to data changes
• Services - Business logic patterns