Another Solution To Sam Saffron’s Annoying INSERT Problem

by Mike BeatonSeptember 8th, 2019

Too Long; Didn't Read

Author of Mighty micro-ORM for.NET Core, SQL & web API dev, some time computer games 3D gfx lead. He explains how Mighty, following Rob Conery’s Massive, implements a great answer to ‘that annoying INSERT problem’ that Sam Saffron never mentioned. Massive has all the same features as Massive, but with new features, especially including full stored procedure support and. possible new features. I’ve finished and now released Mighty, and released Mighty.

Company Mentioned

featured image - Another Solution To Sam Saffron’s Annoying INSERT Problem

I explain how Mighty, following Rob Conery’s Massive, implements a great answer to “that annoying INSERT problem” that Sam Saffron never mentioned.

In a , from a few years ago now, Sam Saffron asks what to do about ‘that annoying INSERT problem’ in micro-ORMs. His question still applies. When you take a user object and use it to create an INSERT statement for a database, you have to deal with two problems: auto-incrementing primary keys, and database column defaults. All databases have some method specifically designed to get back the latest primary key value used in an INSERT, such as

INSERT INTO dbo.Categories (CategoryName, Description)
VALUES (@CategoryName, @Description)

SELECT SCOPE_IDENTITY()

on SQL Server, or

INSERT INTO SCOTT.DEPT (DNAME, LOC, DEPTNO)
VALUES (:DNAME, :LOC, SCOTT.DEPT_SEQ.nextval);

SELECT SCOTT.DEPT_SEQ.currval FROM DUAL;

on Oracle.

Harder, and the problem Sam Saffron’s article was particularly concerned with, are database column default values. These can be either fixed values, or database specific functions (e.g.

GETDATE()

for the current datetime, or

NEWID()

for a new UUID value in SQL Server, or similar equivalents in other databases). They are specified as part of the database table definition, and they result in the (function’s) value being automatically inserted into that column, if no user value is specified at the time of a database INSERT into the table. A micro-ORM working with object fields which map to columns with database defaults has to — or, at least, ideally should! — do something about it.

Take this object:

public class Film
{
    // the primary key
    public int Id;
    public string Title;
    public string Description;
    // DB column has default of the current date-time
    public DateTime LastUpdated;
 
    public Film(string title, string description)
    {
        Title = title;
        Description = description;
    }
}

If you do an insert based on a Film object, e.g.:

db.Insert(new Film(“Star Wars”, “A long time ago….”));

then you ideally don’t want to use the default

null

value of the

LastUpdated

field when you first create an INSERT based on the object (since this will try to write a

NULL

to the column, which may or may not be allowed, and will certainly prevent the column from getting its database default value).

Even when you have dealt with that (perhaps by allowing the user to put an attribute on such fields?), then you still need to do something about reading the database-generated value back (just as the micro-ORM will already have done something about reading back and filling in the primary key value on the object, once it has been inserted). If you don’t do that, then the client-side object won’t truly represent any value which the related database row has ever had, and it’s likely to overwrite the database generated column value once again, as soon as the user does an UPDATE based on the object. Sam Saffron’s article was particularly interesting to me because, at the time I read it, I had been working on the codebase of , and later . Mighty has all the same features as Massive (i.e. the lovely, productive UI shown, albeit very briefly, in the examples in this article; and in more detail and ), but now with a ton of essential new stuff, including:

.NET Core support
Strongly typed as well as dynamic object results
Named and directional parameters, as well as Massive’s default auto-numbered parameters
Multiple result sets
Cursor parameter support

But why Massive or Mighty, and not Dapper? For me, because I especially liked — and wanted to be able to keep using — the way that Massive fully wraps all the

System.Data

object methods, in an intuitive (i.e. familiar, but easy!) interface, instead of just augmenting them with additional helper methods as Dapper does.

For various reasons , but mainly because Massive isn’t really being actively maintained any more, and because I still love it and wanted to be able to carry on using it (but with new features, especially including full stored procedure support and .NET Core) I’ve finished and now released Mighty.

However when I read Sam Saffron’s article it didn’t seem to describe a problem that I’d noticed Massive actually having! Admittedly, I’d started out mainly using (and working on) the data access wrapper parts of Massive and Mighty (such as ‘hydrating’ objects from SQL result sets, and calling stored procedures with named and directional parameters). Still, I definitely wanted to make Mighty fully compatible with Massive, so I definitely had to make sure that I had understood and correctly implemented all the more ORM-ish bits of the micro-ORM, including the object-based INSERTs, UPDATESs and DELETEs (such as the quick

.Insert(…)

example above).

As I worked on that part of Mighty, I realised that Rob Conery’s solution to Sam Saffron’s problem isn’t among the options which Sam’s article lists!

Conery’s solution is to include some highly cross-database-compatible code to read table defaults from the ANSI standard

INFORMATION_SCHEMA

table (read only once per table, per application run, if and when first needed for that table type). If any column defaults are found, these can then be applied when you ask the micro-ORM for a new object of that type in your client-side code, and before it’s ever written to the database. That is Mighty’s (and Massive’s) solution.

So in Mighty, if you do:

var films = new MightyOrm<Film>(connectionString);
var film = films.New(new {
    Title = "Star Wars",
    Description = "A long time ago..."
});

then the generated film object already contains correct values for any fields with database column default values, such as

LastUpdated

in our example, before the row is ever created on the database!

As emphasizes, there is no one-size-fits-all solution to this (or any other) ORM problem. A big ‘pro’ of Rob Conery’s solution is that it’s automatic (e.g. no unnecessary class decorations). In general, if micro-ORMs can make your life easier by doing what you need without you having to specify it, then so much the better! On the ‘con’ side, it means the micro-ORM needs to be able to parse and understand the default value — including some database specific function names — though Massive and Mighty are both pretty good at it! Other possible disadvantages, for some applications, include the fact that for a date-time field, the value will get set from the client-side time, not the server-side time, and it will get set to the time when the object was first created on the client, not when it was first inserted into the database. Both of which will be absolutely fine, for many — but not all — applications. So do note that, if you need to, you can certainly code around this default behaviour in Mighty. For instance, `.New` takes an additional optional parameter (the initialiser parameter used above is optional too); a boolean which, if false, tells Mighty not to look up or apply any database column defaults. Additionally Mighty provides attributes to mark columns for database read or write only. So you can disable the default by behaviour using those two features if you ever need to (or in some other possible ways, including inserting from a dynamic or an anonymous object, so that your insert object only contains the fields which you actually want to write), and then it’d up to you to read the column values back from the database yourself, when you know that you need them. I do think this is a particularly neat solution to Sam Saffron’s problem. In fact, I thought I’d write this article because it took me a while to understand how Rob Conery had solved Sam Saffron’s problem… by choosing none of his possible answers! Of course, Mighty still works this way, because I’ve found that Rob Conery’s approach to how a micro-ORM should work is very tasteful, by which I mean that all the decisions made go together well and just work… they basically just do what you need, before you even knew that you needed it!

L O A D I N G
. . . comments & more!