Home

Awesome

SogePoco

SogePoco is a dotnet library and source generator to interact with SQL databases in a type safe and easy manner. It leverages incremental source generators to generate ADO.NET commands and readers. Contents of tables is represented in auto-generated classes following plain-old-CLR-object expectations. Currently supported databases: SQL Server, Postgres, Sqlite
Name: Source Generated Plain old clr objects

Quickstart / Demo

Watch the video

If you wish to repeat process yourself, use same instructions but in text format. It works on Windows too as evidenced by tests. In Visual Studio w/wo ReSharper you may need to unload/reload projects to make "errors" go away.

Example

Assuming that you are dealing with following sqlite database:

CREATE TABLE foo(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    nullable_text TEXT,
    not_nullable_text TEXT NOT NULL);

CREATE TABLE child_of_foo(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    foo_id INTEGER NOT NULL,
    val INTEGER NULL,
    CONSTRAINT FK_ChildOfFoo_fooId FOREIGN KEY (foo_id) REFERENCES foo(id));

and that you used default settings in code generator you will end up with following generated Poco classes


public class Foo {
    public long Id {get; set;}
    public string NotNullableText {get; set;}
    public string? NullableText {get; set;}

    public Foo(long id, string notNullableText, string? nullableText) {
        this.Id = id;
        this.NotNullableText = notNullableText;
        this.NullableText = nullableText;
    }

    public Foo() : this(default(long), "", default(string?)) {}

    public override string ToString() => @"foo";
} 

public class ChildOfFoo {
    public long Id {get; set;}
    public long FooId {get; set;}
    public long? Val {get; set;}
    
    public class ForeignKeysCollection {
        public SogePoco.Common.JoinInfo<ChildOfFoo,SogePocoIntroduction.Pocos.Foo> Foo_by_FooId                 
            => throw new System.Exception("This code should not be called in runtime, it only servers as DSL during query generation");
    }
    public ForeignKeysCollection ForeignKeys => throw new System.Exception("This code should not be called in runtime, it only servers as DSL during query generation");

    public ChildOfFoo(long id, long fooId, long? val) {
        this.Id = id;
        this.FooId = fooId;
        this.Val = val;
    }

    public ChildOfFoo() : this(default(long), default(long), default(long?)) {}

    public override string ToString() => @"child_of_foo";
}

There will also be a database.cs generated with methods: Insert(Foo),Insert(ChildOfFoo), Update(Foo), Update(ChildOfFoo).

If you request query to be generated in the following way:


[GenerateQueries]
public class InvokeGenerator {
    public void GetFoo() => Query.Register((Foo x) => x.NotNullableText == "something");
}

SogePoco will generate following code:


public static class DatabaseExtensions {    
    public static async System.Collections.Generic.IAsyncEnumerable<SogePocoIntroduction.Pocos.Foo> GetFoo(
            this SogePocoIntroduction.Database self) {

        await using var cmd = self.CreateCommand();
        cmd.Parameters.Add(
            CreateParam(
                cmd,
                @"$0", 
                ((object?)@"something" ?? System.DBNull.Value)));                        
        cmd.CommandText = @"SELECT
""t0"".""id"", ""t0"".""not_nullable_text"", ""t0"".""nullable_text""
FROM ""foo"" as ""t0""
WHERE ""t0"".""not_nullable_text"" = $0;";
        self.LastSqlText = cmd.CommandText;
        self.LastSqlParams = cmd.Parameters.Cast<System.Data.Common.DbParameter>().ToArray();
        await using var rdr = await cmd.ExecuteReaderAsync();

        while (await rdr.ReadAsync()) {
            var iCol = 0;

            var itm0 = new SogePocoIntroduction.Pocos.Foo(
                id:(long)rdr.GetValue(iCol++), 
                notNullableText:(string)rdr.GetValue(iCol++), 
                nullableText:rdr.GetValue(iCol++) switch { System.DBNull => null, var x => (string?)x});
            
            yield return itm0;
        }            
    }
    
    private static System.Data.Common.DbParameter CreateParam(System.Data.Common.DbCommand cmd, string n, object? v) {
        var result = cmd.CreateParameter();
        result.ParameterName = n;
        result.Value = v;
        return result;
    }
}

...hence just regular contemporary async ADO.NET command with added benefit of LastSqlText and LastSqlParams inspired by PetaPoco and others.

Features

For brevity, please assume that you are dealing with same sqlite database schema as defined in former point.

use SogePoco.Common;

[GenerateQueries]
class SomeClassToHoldMyRequestsToGenerateQueries {
    public void SomeRequestedQuery() =>	
        Query.Register((Foo f) => f.NullableText == "abc");		
}

use SogePoco.Common;

[GenerateQueries]
class SomeClassToHoldMyRequestsToGenerateQueries {
    public void SomeRequestedQuery(string x) =>	
        Query.Register((Foo f) => f.NullableText == x);		
}

use SogePoco.Common;

[GenerateQueries]
class Irrelevant {    
    //to get equivalent of `select * from Foo where nullable_text is null or nullable_text = $0` use:
    public void GetMatchingFoo(string p) =>	
        Query.Register((Foo f) => f.NullableText == null || f.NullableText == p);
    
    //to get equivalent of `select * from Foo where nullable_text is not null and nullable_text = $0` use:
    public void GetMatchingFoo(string p) =>	
        Query.Register((Foo f) => f.NullableText != null && f.NullableText == p);	
}

use SogePoco.Common;

[GenerateQueries]
class Irrelevant {
    /* 
    to get equivalent of
        select * 
        from child_of_foo 
        join foo on foo.id = child_of_foo.foo_id 
        where child_of_foo.val = $0` 
    Notice: you don't need to specify how to join tables. This information is stored as foreign key
    */
    public void GetValueTupleOfChildOfFooAndFoo(int neededVal) =>	
        Query
            .From<ChildOfFoo>()
            .Join(cof => cof.ForeignKeys.Foo_by_FooId)		
            .Where( (cof,f) => cof.Val == neededVal); //no `.Select(...)` hence generated query will return Value tuple of all joined types/pocos. In this case: (ChildOfFoo, Foo)
    
    //you can initiate join from another type (one that doesn't have foreign key) like this:
    public void GetValueTupleOfChildOfFooAndFoo(int prm) =>
        Query
            .From<Foo>()
            .Join((ChildOfFoo cof) => cof.ForeignKeys.Foo_by_FooId, f => f) //second parameter is very important when you have joined at least one table already
            .Where( (cof,f) => f.Val == prm);
    
    public void GetValueTupleOfChildOfFooAndFoo(int v) =>	
        Query
            .From<ChildOfFoo>()
            .Join(cof => cof.ForeignKeys.Foo_by_FooId)		
            .Where( (cof,f) => cof.Val == v)
            .Select((cof,f) => cof); //same as first query but generated query will return `ChildOfFoo` instances instead of ValueTuple: (ChildOfFoo, Foo)

    //you can also use .LeftJoin() with same syntax as .Join() above to get `left join` instead of `inner join` 
}

use SogePoco.Common;

[GenerateQueries]
class Second {
public void GetFoo() =>	
    Query
        .From<Foo>()
        .OrderByAsc(f => f.NotNullableText);
        
        //for reverse order use: 
        //.OrderByDesc(f => f.NotNullableText)
}

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetFoo() =>	
        Query
            .From<Foo>()
            .Take(1);            
}

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetMatchingFoo(string[] mustBeIn) =>
        Query.Register((Foo f) => mustBeIn.Contains(f.NullableText));
}

[!NOTE] Support:

use SogePoco.Common;

[GenerateQueries]
class Second {
    public void GetMatchingFoo() =>	
        PostgresQuery  //notice: different root here
            .From<Foo>()
            .ForUpdate();
            //or .ForShare()
            //or .ForNoKeyUpdate()
            //or: .WithForClause("update nowait or anything custom goes here") 
}

Note blocking/locking rules in Postgres are much simpler than in Sql Server. It is advised in Sql Server to use different isolation levels instead of playing with query hints (e.g. with rowlock). See TestLockingUnrelatedTables.cs and TestLockingRelatedTables.cs to see it yourself. That's why I decided to not implement it (yet?)

There are many more features. At this moment it's best to look at rich collection of included xunit tests to know what else is supported.

System.Action<string> OnRowsAffectedExpectedToBeExactlyOne {set;}

Missing features, shortcomings

Some things that I consider as important and would like to implement them eventually

Nuget version / status

SogePoco.Common NuGet Version SogePoco.Common

SogePoco.Impl NuGet Version SogePoco.Impl

SogePoco.Template.Postgres NuGet Version SogePoco.Template.Postgres

SogePoco.Template.SqlServer NuGet Version SogePoco.Template.SqlServer

SogePoco.Template.Sqlite NuGet Version SogePoco.Template.Sqlite

Github actions

assure_project_builds dotnet pack works for all libraries and templates:

assure_template_is_usable_postgres postgres template generates working POCOs and queries
(using postgres server instance running in podman container)?

assure_template_is_usable_sqlite sqlite template generates working POCOs and queries
(using in memory sqlite)?

assure_template_is_usable_sqlserver sqlserver template generates working POCOs and queries
(using sqlserver instance running in podman container)?

tests_postgresql_and_common all tests (~100) pass when using postgres server
(instance running in podman container)?

tests_sqlserver_and_common
all tests (~100) pass when using sqlserver server
(instance running in podman container)?

tests_sqlite_and_common all tests (~100) pass when using in memory sqlite (on ubuntu)?

windows_tests_sqlite_and_common all tests (~100) pass when using in memory sqlite (on windows)?

High Level Design

<details> <summary>Read more about design...</summary>

Whole library relies on knowing:

This way, source generator doesn't need connect to db everytime it needs schema. Otherwise things would be slow.
Calling extraction process is only need when db changes e.g. after performing db migrations.
What schema extractor does can be summarized in following way:

Extraction step

What logically follows after extraction are two invocations of source generator. First one generates poco classes and database class. Second generates queries build using those poco classes and registering them as extensions of database class.

First invocation overview: POCOs and database classes generation

Second invocation overview: POCO query generation

</details>

Scope of project and motivation

I wanted to build a spiritual successor of PetaPoco, AsyncPoco projects but instead of emitting MSIL. I wanted to use incremental source generators which is a recommended technology for metaprogramming. Apart from that I also wanted to:

Goals

Non goals

License

Apache License 2.0