Home

Popsql is a library eliminating the need for magic SQL strings in your code.

Currently we are still doing active development, but the basic idea is that magic strings are evil, and that writing high-performance database-driven applications is nearly impossible using common frameworks like the Entity Framework. This leads to the need to resort to raw SQL to control the underlying database calls yourself. Popsql is an attempt to create a fluent API for representing these SQL statements in such a way that you have control over the output while keeping your code as maintainable as possible.

To give a sneak preview of where things are going (these are working examples):

    // SELECT [Id], [UserName], [Email], [Age] FROM [Users] WHERE ([Age] >= 30) ORDER BY [Id] DESC, [Name] ASC
    var sql = Sql
        .Select("Id", "UserName", "Email", "Age")
        .From("Users")
        .Where(SqlExpression.GreaterThanOrEqual("Age", 30))
        .OrderBy("Id", SqlSortOrder.Descending)
        .OrderBy("Name")
        .ToSql();

    // SELECT [u].[Id], [u].[UserName], [p].[Email], [p].[Age] 
    // FROM [Users] [u]
    // LEFT JOIN [Profiles] [p] ON ([u].[Id] = [p].[UserId])
    // WHERE ([p].[Age] >= 30) 
    // ORDER BY [u].[Id] DESC, [u].[Name] ASC
    SqlTable u = new SqlTable("Users", "u");
    SqlTable p = new SqlTable("Profiles", "p");
    var sql = Sql
        .Select(u + "Id", u + "UserName", p + "Email", p + "Age")
        .From(u)
        .LeftJoin(p, SqlExpression.Equal(u + "Id", p + "UserId"))
        .Where(SqlExpression.GreaterThanOrEqual(p + "Age", 30))
        .OrderBy(u + "Id", SqlSortOrder.Descending)
        .OrderBy(p + "Name")
        .ToSql();

    // INSERT INTO [Users] ([Id], [UserName], [Email]) VALUES (@Id, @UserName, @Email)
    var sql = Sql
        .Insert()
        .Into("Users", "Id", "UserName", "Email")
        .Values(
            "Id" + SqlConstant.Null, 
            "UserName" + (SqlConstant)"Wouter", 
            "Email" + (SqlConstant)"mail@popsql.local")
        .ToSql();

    // UPDATE [Users] SET [UserName] = 'Wouter' WHERE ([Id] = 5)
    var sql = Sql
        .Update("Users")
        .Set("UserName", "Wouter")
        .Where(SqlExpression.Equal("Id", 5)
        .ToSql();

    // DELETE FROM [Users] WHERE ([Age] > 30)
    var sql = Sql
        .Delete()
        .From("Users")
        .Where(SqlExpression.GreaterThan("Age", 30))
        .ToSql();

This counts as the documentation for now, but stay tuned for more information! If you want to play around with the API, feel free to check out the NuGet package at https://www.nuget.org/packages/popsql/!