The algebraic types wish is to be able to do scientific calculations more safely. I have built a system figures out the maximum safe speed limit for a car on a tight curve, and having the compiler validate my units of measure would go a long way towards ensuring the quality of the formulae over time as we tweak it.

The wish for a macro system is a different story entirely.

For the last, ooooh, 7 years or so, I've been building a relational data access system that I have come to realize is an attempt to build a static type checker for SQL queries. It's not quite to that level yet, but right now, for me it's the most convenient way to make type-safe access to a relational database. I also think it is a very convenient way to manage change of schema over time.

I know, every hot-headed developer makes his own "ORM", but I had some specific uses cases that made sense for me to roll my own system. At the time, the only options for data access were ADO.NET [0], NHibernate[1], and a slew of NHibernate clones[2].

I needed the ability to execute fairly arbitrary SQL against a slew of databases whose schema was off limits to me. I could make all the stored procedures I wanted, but i couldn't change any tables. I needed a better way to manage those stored procedures through different environments, for versioning and for regression testing if parameters changed. And I needed it to automatically manage things, so the idiot developers on my team wouldn't screw shit up with raw ADO.NET calls.

So I created SqlSiphon: https://github.com/capnmidnight/SqlSiphon

In the last couple of weeks I've managed to break a few of the features, but I'll be fixing them soon[3].

You define parameterized queries directly in your data access layer. These get translated into stored procedures whose name and parameters are gleaned from the sameness in your data access layer, e.g. "public UserPref GetUserPreferences(int userId){}" in C# will be used to generate a stored procedure in the database named GetUserPreferences that takes an integer parameter and selects out values that can be mapped to a structure called UserPref.

You define C# classes that map to your query views. This is why it's not an ORM: there is no attempt to resolve foreign key references to build an object graph from a query. This is also why it's fast enough for my needs: it is only a system for executing traditional SQL.

It supports SQL Server, Postgres (in master, will be fixing in dev), and MySQL (ditto). It can query from OLE DB sources like Access database. It is easy to extend to support querying from any database, as long as an ADO.NET driver exists for it. It is only a little more difficult to extend to support code generation for other databases.

At one time, it was able to analyze a database on, say, SQL Server, recreate the database in Postgres, and copy all of the data over. It lacks it now, as I had built it as a propriety extension for a client, but will be getting it back soon.

My immediate goal with the project is to create a type-safe way to manage correct database design across environments. It's all about the DRY principle. I'm very close to that goal, and even right now I think it's far better than using anything else, given the tradeoffs.

My long term goal is to make it into a system for breaking database vendor lockin. Once I have a system for analyzing query structure directly, I believe I'll be able to translate the majority of queries transparently between databases[4].

And it might not happen tomorrow, but I'm fairly certain it will eventually happen. If you told me 7 years ago that I would have made this thing that currently exists, I would have never believed it. But it's all features I need, and I make more and more progress every year, so I feel pretty confident that it will eventually get there.

[0] Which everyone used incorrectly--not disposing all of the associated objects properly, doing string concatenating for parameters, etc.

[1] Which had just started to prove out the problems of object-relational impedance mismatch.

[2] That were even worse performance-wise, and ORM in general is really only good for brand-new CRUD apps.

[3] That is no empty promise. I've broken things I need, so I will certainly be fixing it again. At any rate, the master branch is "stable" right now and will eventually be forked as "1.0", with the dev branch pretty soon getting stabilized and becoming "2.0".

[4] Or at the very least, create a SQL subset for which it can be done that guides developers into making structurally sound queries. Most of the problem with poor database performance comes from poor database developers writing poor queries. Most of the work for porting an application from one database to another is dealing with those shitty queries.

Interesting, sounds like Scala could fit the bill, algebraic data types and macros included ;-)

Plethora of type safe query libraries to choose from as well. No easy interop with .NET, however.

Saying that, why not LINQ-to-SQL or whatever the latest and greatest is from Microsoft, Performance?

Quite a few reasons. Originally, I was stuck on .net 2 (.net 3.5 had only just come out), so LINQ wasn't available. Then, for a long time, LINQ to SQL was not thread safe. It doesn't generate stored procedures, so there is a significant network overhead for complex queries and complex queries can't be shared across language independent projects. And finally, my system provides a ton of metadata that I could then start leveraging for script generation. Entity Framework was too all-or-nothing, and my own system had too much momentum for me by that point. It has evolved over time and it has always just been easier to adapt it than to take on whatever has been popular at the time.

Thanks for taking the time to write this up. I really appreciate it. I have been working on low level database kind of things recently so I am keen to hear about what people are doing in userspace (from a database point of view).