s-e-f / squeel Goto Github PK

View Code? Open in Web Editor NEW

1.0 1.0 0.0 99 KB

Compile-time SQL query validation and entity generation

C# 100.00%

csharp database dotnet postgres source-generator sql

squeel's Introduction

Squeel - What is it?

Squeel is a source generator that automatically validates your SQL queries and generates your POCO's.

Squeel is also the correct pronunciation of SQL.

Security

Squeel uses string interpolation to simplify parameters. This is completely safe with Squeel through the SqueelInterpolatedStringHandler. This special interpolated string handler replaces interpolation holes with parameters, keeping your SQL queries safe. No SQL injections in Squeel!

Type Safety

Squeel validates queries at build time, so if your code compiles, you KNOW your SQL queries are correct. It also generates the C# POCO's automatically so you KNOW the mappings are correct. The validation occurs by running the queries in a transaction that is immediately rolled back, so no mutations occur on your databases.

Ease of Use

With Squeel you no longer have to write all that pesky boilerplate. This includes the POCO's, DTO's, Entities or whatever you want to call them. Squeel does it for you. In addition to validating the SQL query at build time, it uses the retrieved schema information to generate a correctly shaped C# record, and handles the parameter passing and reader/parser nonsense for you.

Correctness

Squeel helps you in writing correct SQL queries by essentially doing the integration testing automatically at build time, without having to write any tests for the SQL to C# mappings manually.

Simplicity

Squeel aims to simplify writing SQL queries and managing your database with SQL. It is NOT an ORM (object relational mapper) and never will be.

How does it work?

Squeel requires a local database it can use to validate your SQL queries. Provide it by setting the SqueelConnectionString MSBuild property in your .csproj file.

Due to a technical limitation in MSBuild and C# source generators, the connection string can NOT contain any semicolons (;). To work around this, substitute all semicolons for plus (+) symbols.

<Project>

    <PropertyGroup>
        <SqueelConnectionString>Host=localhost+User ID=postgres+Password=secretpassword+Database=mydatabase</SqueelConnectionString>
    </PropertyGroup>

</Project>

Squeel also generates as much boilerplate ADO.NET code as possible, turning this

record Product(string Sku, string Description, decimal Price);

async Task<IEnumerable<Product>> GetProductsBelowPrice(this DbConnection connection, decimal maxPrice)
{
    using var command = connection.CreateCommand();
    command.CommandText = "SELECT sku, description, price FROM products WHERE price < @max_price";
    command.Parameters.Add(new NpgsqlParameter("max_price", maxPrice));
    using var reader = await command.ExecuteReaderAsync();
    var products = new List<Product>();
    while (await reader.ReadAsync())
    {
        products.Add(new Product(
            Sku: reader.GetString(0),
            Description: reader.GetString(1),
            Price: reader.GetDecimal(2)
        ));
    }
    return products;
}

Into this

async Task<IEnumerable<Product>> GetProductsBelowPrice(this DbConnection connection, decimal maxPrice)
{
    return connection.QueryAsync<Product>($"""
        SELECT sku, description, price
        FROM products
        WHERE price < {maxPrice}
        """);    
}

squeel's People

Contributors

Stargazers

Watchers

squeel's Issues

SqueelConnectionString in csproj can cause conflict amongst development teams

If the SqueelConnectionString is specified in the csproj, it forces each dev working on the project to use the same connection string which can cause problems.

Environment is technically banned by is an option for Squeel, although that introduces the issue of single environment multiple projects (and .NET doesn't have env files).

.NET does have user secrets, which we can technically see from our source generator with CompilerVisibleProperty Include="UserSecretsId", so maybe that is the best solution, but we would have to read the file which is technically also forbidden in source generators.

Add support for mysql

The mysql ado.net package targets netstandard 2.0 which is perfect

Postgres: complex columns and array columns

Postgres has native support for complex columns and array columns, which we should use to our benefit

INSERT, UPDATE, DELETE statements

In addition to these, we want to run validations inside a transaction

Create a readme

Warn when SELECT * is used

Create a NuGet pipeline

Add support for mssql

The mssql ado.net package targets .net standard 2.0, which is perfect

NuGet properties

The nupkg currently has next to no metadata like description and tags. Before the first full release add these

Commit to .NET 8 for interceptors as minimum version

Generating code for mutations is very uncomfortable without interceptors, so I will probably move to .NET 8 already and drop .NET 7 completely

.NET 8: Incorporate Interceptors

Currently in .NET 7 the generated code in QueryAsync uses a Dictionary<Type, Func<...>> to map known types T to their parsers involving (object) and (T) casts.

In .NET 8 we can use interceptors to generate specific methods for every call site, removing the need for a dictionary and casting hell.

I've already validated that generic methods can be intercepted by non-generic methods as long as the return type and parameter types match, so an example interceptor would look like:

public static class InterceptedCalls
{
    [InterceptsLocation("path", line, column)] // line and column are 1-based
    public static Task<IEnumerable<User>> QueryUserAsync(this DbConnection connection, ref SqueelInterpolatedStringHandler query, CancellationToken ct = default)
    {
        // Notice that T is concrete User here since we are intercepting a single exact call where T is known, this work!
    }
}