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. widely linked article 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 dbo.Categories (CategoryName, Description) (@CategoryName, @Description) SCOPE_IDENTITY() INSERT INTO VALUES SELECT on SQL Server, or SCOTT.DEPT (DNAME, LOC, DEPTNO) (:DNAME, :LOC, SCOTT.DEPT_SEQ.nextval); SCOTT.DEPT_SEQ.currval DUAL; INSERT INTO VALUES SELECT FROM 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. for the current datetime, or 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. GETDATE() NEWID() Take this object: { Id; Title; Description; DateTime LastUpdated; Film( title, description)
    {
        Title = title;
        Description = description;
    }
} public class Film // the primary key public int public string public string // DB column has default of the current date-time public public string string If you do an insert based on a Film object, e.g.: db. ); Insert( Film(“Star Wars”, “A ….”) new long time ago then you ideally don’t want to use the default value of the field when you first create an INSERT based on the object (since this will try to write a to the column, which may or may not be allowed, and will certainly prevent the column from getting its database default value). null LastUpdated NULL 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 . Massive Mighty 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: here here .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 object methods, in an intuitive (i.e. familiar, but easy!) interface, instead of just augmenting them with additional helper methods as Dapper does. System.Data 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. which I describe here 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 example above). .Insert(…) 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 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 in your client-side code, and before it’s ever written to the database. That is Mighty’s (and Massive’s) solution. INFORMATION_SCHEMA when you ask the micro-ORM for a new object of that type So in Mighty, if you do: films = MightyOrm<Film>(connectionString); film = films. ( {
    Title = ,
    Description = }); var new var New new "Star Wars" "A long time ago..." then the generated film object already contains correct values for any fields with database column default values, such as in our example, the row is ever created on the database! LastUpdated before As emphasizes, there is no one-size-fits-all solution to this (or any other) ORM problem. Sam Saffron’s article 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!

Oracle

Try Mighty, the new Massive-compatible micro-ORM for  .NET Core

Another Solution To Sam Saffron’s Annoying INSERT Problem

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

I Got 15K People to Practice SQL by Turning Them Into Detectives

The "Concrete Foundation" Fallacy: Why Your Quick-and-Dirty Database Schema is a Ticking Time Bomb

When The Oldest Programming Language Outsmarts The Newest AI

AI Cybersecurity: The Sword and Shield of the Next Cyber Frontier

Stop Hacking SQL: How to Build a Scalable Query Automation System

Spring Boot Audit Logs: Capture Every API Action Without Writing Boilerplate Code

I Got 15K People to Practice SQL by Turning Them Into Detectives

The "Concrete Foundation" Fallacy: Why Your Quick-and-Dirty Database Schema is a Ticking Time Bomb

When The Oldest Programming Language Outsmarts The Newest AI

AI Cybersecurity: The Sword and Shield of the Next Cyber Frontier

Stop Hacking SQL: How to Build a Scalable Query Automation System

Spring Boot Audit Logs: Capture Every API Action Without Writing Boilerplate Code

Light-Mode

Classic

Newspaper

Minty

Dark-Mode

Neon Noir

Minty

HN StartUps