Locode - Database-First

Using AutoQuery's AutoGen enables the quickest way to modernize an existing database by generating Data Models & AutoQuery CRUD APIs from RDBMS table schemas. From Locode's point of view, the result is indistinguishable to Code-First where instead of developers defining Data Models & API Contracts in code they're dynamically generated by AutoGen at runtime, on Startup.

The difference is how APIs & Types are customized, with Code-First Types able to naturally access the Declarative Dev Model using C# Attributes, Database-First Models instead need to dynamically add attributes at runtime using AutoGen's Type & Service filters.

Regardless of how they're created & customized, each have access to Locode's instant modern UI around AutoQuery services.

To get started quickly watch this video for a step-by-step walkthrough into creating a Database-first Locode App:

Northwind example​

We have an example of this in the Northwind demo which provides a way to manage all the data in the Northwind database with some customizations to improve usability, all in ~120 lines of C#.

Create your project​

Starting with the basic web template for a ServiceStack application will provide the basic solution structure with a sample Hello World service. This can be done using the ServiceStack website under Get Started.

Alternatively, templates can be created using the dotnet CLI tool x. The dotnet x tool can be installed using the following command:

Once installed, a new web template can be created using:

Configuring database connection​

Once you have the new web project open, you will need to configure the following.

• Database type (PostgreSQL, SQL Server, MySQL, or SQLite)
• Database connection string
• AutoQuery Generated Services

We can use the dotnet x tool to mix in specific database support and AutoQuery quickly using the command run from the project directory.

This command will create two files, Configure.Db.cs and Configure.AutoQuery.cs and install required NuGet dependencies into the AppHost (MyLocodeApp in the link above) project.

Configure.Db.cs​

Below we have an example using sqlite of the configuration to add an IDbConnectionFactory dependency into IoC created by this command.

public class ConfigureDb : IHostingStartup
{
    public void Configure(IWebHostBuilder builder) => builder
        .ConfigureServices((context, services) => {
            services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(
                context.Configuration.GetConnectionString("DefaultConnection")
                ?? ":memory:",
                SqliteDialect.Provider));
        });
}

The example above is using an in-:memory: SQLite database, but we want to use a pre-existing database the connection string will need to be updated. To use the Northwind sample database, we can download and copy it into the AppHost project with the Configure.Db.cs file and replace the :memory: connection string with the file name northwind.sqlite. Another easy way to download northwind.sqlite is by using the x tool with the following command run from the AppHost directory.

Now our application can communicate with the Northwind sample database, we will need to configure AutoQuery to use AutoGen to generate our CRUD services from our database schema.

Configure.AutoQuery.cs​

With the database connection configured, next you will need to configure AutoQuery to scan your database schema and generate the required CRUD services. This feature is known as AutoGen and can be enabled by instantiating the GenerateCrudServices option on the AutoQueryFeature plugin with the AutoRegister flag set to true.

public class ConfigureAutoQuery : IHostingStartup
{
    public void Configure(IWebHostBuilder builder) => builder
        .ConfigureAppHost(appHost => {
            appHost.Plugins.Add(new AutoQueryFeature {
                MaxLimit = 1000,
                
                // Add this line, Configures Generated CRUD services with defaults
                GenerateCrudServices = new GenerateCrudServices()
                {
                    AutoRegister = true
                }
            });
        });
}

The AutoQueryFeature plugin will automatically use your registered IDbConnectionFactory to communicate with your database and generate services for the public schema. Running the application after these changes, we will have a Query, Create, Update and Delete services ready to use for each table.

Multiple Schemas​

By default, GenerateCrudServices with AutoRegister will create services for each table in the public schema. If you want to enable services for tables in other schemas, you can use the CreateServices option. For example, if you have a schema by the name of dbo and public, you would use the following options.

appHost.Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    //IncludeTotal = true,
    GenerateCrudServices = new GenerateCrudServices()
    {
        // Configure which schemas should be used, `public` is the default.
        CreateServices = new List<CreateCrudServices>
        {
            new CreateCrudServices(),
            new CreateCrudServices { Schema = "dbo" }
        }
    }
});

Multiple database connections​

If you are using Named connections with OrmLite, you can also specify these connections in the CreateServices list. Named connection registration can be done using the IDbConnectionFactory and RegisterConnection method.

// SqlServer with a named "Reporting" PostgreSQL connection as a part of the same `dbFactory`
var dbFactory = new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider);
container.Register<IDbConnectionFactory>(dbFactory);

