Nanorm.Npgsql 0.1.2

Nanorm

A tiny data-access helper library for ADO.NET. Trimming and native AOT friendly.

It supports:

• PostgreSQL via Npgsql
• SQLite via Microsoft.Data.SQLite
• Any ADO.NET data provider via System.Data.Common

How to use

Nanorm supports .NET 6+, with special support for .NET 7+ thanks to static virtual members on interfaces and source generators.

Getting started

1. Install the Nanorm package that's suitable for the ADO.NET provider (i.e. database) you're using. Using the dotnet command line in the project directory:

   • PostgreSQL:

     dotnet add package Nanorm.Npgsql --prerelease
     

   • SQLite:

     dotnet add package Nanorm.Sqlite --prerelease
     

   • All other ADO.NET providers:

     dotnet add package Nanorm --prerelease
     

2. Create a class, record, or struct to map a database query result to. If you're using .NET 7 or .NET 8, you can make it partial and decorate it with the [DataRecordMapper] attribute to enable the source generator which will take care of implementing the IDataRecordMapper<T> interface for you:

   [DataRecordMapper]
   public partial class Todo
   {
       public int Id { get; set; }
   
       public required string Title { get; set; }
   
       public bool IsComplete { get; set; }
   }
   
   // Generated for you if .NET 7+, otherwise you'll need to write it yourself
   partial class Todo : IDataRecordMapper<Todo>
   {
       public static Todo Map(System.Data.IDataRecord dataRecord) =>
           new()
           {
               Id = dataRecord.GetInt32("Id"),
               Title = dataRecord.GetString("Title"),
               IsComplete = dataRecord.GetBoolean("IsComplete"),
           };
   }
   

3. Create an instance of the appropriate IDbConnection type and use one of the Nanorm extension methods to execute a query. The QueryAsync methods return IAsyncEnumerable so you can await foreach over the results or simply call ToListAsync() to asynchronously get a List<T> result:

   // Using Npgsql to query a local PostgreSQL instance
   var connectionString = "Server=localhost;Port=5432;Username=postgres;Database=postgres";
   await using var db = new NpgsqlDataSourceBuilder(connectionString).Build();
   
   var todos = db.QueryAsync<Todo>("SELECT * FROM Todos");
   var todosList = await todos.ToListAsync();
   
   if (todosList.Count == 0)
   {
       Console.WriteLine("There are currently no todos!");
   }
   else
   {
       Console.WriteLine($"Found {todosList.Count} todo(s):");
       foreach (var todo in todosList)
       {
           Console.WriteLine($"({todo.Id}) {todo.Title}");
       }
   }
   

Using parameters

Nanorm supports a few different ways to pass parameters to queries:

• Passing an interpolated string that is processed by Nanorm's custom string interpolation handler. This is the preferred approach. The query is automatically parameterized in an optimal way, i.e. no string concatenation, uses pooled allocations, etc.:

  var title = "Do the dishes";
  var result = db.QueryAsync<Todo>($"SELECT * FROM Todos WHERE Title = {title}")
  

• Passing instances of the ADO.NET provider's DbParameter implementation as parameters, e.g.:

  var title = "Do the dishes";
  var sql = "SELECT * FROM Todos WHERE Title = $1";
  var result = db.QueryAsync<Todo>(sql, new NpgsqlParameter { Value =  title }))
  

• Passing a callback that modifies the ADO.NET provider's parameter collection implementation, e.g.:

  var title = "Do the dishes";
  var sql = "SELECT * FROM Todos WHERE Title = $1";
  var result = db.QueryAsync<Todo>(sql, parameters => parameters.Add(title))
  

Extension methods

The following extension methods are provided to make it easier to work against your database:

Benchmarks

Nanorm is intended to be a very thin layer over ADO.NET with support for trimming and native AOT. Here's how it compares to raw ADO.NET and Dapper for a simple insert & return operation with regards to execution time and memory allocations: