Getting Started with DataProvider

This walkthrough takes you from an empty folder to a running, type-safe database query using the full DataProvider toolchain: DataProviderMigrate for schema, Lql for queries, and DataProvider for C# code generation.

Prerequisites

  • .NET 10 SDK or later
  • A terminal

No database server required — we use SQLite.

1. Create the project

mkdir MyApp && cd MyApp
dotnet new console
dotnet new tool-manifest

2. Install the CLI tools

dotnet tool install DataProvider --version 0.9.6-beta
dotnet tool install DataProviderMigrate --version 0.9.6-beta
dotnet tool install Lql --version 0.9.6-beta

3. Add the runtime package

dotnet add package Nimblesite.DataProvider.SQLite --version 0.9.6-beta

Set the target framework in MyApp.csproj:

<PropertyGroup>
  <TargetFramework>net10.0</TargetFramework>
  <Nullable>enable</Nullable>
  <LangVersion>latest</LangVersion>
</PropertyGroup>

4. Define the schema

Create example-schema.yaml:

name: example
tables:
  - name: Customer
    schema: main
    columns:
      - name: Id
        type: Text
        isNullable: false
      - name: CustomerName
        type: Text
        isNullable: false
      - name: Email
        type: Text
        isNullable: true
    primaryKey:
      columns:
        - Id

Generate the database:

dotnet DataProviderMigrate migrate \
  --schema example-schema.yaml \
  --output app.db \
  --provider sqlite

5. Write a query in LQL

Create GetCustomers.lql:

Customer
|> filter(fn(row) => (@customerId IS NULL OR Customer.Id = @customerId))
|> select(Customer.Id, Customer.CustomerName, Customer.Email)
|> order_by(Customer.CustomerName)

Transpile it to SQLite:

dotnet Lql sqlite \
  --input GetCustomers.lql \
  --output GetCustomers.generated.sql

6. Configure DataProvider

Create DataProvider.json:

{
  "queries": [
    {
      "name": "GetCustomers",
      "sqlFile": "GetCustomers.generated.sql"
    }
  ],
  "tables": [
    {
      "schema": "main",
      "name": "Customer",
      "generateInsert": true,
      "generateUpdate": true,
      "generateDelete": true,
      "primaryKeyColumns": ["Id"]
    }
  ],
  "connectionString": "Data Source=app.db"
}

Generate the C# extension methods:

dotnet DataProvider sqlite \
  --project-dir . \
  --config DataProvider.json \
  --out ./Generated

7. Wire everything into MSBuild

Instead of running the tools manually, add targets to MyApp.csproj so every build regenerates everything:

<Target Name="RunDataProviderMigrate" BeforeTargets="CoreCompile">
  <Exec Command="dotnet DataProviderMigrate migrate --schema example-schema.yaml --output app.db --provider sqlite" />
</Target>

<Target Name="RunLqlTranspiler" BeforeTargets="CoreCompile" DependsOnTargets="RunDataProviderMigrate">
  <Exec Command="dotnet Lql sqlite --input GetCustomers.lql --output GetCustomers.generated.sql" />
</Target>

<Target Name="RunDataProvider" BeforeTargets="CoreCompile" DependsOnTargets="RunLqlTranspiler">
  <Exec Command="dotnet DataProvider sqlite --project-dir . --config DataProvider.json --out ./Generated" />
  <ItemGroup>
    <Compile Include="Generated/**/*.g.cs" />
  </ItemGroup>
</Target>

8. Consume the generated extension method

Edit Program.cs:

using Microsoft.Data.Sqlite;
using Nimblesite.DataProvider.Core;
using MyApp.Generated;

await using var connection = new SqliteConnection("Data Source=app.db");
await connection.OpenAsync();

var result = await connection.GetCustomersAsync(customerId: null);

var message = result switch
{
    Result<IReadOnlyList<GetCustomersRow>, SqlError>.Ok ok =>
        $"Found {ok.Value.Count} customers",
    Result<IReadOnlyList<GetCustomersRow>, SqlError>.Error err =>
        $"Query failed: {err.Value.Message}"
};

Console.WriteLine(message);

This is the default generator output — errors are explicit in the return type, so the compiler makes you handle both the success and failure branches. Prefer a different shape (thrown exceptions, Task<T>, a custom result type)? See Customising generated code.

Next Steps