dbFactory.RegisterConnection("Reporting", pgConnString, PostgreSqlDialect.Provider);

The string name provided to RegisterConnection must match that provided to the NamedConnection property on CreateCrudServices.

appHost.Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    //IncludeTotal = true,
    GenerateCrudServices = new GenerateCrudServices()
    {
        // Configure multiple databases, `public` is the default schema.
        CreateServices = new List<CreateCrudServices>
        {
            new CreateCrudServices(),
            new CreateCrudServices { NamedConnection = "Reporting" }
        }
    }
});

Multiple Schemas with Named Connections​

These options can be combined so that specific schemas on named connections can also be used.

appHost.Plugins.Add(new AutoQueryFeature {
    MaxLimit = 1000,
    //IncludeTotal = true,
    GenerateCrudServices = new GenerateCrudServices()
    {
        // Configure multiple databases, `public` is the default schema.
        CreateServices = new List<CreateCrudServices>
        {
            new CreateCrudServices { NamedConnection = "Reporting" },
            new CreateCrudServices { NamedConnection = "Finance", Schema = "trading" }
        }
    }
});

Customizing Locode App​

Locode includes a declarative dev model where attributes can be used to add additional metadata to your services and data model that can be used to enlist additional functionality and enhance the Locode App's UI.

Dynamically adding attributes​

The use of C# attributes to configure your AutoQuery service metadata is optimal for code-first DTOs & Data Models but when starting with a Database First dev model, the Types are generated and only exist at runtime which typically would prohibit them from being annotated with C# attributes, however AutoQuery AutoGen's ServiceFilter and TypeFilter lets you dynamically apply attributes to its generated types at startup.

Exporting to code-first Types​

In addition to enabling access to the same rich declarative functionality ServiceStack makes available via attributes, it also annotates the code-generated types which are retained when moving from database-first to code-first dev model by exporting to code-first types with:

Annotated Example of Northwind's Customizations​

To help illustrate some customizations available we've annotated the customizations made to the Northwind Sample SQLite Database that was used to create the custom northwind.locode.dev Locode App:

Modifying Dynamic Types at Runtime​

Annotating the Database First code-gen types can be done via the ServiceFilter and TypeFilter properties on GenerateCrudServices instructions when registering the AutoQueryFeature.

The ServiceFilter is called for every Service Operation whilst the TypesFilter is called for every code-gen Type including Request & Response DTOs.

