Dipper 0.1.0

Dipper: The companion Dapper didn't know it needed

A true companion to Dapper for that near-native-sql experience. This source generator automatically converts SQL files into strongly typed query classes for use with Dapper.

The generator itself does as little as possible and lets the powerful language of SQL express itself.

This project is an experiment to explore writing .sql files and "compiling" those into strongly typed source files, then providing easy to use Dapper extensions for execution quickly turning complicated SQL calls into simple declarative code.

TODO

• Is COALESCE enough to handle complex cases
• How do we handle deep hydration like the lovely EFCore (can we do it elegantly) (Dapper.Mapper?)?
• How do we strongly type database column names. Figure out a strategy for a single-source-of-truth for column names, so we can discover when a model field does not match the model field.
• Generated source file naming clashes. Do we care?
• Make the type mapping extensible so we can add new and exciting types like enums
• Add a ? to the type syntax to allow for optional fields
• Release as a nuget package
• Could we go hogwild and validate the output fields to the actual database or some other source of truth?
• How the heck do you test Source Generators?

Features

• Parses SQL files with metadata comments
• Generates strongly typed input classes based on SQL parameters with type annotations
• Creates extension methods for executing queries with Dapper
• Supports synchronous and asynchronous execution

This source generator automatically converts SQL files into strongly typed query classes for use with Dapper.

How to Use

1. Add the Source Generator to Your Project

Run dotnet add package Dipper in your desired project that should use Dipper.

Add a .sql file anywhere and make sure you're adding those files with the <AdditionalFiles /> xml tag

<ItemGroup>
  <AdditionalFiles Include="**/*.sql" />
</ItemGroup>

Now watch the magic happen.

<ItemGroup>
  <ProjectReference Include="..\Dipper.Generator.csproj" 
                    OutputItemType="Analyzer" 
                    ReferenceOutputAssembly="false" />
</ItemGroup>

3. Use the Generated Classes

// Get your database connection
using var connection = new NpgsqlConnection(connectionString);

// Create input parameters
var input = new GetUsersQuery.Input
{
    SearchTerm = "john",  // Optional search term
    IsActive = true       // Optional filter for active users
};

// Execute the query and get strongly typed results
var users = connection.Execute(input);

// Or async
var usersAsync = await connection.ExecuteAsync(input);

// View the SQL that will be executed with the parameters
string sql = GetUsersQuery.GetSql(input);
Console.WriteLine(sql);

SQL File Format

The SQL files should follow this format:

-- name: QueryName (optional, defaults to filename)
-- result: ResultType (optional, specifies return type)
-- Any other comments
-- SQL statement with @ParameterName:type annotations;
SELECT * FROM users
WHERE id = @Id:uuid

Conditional SQL Filtering with COALESCE

The Problem: Optional Query Parameters

When building dynamic SQL queries with optional filter parameters, we often need to handle cases where some parameters may be NULL or unspecified. The traditional approach of concatenating SQL strings can lead to complex, error-prone code:

// Problematic approach
var sql = "SELECT * FROM products WHERE 1=1";
if (category != null) sql += " AND category = @Category";
if (maxPrice != null) sql += " AND price <= @MaxPrice";
if (color != null) sql += " AND color = @Color";
// More conditions...

This approach becomes unwieldy as the number of parameters increases and we've now increased complexity as we handle conditionals multiple places.

The Solution: Abusing COALESCE

We leverage PostgreSQL's COALESCE function in a clever way to create conditional filters that automatically activate or deactivate based on parameter values:

SELECT * FROM products
WHERE 
  COALESCE(category = @Category:text, TRUE) AND
  COALESCE(price <= @Price:money, TRUE) AND
  COALESCE(color = @Color:text, TRUE)

It's almost disgusting, but it works wonders, the consequences of this approach are undiscovered for now.

How It Works

1. For NULL parameters: When a parameter is NULL, the comparison (e.g., category = NULL) evaluates to NULL, and COALESCE(NULL, TRUE) returns TRUE, effectively making the condition inactive.

