Asmerald 0.1.0
dotnet add package Asmerald --version 0.1.0
NuGet\Install-Package Asmerald -Version 0.1.0
<PackageReference Include="Asmerald" Version="0.1.0" />
paket add Asmerald --version 0.1.0
#r "nuget: Asmerald, 0.1.0"
// Install Asmerald as a Cake Addin
#addin nuget:?package=Asmerald&version=0.1.0
// Install Asmerald as a Cake Tool
#tool nuget:?package=Asmerald&version=0.1.0
Asmerald
A library for writing type safe SQL statements in code.
It provides:
- type safety where possible
- support of major database providers
- low runtime overhead
See this very good answer on Stackoverflow: https://stackoverflow.com/questions/22860167/what-exactly-does-type-safe-queries-means
Supported database providers
SQLite | PostgreSQL | MySql | MariaDb | MSSql | Oracle | |
---|---|---|---|---|---|---|
SQL standard | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
Provider specific | ✓ |
SQL standard = Most common statements such as "SELECT", "WHERE", "JOIN"s, "HAVING", etc. (mostly SQL-92) Provider specific = Specific database statements (e.g. materialized views), stored procedures, etc.
Limitations
Because of SQL being a declarative language, not everything can be checked in code by a compiler without introducing new complexity or deviating from SQL. For example, there is no check in place wether
- tables of columns mentioned in SELECT, HAVING, GROUP By, etc. statements are added as "FROM" or "JOIN" statements.
- ...
Task list
- Add PostgreSQL support
- Add SQL standard support for all database providers
- Implement functional tests to verify generated SQL string
- Source generators to transform queries during compilation to SQL strings. This approach would not cause any performance impact at runtime at all - type safety for free.
Example
Simple query using Asmerald and Dapper:
// Build query with Asmerald
var dslCtxt = new SQLiteDSLContext();
var ts = dslCtxt
.Select(Tbl_Cards.Id(), Tbl_Cards.Name(), Tbl_Cards.Form().As("test"))
.From<Tbl_Cards>()
.Where(Tbl_Cards.Id().Greater(10))
.OrderBy(Tbl_Cards.Id())
.Limit(50)
.QueryBuilder
.BuildPreparedStatement();
// Use result as input for Dapper query
var res = dbConnection.Query(ts.Statement, param: ts.Parameters)
.ToList();
Joins and Where statements:
var stmt = dslCtxt
.Select()
.All()
.From<TblCards>()
.InnerJoin<TblCards, TblPacks>()
.On(TblCards.Pack_id(), TblPacks.Id())
.Where(TblCards.Attribute().Equal("s")
.And(TblCards.Form().Equal("c"))
.Group()
.And(TblCards.Id().Equal(1)
.And(TblCards.Id().Equal(2))))
.Limit(20)
.Offset(5)
.QueryBuilder
.BuildPreparedStatement();
Why?
Don't limit yourself to abstracted libraries / frameworks and utilize the potential databases provide. Enjoy writing SQL - with armor.
"Why not use a ORM?"
I used ORMs in multiple projects and have not had good experiences using them. Most of them forced me to do a "code first" approach which never worked for me as I design my database schemes first. Besides there were issues with errors during setup I spent hours fixing and figuering out cryptic error messages.
When I had adjusted my code to the needs of the ORM and fixed error messages I got problems writing my statements. Easy ones are easy but once you get a bit more complex you basically have to write SQL again.
In the end hand-written SQL queries are required again.
So let's make sure that we get more safety when writing SQL queries and utilize the power of typed programming languages like C#.
How does it work?
For the query itself to the database you need to use an provider - this library only helps you by creating SQL query strings. Recommendation for querying data: https://github.com/DapperLib/Dapper
- Use binary (.exe) of Asmerald.Generate to create classes from database
- Include Asmerald library in your project
Prerequisite
Asmerald builds upon an existing database scheme and makes the output available for databases providers to query from the database.
Performance impact
Function calls and string builder bring overhead to the table. If you are running queries in a hot path - check if it has a major impact on performance. Shouldn't because of JIT opitmisations but check either way.
Benchmark
Benchmarking a simple SQL query which benefits Asmerald
SELECT Id, Name, Form as 'test' FROM cards WHERE id > @idGr ORDER BY Id LIMIT @limit
and translated to code with Asmerald
dslCtxt
.Select(TblCards.Id(), TblCards.Name(), TblCards.Form().As("test"))
.From<TblCards>()
.Where(TblCards.Id().Greater(10))
.OrderBy(TblCards.Id())
.Limit(50)
.QueryBuilder
.BuildPreparedStatement();
Running those queries against a SQLite database in combination with Dapper returns following results:
Method | Mean | Error | StdDev | Ratio |
---|---|---|---|---|
DapperRaw | 179.4 us | 1.48 us | 1.38 us | 1.00 |
Asmerald | 191.4 us | 2.28 us | 2.02 us | 1.07 |
Asmerald adds about 7% overhead.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.1
- Microsoft.CodeAnalysis (>= 4.4.0)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
0.1.0 | 225 | 12/27/2022 |
Initial release