Plugins.Add(new AutoQueryFeature {
    MaxLimit = 100,
    GenerateCrudServices = new GenerateCrudServices {
        AutoRegister = true,
        ServiceFilter = (op, req) => {
            // Annotate all Auto generated Request DTOs with [Tag("Northwind")] attribute
            op.Request.AddAttributeIfNotExists(new TagAttribute("Northwind"));
        },
        TypeFilter = (type, req) =>
        {
            // Configure to use Icon for this Type
            if (Icons.TryGetValue(type.Name, out var icon))
                type.AddAttribute(new IconAttribute { Svg = Svg.Create(icon) });

            // Is Employee Data Model or any AutoQuery Employee Request DTO
            if (type.Name == "Employee" || type.IsCrudCreateOrUpdate("Employee"))
            {
                // Remove unused `Photo` column
                type.Properties.RemoveAll(x => x.Name == "Photo");
                
                // Reorder columns from db-schema to the order we want them to appear in the UI
                type.ReorderProperty("PhotoPath", before: "Title")
                    .AddAttribute(new FormatAttribute(FormatMethods.IconRounded));
                type.ReorderProperty("ReportsTo", after: "Title");
                                
                if (type.IsCrud()) // Is AutoQuery Employee Request DTO
                {
                    // Configure to use File Input & upload to 'employees' Managed File Upload location 
                    type.Property("PhotoPath")
                        .AddAttribute(new InputAttribute { Type = Input.Types.File })
                        .AddAttribute(new UploadToAttribute("employees"));
                    
                    // Use TextArea control for larger text inputs
                    type.Property("Notes")
                        .AddAttribute(new InputAttribute { Type = Input.Types.Textarea });
                }
                else if (type.Name == "Employee") // Employee Data Model
                {
                    // Configure Employee FK Relation, utilizing UX-friendly LastName & Employee Lookup   
                    type.Property("ReportsTo").AddAttribute(
                        new RefAttribute { Model = "Employee", RefId = "Id", RefLabel = "LastName" });
                    
                    // Format to use `tel:` link allowing Phone call to be initiated from UI
                    type.Property("HomePhone").AddAttribute(new FormatAttribute(FormatMethods.LinkPhone));
                }
            }
            else if (type.Name == "Order")
            {
                // Customize all Date Columns to use UX-friendly Date Format 
                type.Properties.Where(x => x.Name.EndsWith("Date")).Each(p =>
                    p.AddAttribute(new IntlDateTime(DateStyle.Medium)));
                
                // Format number as USD Currency using JavaScript's Intl.NumberFormat
                type.Property("Freight").AddAttribute(new IntlNumber { Currency = NumberCurrency.USD });
                
                // Configure Shipper FK Relation, utilizing UX-friendly CompanyName & Shipper Lookup   
                type.Property("ShipVia").AddAttribute(
                    new RefAttribute { Model = "Shipper", RefId = "Id", RefLabel = "CompanyName" });
            }
            else if (type.Name == "OrderDetail")
            {
                // Format number as USD Currency using JavaScript's Intl.NumberFormat
                type.Property("UnitPrice").AddAttribute(new IntlNumber { Currency = NumberCurrency.USD });
                // Format as % using JavaScript's Intl.NumberFormat
                type.Property("Discount").AddAttribute(new IntlNumber(NumberStyle.Percent));
            }
            else if (type.Name == "EmployeeTerritory")
            {
                // Configure Territory FK Relation & Lookup, utilizing UX-friendly TerritoryDescription
                type.Property("TerritoryId").AddAttribute(new RefAttribute {
                    Model = "Territory", RefId = "Id", RefLabel = "TerritoryDescription" });
            }
            else if (type.Name is "Customer" or "Supplier" or "Shipper")
            {
                // Format to use `tel:` link allowing Phone call to be initiated from UI
                type.Property("Phone").AddAttribute(new FormatAttribute(FormatMethods.LinkPhone));
                type.Property("Fax")?.AddAttribute(new FormatAttribute(FormatMethods.LinkPhone));
            }
        },
    },
});

Code-gen Customization Helpers​

A number of UX-Friendly extension methods are available to reduce effort for applying common customizations to MetadataType and MetadataPropertyType blueprints used in code generating .NET Types:

Format search results​

The Declarative Attributes docs contains a more complete reference of built-in customizations, but we'll cover a few the Northwind Locode App uses to illustrate some potential enhancements available.

An effortless way to add a lot of value to DB Apps is to mark up the raw data stored in RDBMS tables into a UX-friendly view, an example of this in Northwind is using FormatMethods.LinkPhone on the Phone and Fax properties for the Customer, Supplier, and Shipper tables:

if (type.Name is "Customer" or "Supplier" or "Shipper")
{
    type.Property("Phone").AddAttribute(new FormatAttribute(FormatMethods.LinkPhone));
    type.Property("Fax")?.AddAttribute(new FormatAttribute(FormatMethods.LinkPhone));
}

To format the phone numbers in tel: HTML links enabling 1-click to call, directly from the search results page:

A complete list of built-in functions can be found in the Format Functions docs, another example used in Northwind is FormatMethods.IconRounded on PhotoPath:

if (type.Name == "Employee" || type.IsCrudCreateOrUpdate("Employee"))
{
    type.ReorderProperty("PhotoPath", before: "Title")
        .AddAttribute(new FormatAttribute(FormatMethods.IconRounded));
}

To apply the iconRounded JavaScript function to render a preview of the Employee profile directly in the search results:

ReorderProperty is used to change ordering of Table columns which changes the order they're displayed in APIs and UIs.

update Employee set PhotoPath = "/profiles/employees/" || Employee.Id || ".jpg"

ECMAScript Internationalization APIs​

ECMAScript's rich Intl.NumberFormat, Intl.DateTimeFormat and Intl.RelativeTimeFormat APIs are also available from the typed [Intl*] Attributes which OrderDetail makes use of to format UnitPrice in USD Currency and Discount in a % percentage format:

if (type.Name == "OrderDetail")
{
    type.Property("UnitPrice").AddAttribute(new IntlNumber { Currency = NumberCurrency.USD });
    type.Property("Discount").AddAttribute(new IntlNumber(NumberStyle.Percent));
}

This can give a much more contextual view of the data in the returning from our services.

For more customization options, see the Declarative Attributes docs.