There’s an old saying that goes, ‘You never know until you try.’ Well, recently, I tried to develop an Object-Relational Mapping (ORM) using PowerShell and admittedly, the attempt fell short of expectations.

First, let’s clarify: an ORM is a technique that lets you interact with your database, like you would with SQL. In other words, it ‘maps’ your objects to tables in a database, and vice versa. It allows us to manipulate the data stored in the database using our preferred programming language.

The goal was to create an ORM that would enable me to make a strongly typed dataset – where I define what kind of data my variables can store. Furthermore, I also wanted to incorporate a couple of helpful functions, specifically CRUD (Create, Read, Update, Delete) actions. These are the four basic functions that models should be able to do in most systems.

The plan was simple on paper: use a master class (also known as a ‘Model’ in ORM terms) and plug in several child classes into it. Each child class would represent a table in the database. Think of it like a tree, with the master class being the trunk and the child classes the branches.

However, the attempt was not successful. It was a reminder of why there don’t seem to be any ORMs in PowerShell. The challenges encountered underscore why most developers prefer to use tools like Entity Framework for .NET applications.

One of my biggest roadblocks was the lack of class property updates to the database name. I created a class, assigned the database name, and later updated the class. While PowerShell added the updated properties of the class, it didn’t update the database name. This became a recurrent issue, raising questions around PowerShell’s suitability for these types of tasks.

There are technical terms available to describe this process, such as ‘instantiate’ (when we create an instance of a class), and ‘initialize’ (when we assign the initial state of an object). However, no matter the jargon, the issue remained – PowerShell, at least in my experience, was not the best choice for creating an ORM.

I encourage any tech enthusiast to look further into this topic – there’s always something new to be learned from the attempts of others, regardless of whether those attempts were successful or not. The challenges and hurdles we encounter along the way can often lead us to more insights than easier paths would.