5 Simple Rules for CRUD Operations

CRUD is probably the first word your CS teacher taught you, and you might be thinking there's no way anyone could teach you more about it, right? Well, hopefully I will prove you wrong because once you dive as deep into CRUD as I have, interesting ideas manifest themselves.

For those who don't already know this from before, CRUD is one of those acronyms that we software developers love, and it means Create, Read, Update, and Delete. It is the 4 axioms around which most of our data evolves. If you have all 4 CRUD operations towards your data, your data has an "orthogonal API."

Your relational database evolves around 4 basic SQL statements: Insert, Select, Update, and Delete. It doesn't require a rocket scientist to understand that these 4 words are the SQL equivalent of the CRUD acronym. The SQL version of CRUD hence becomes ISUD. HTTP has the same ideas, but yet again under a different name. HTTP REST refers to these same operations as POST, GET, PUT, and DELETE.

You might also want to read:  CRUD Operation With ASP.NET Core MVC Using ADO.NET and Visual Studio 2017

So the HTTP equivalent of CRUD becomes PGPD. It's important for you to understand that POST is for creating new data and PUT is for updating existing data at this point. The reason is that according to the HTTP standard, if PUT has been implemented correctly, you should be able to transmit the same payload to your endpoint without resulting in changes to your server's internal state. Therefore, updating records multiple times with the same payload does not violate how the PUT verb was intended to be used, since no changes are applied, because the same data for your change is simply transferred multiple times, resulting in no actual change of your underlying data.

Secondly, it's important to visualize the primary keys in your database. All sane databases should have a way to uniquely address one single record in their tables. If one of your tables doesn't have this trait, it's theoretically impossible to reference a single record in it, hence you can insert records and you can read them, but without a primary key, you can never delete a single record, and you can never update a single record (safely!). This is because if there is no way to uniquely identify a single record in your table, any update or delete statement, might in theory reference multiple records.

When I created Magic, which you can see in the video below, I realized that it's all about primary keys — at least from a philosophical point of view. Watch the video first, and then I'll explain what I mean afterwards.

The only reason I could create Magic was because I was able to extract a common "design pattern" in regards to how I treated my data. This pattern is as follows:

1. All read endpoints return all columns from your table.
2. All create endpoints do not take any primary key fields, as long as the primary key has a default value. Only if there is no default value for your primary key will create require the client to supply it.
3. All update endpoints require everything that the create endpoints require, but in addition, they also require all primary key fields to uniquely identify which record to update.
4. All delete endpoints require only the primary key fields and nothing else.
5. Create always returns the primary key(s) for the records it creates unless an explicit primary key was supplied by the client. And update and delete always returns the number of records affected.

Once applied, the above 5 simple rules allowed me to, in a generic fashion, read metadata from my database tables, at which point I'd end up knowing my table's column names, which columns are primary keys,  which columns have default values, etc. Once I knew this, I could scaffold code 100% generically, which would first wrap my entire database into HTTP REST endpoints, for then to create an Angular frontend client, consuming my backend, resulting in having my computer automagically create my entire app.

At that point, all I needed to do was to allow for configuring my endpoints by allowing the user to select validators, which roles the client needs to exist within to be allowed to execute the endpoint, etc, etc, etc. In a way, what I did was create my own internal "standard" for how to allow the ideas of CRUD to penetrate securely all the way from my relational database through my .Net Core backend, over HTTP as JSON, and into my frontend Angular data grids. This results in something resembling the following, automatically created for me by my computer.

Notice how the primary key for my above Customer record is not possible to edit. In fact, in my Datagrid, I don't even show it by default, but it's there behind the scenes since it's a prerequisite for being able to update my records.

Standardization

I often have junior developers asking me what the difference between a junior and a senior developer is. Often, the answer to this is surprising for the junior, since the junior often knows 15 different ways to implement QuickSort in 10 different programming languages, while the senior has long since forgotten this due to not needing it for 25+ years or something. So I will teach you the difference, and it is "the ability to extract common patterns, standardize on them, and think generically."

In other words, the junior will happily slash away at any problem they are given and try to solve it as fast as possible, while the senior will spend two days thinking about his problem's common traits before even implementing a single line of code. Once the senior is done of course, not only has he solved the problem at hand, but he has also solved every possible permutation of future similar problems. So while the junior is adding to the cognitive and technical debt of his employer, by producing hundreds of thousands of lines of code that needs to be maintained, the senior developer does not do this because every time he solves one problem, he solves a thousand similar problems.

If you want to become a senior developer, stop solving problems immediately! Find the common traits the current set of problems you're working with has with other problems, and extract the commonalities. Then, solve that problem. This will oftentimes result in only 5-10 lines of code you need to maintain in the future instead of tens of thousands of lines of code you need to maintain in the future to keep your "previous solutions running."

I have created software since I was 8 years old, and I am 45 years of age today, which implies I have created software for 37 years. And yes, I still love it! If you want to see how a "super-senior" software developer solves problems, you can start out by downloading Magic.

Further Reading

Automate CRUD Operations and Focus on What Matters

Web Dev Roundup: What the CRUD!