---
title: Locode - Database-First
---
Using [AutoQuery's AutoGen](/autoquery/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](/locode/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](/locode/declarative)
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](https://northwind.locode.dev) 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](https://servicestack.net) under
[Get Started](https://servicestack.net/start).
Alternatively, templates can be created using the dotnet CLI tool `x`. The dotnet `x` tool can be installed
using the following command:
:::sh
dotnet tool install --global x
:::
Once installed, a new `web` template can be created using:
:::sh
npx create-net web MyProjectName
:::
## 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.
:::sh
npx add-in sqlite autoquery
:::
::: tip
Replace `sqlite` with `postgres`, `sqlserver`, or `mysql` or other RDBMS providers
:::
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.
```csharp
public class ConfigureDb : IHostingStartup
{
public void Configure(IWebHostBuilder builder) => builder
.ConfigureServices((context, services) => {
services.AddSingleton(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.
:::sh
npx add-in northwind.sqlite
:::
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`.
```csharp
public class ConfigureAutoQuery : IHostingStartup
{
public void Configure(IWebHostBuilder builder) => builder
.ConfigureServices((context, services) => {
var ormLite = services.AddOrmLite(options => options.UseSqlite(connString));
// Configure ASP.NET Core IOC Dependencies
services.AddPlugin(new AutoQueryFeature {
MaxLimit = 1000,
// Add this line, Configures Generated CRUD services with defaults
GenerateCrudServices = new GenerateCrudServices {
AutoRegister = true,
DbFactory = ormLite.DbFactory,
}
});
});
}
```
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.
```csharp
services.AddPlugin(new AutoQueryFeature {
MaxLimit = 1000,
//IncludeTotal = true,
GenerateCrudServices = new GenerateCrudServices()
{
// Configure which schemas should be used, `public` is the default.
CreateServices = new List
{
new CreateCrudServices(),
new CreateCrudServices { Schema = "dbo" }
}
}
});
```
### Multiple database connections
If you are using [Named connections](/autoquery/rdbms#named-connection) with [OrmLite](/ormlite/), you can also specify these connections in the `CreateServices` list.
Named connection registration can be done using the `IDbConnectionFactory` and `RegisterConnection` method.
```csharp
// SqlServer with a named "Reporting" PostgreSQL connection as a part of the same `dbFactory`
var dbFactory = new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider);
container.Register(dbFactory);
dbFactory.RegisterConnection("Reporting", pgConnString, PostgreSqlDialect.Provider);
```
The string name provided to `RegisterConnection` must match that provided to the `NamedConnection` property on `CreateCrudServices`.
```csharp
services.AddPlugin(new AutoQueryFeature {
MaxLimit = 1000,
//IncludeTotal = true,
GenerateCrudServices = new GenerateCrudServices()
{
// Configure multiple databases, `public` is the default schema.
CreateServices = new List
{
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.
```csharp
services.AddPlugin(new AutoQueryFeature {
MaxLimit = 1000,
//IncludeTotal = true,
GenerateCrudServices = new GenerateCrudServices()
{
// Configure multiple databases, `public` is the default schema.
CreateServices = new List
{
new CreateCrudServices { NamedConnection = "Reporting" },
new CreateCrudServices { NamedConnection = "Finance", Schema = "trading" }
}
}
});
```
## Customizing Locode App
Locode includes a [declarative dev model](/locode/declarative) 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](/autoquery/autogen) `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](/locode/declarative),
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](/autoquery/autogen#export-code-first-dtos) with:
:::sh
`x csharp https://localhost:5001 -path /crud/all/csharp`
:::
### 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](https://northwind.locode.dev) Locode App:
[](https://northwind.locode.dev/locode/QueryEmployees)
### 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.
```csharp
var ormLite = services.AddOrmLite(options => options.UseSqlite(connString));
services.AddPlugin(new AutoQueryFeature {
MaxLimit = 100,
GenerateCrudServices = new GenerateCrudServices {
DbFactory = ormLite.DbFactory,
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:
| Type Methods | Description |
|---------------------------|-------------------------------------------------------------------------|
| AddAttribute() | Add Attribute to Type |
| AddAttributeIfNotExists() | Add Attribute to Type if not already exists |
| Property() | Resolve Property from Type |
| ReorderProperty() | Reorder where the DB Column appears in Type (changes API & UI ordering) |
| EachProperty() | Apply custom lambda to each matching property |
| RemoveProperty() | Omit properties from inclusion in code-gen type |
| Property Methods | Description |
|---------------------------|-------------------------------------------------------------------------|
| AddAttribute() | Add Attribute to Property |
| AddAttributeIfNotExists() | Add Attribute to Property if not already exists |
### Format search results
The [Declarative Attributes docs](/locode/declarative) 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:
```csharp
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](/locode/formatters), another example used in
Northwind is `FormatMethods.IconRounded` on `PhotoPath`:
```csharp
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.
::: tip
The original images in Northwind were migrated to reference external images using the following SQL:
```sql
update Employee set PhotoPath = "/profiles/employees/" || Employee.Id || ".jpg"
```
Where its uploads are now managed by the configured `FilesUploadFeature` and built-in File Input UI controls.
:::
### ECMAScript Internationalization APIs
ECMAScript's rich `Intl.NumberFormat`, `Intl.DateTimeFormat` and `Intl.RelativeTimeFormat` APIs are also available from the
typed [[Intl*] Attributes](/locode/formatters.html#intl-attributes) which `OrderDetail` makes use of to format `UnitPrice`
in **USD Currency** and `Discount` in a **% percentage** format:
```csharp
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.