2. For non-NULL parameters: The comparison (e.g., category = 'Electronics') evaluates to TRUE or FALSE normally, and COALESCE returns this value, making the condition active.

OR Conditions

For OR conditions, we use the same technique but with FALSE as the default:

SELECT * FROM products
WHERE
  (COALESCE(color = 'red', FALSE)) OR
  (COALESCE(color = 'blue', FALSE)) OR
  (COALESCE(on_sale = TRUE, FALSE))

Complex Filtering Example

You can combine AND and OR conditions for sophisticated filtering:

SELECT * FROM products
WHERE 
  -- These conditions are TRUE when the parameter is NULL
  (COALESCE(category = @Category:text, TRUE)) AND
  (COALESCE(price <= @Price:money, TRUE)) AND
  
  -- At least one of these must be TRUE (if all parameters are NULL, no products match)
  (
    (COALESCE(color = @Color:text, FALSE)) OR 
    (COALESCE(brand = @Brand:text, FALSE)) OR
    (COALESCE(on_sale = @OnSale:bool, FALSE))
  )

Benefits

• "Clean-enough", maintainable code: Single SQL query without complex string concatenation and without introducing new templating syntax for conditional AND and OR expressions
• Readability: Makes the intention of optional filtering clear. The caveat being that COALESCE is not immediately obvious

2. Add SQL Files to Your Project

Create a SQL file with metadata comments and optional conditional blocks:

-- name: GetProducts
-- result: Models.ProductView
-- Query users with optional filtering
SELECT * FROM products
WHERE 
  COALESCE(category = @Category:text, TRUE) AND
  COALESCE(price <= @Price:money, TRUE) AND
  COALESCE(color = @Color:text, TRUE)
ORDER BY name

Make sure to include the SQL files in your project file:

<ItemGroup>
  <AdditionalFiles Include="**/*.sql" />
</ItemGroup>

Parameter Type Mapping

SQL parameters use the format @ParameterName:type where type is mapped to C# types:

Example generation result

namespace Dipper.Generated.SqlQueries
{
    public class SelectProductsQuery
    {
        public const string Sql = @"
-- name: SelectProducts
-- result: Dipper.Tests.Models.Product
select * from products
where id = @Id;
";
        public class Input
        {
            public Guid Id { get; set; }
        }

        // Result type: Dipper.Tests.Models.Product
    }
    
    /// <summary>
    /// Extension methods for executing the query with Dapper.
    /// </summary>
    public static partial class DapperExtensions
    {
        public static IEnumerable<Dipper.Tests.Models.Product> Query(this IDbConnection connection, SelectProductsQuery.Input parameters, IDbTransaction transaction = null, int? commandTimeout = null)
            => connection.Query<Dipper.Tests.Models.Product>(SelectProductsQuery.Sql, parameters, transaction, commandTimeout: commandTimeout);

        public static Task<IEnumerable<Dipper.Tests.Models.Product>> QueryAsync(this IDbConnection connection, SelectProductsQuery.Input parameters, IDbTransaction transaction = null, int? commandTimeout = null)
            => connection.QueryAsync<Dipper.Tests.Models.Product>(SelectProductsQuery.Sql, parameters, transaction, commandTimeout: commandTimeout);

        public static Dipper.Tests.Models.Product QuerySingle(this IDbConnection connection, SelectProductsQuery.Input parameters, IDbTransaction transaction = null, int? commandTimeout = null)
            => connection.QuerySingle<Dipper.Tests.Models.Product>(SelectProductsQuery.Sql, parameters, transaction, commandTimeout: commandTimeout);

        public static Task<Dipper.Tests.Models.Product> QuerySingleAsync(this IDbConnection connection, SelectProductsQuery.Input parameters, IDbTransaction transaction = null, int? commandTimeout = null)
            => connection.QuerySingleAsync<Dipper.Tests.Models.Product>(SelectProductsQuery.Sql, parameters, transaction, commandTimeout: commandTimeout);
    }
}