Savers

Overview

Savers is a small model-first SQL library for C#, a sort of mini ORM. You mark up a normal C# class with attributes, and Savers generates the CREATE TABLE for it. Queries are built with a fluent, LINQ-style chain instead of hand-written SQL strings. I built it for my A-level Computer Science project in March 2024, then pulled it out into its own repo with a MySQL backend, an SQLite backend and an xUnit test project.

The problem

My C# projects had model classes for their data, plus SQL strings that repeated the same table and column names by hand. When a model changed, the SQL had to change too, and I'd only find a mismatch at runtime.

At the same time I was writing JavaScript APIs with Sequelize, where the model is the schema, and I used LINQ in C# all the time. Savers was me trying to get both in C#: the class defines the table, and queries read like a chain of method calls.

Two more things shaped it. Friends who wanted to use it were on SQLite and I was on MySQL, so the query API had to be separate from the database behind it. And I wanted tests, so I could keep adding features without breaking the ones I already had.

How it works

Models. A class gets [DatabaseTable("Users")], and each property gets [DatabaseColumn(name, flags)], where the flags cover things like primary key, auto increment, not null and foreign key. [DatabaseIgnore] leaves a property out. A class with no table attribute or no primary key throws (CannotGetTableName, NoPrimaryKeyFound) instead of getting a half-right table. If a property is a foreign key to another model, the generator builds that model's table first and puts it in front, so the referenced table always exists before the one pointing at it.

Queries. StartQuery() gives you an IStatement<T> (Select, Count, Sum, Average, Update, Insert). Every statement returns an IClause<T> (Where, WhereStarts, WhereEnds, WhereContains, OrderBy, Limit), and Finalise() hands the result to an executor, which runs it with Dapper. A query reads like new MySqlSaver<TestClass>().StartQuery().Select().Where(("TestValueString", "abc")).Finalise().

Values never go into the SQL text. Each one becomes a named parameter (@TestValueString, with a number added if the same column is used twice), so the values can't be used for SQL injection.

The part I like most is how clauses are put together. You can call them in any order, but SQL needs them in a fixed order, so each clause is stored with a number:

1public class SqlData(string connection_path)2{3    public string Sql => StatementSql + FilterSql + ";";4    public string ConnectionPath { get; private set; } = connection_path;5    public string StatementSql { get; set; }6    public string FilterSql => string.Join(" ", Filters.OrderBy(x => x.Value.Item2).Select(x => $"{x.Key} {x.Value.Item1}"));78    public Dictionary<string, (string, int)> Filters { get; set; } = [];910    public List<DataParam> Params { get; set; } = [];11}

1    public IClause<T> Limit(int count)2    {3        switch (count)4        {5            case 0:6                throw new LimitCannotBeZero();7            case < 0:8                throw new LimitCannotBeNegative();9            default:10                Data.Filters["LIMIT"] = (count.ToString(), int.MaxValue);11                return this;12        }13    }1415    public IExecutor Finalise()16    {17        return new MySqlExecutor(Data);18    }

The clauses go in a dictionary keyed by keywordL8, each with a number. WHERE gets 1, ORDER BY gets int.MaxValue - 1, and LIMIT gets int.MaxValueL10. FilterSql sorts by that numberL6 when it builds the string, so .Limit(10).Where(...) still comes out with WHERE before LIMIT. Calling Where twice adds to the existing entry with AND instead of adding a second WHERE. Bad input fails straight away with a specific exception, for example a limit of zero or lessL5-8, rather than as an SQL error later.

Backends. Savers.Shared holds the interfaces (ISqlSaver, IStatement, IClause, IExecutor), the exceptions and the table generator. Savers.MySql and Savers.SqlLite each implement them for their own database, so code using Savers only depends on the interfaces. I wrote the SQLite backend myself in the same week, and moved all three libraries to .NET Standard 2.0 with NuGet package metadata.

Tests. 15 xUnit test methods compare the generated DDL and SQL against strings I wrote by hand.

Outcome

Savers did its job in my A-level project: the models defined the database, so I didn't write the table SQL by hand. It was also the first project where I wrote tests as I went. A year later I used sequelize-typescript in CiB Group 11, which uses the same decorated-model idea in a mature library, and it was clear how much work the last 20% of an ORM is.

Reading the repo version back now (I reworked it after moving it out of the project, so I can't say which of these the project copy had), I found real bugs that the tests didn't catch:

• A missing space. SqlL3 joins the statement and the clauses with no space between them, so a query with a clause comes out as ...FROM TestClassWHERE.... The clause tests only check FilterSql and the statement tests have no clauses, so nothing ever tested the two together.
• Wrong SQL in the tests. WhereStarts and friends produce col = @p% instead of a LIKE, and the tests expect exactly that string. A test that compares against my own wrong answer only proves the code matches my mistake.
• Smaller ones. Where with more than one filter throws (it calls Single()), Select joins column names with AND instead of commas, and Average of several columns always divides by 2.

The lesson I took: tests that compare strings are a good start, but a query builder needs at least a few tests that run the SQL against a real database.

Column and table names also go into the SQL as plain text, not escaped. That's fine while they come from my own attributes, but not if user input could ever reach them.