Automatic ORM Designing

I believe that all programmers worth their salt, know of the famous quote that Larry Wall published, the three virtues of a programmer is:

1. Laziness
2. Impatience, &
3. Hubris

With this in mind, I would like to share with you a database class I have drafted over the recent years.  To give you some context, the reason I wrote this originally was because, a) I am too lazy to put the entire database schema in to a framework's model, and b) I was tasked to put together an enterprise system which many database systems with enormous models had to interact with each other.

First, what is an ORM?

Object-Relational Mapping; is a technique to wrap your database queries (CRUD) from your object-orientated frameworks. I'm going to go in to a fair bit of detail about the one I have written.

The Premise

Database schemas are written in to the tables, the same way as you store anything else.  Therefore every way a table is structured can be queried and extrapolated.  Typical ORM's require you to write out the whole schema in code - and I began to ask myself why.  Laravel Database Migrations is good, but I think you'll agree this concept is interesting, that it will automatically discover and validate tables, their fields along their datatypes, what other table/fields they link to and even if they are required or not.

Now, I've started this with small to medium sized projects, and I've lacked applying recent updates and to remove the way it was attached to every query, meaning it was always re-discovering on every query that was made, but with a few tips, I'll demonstrate here now, how we can do this process once, and cache the schema's information to a JSON definition file.

 The Basics

I'm going to assume a few things, one is that you have some type of dotenv setup to describe your database type, user, password for its connectivity.  I have my class set up so depending on the type specified in the configuration, I switch these sets of queries, but for the sake of this article, I'm going to base them on MySQL.  Here are some of the basic queries: So as you can see, the first snippet will get a list of all of the table names, the second can be use to get the field definitions for a given table, and finally the third shows how you can list the foreign fields for a given table:field.

Implementing as Middlewear

The way this should come together, is on the consturct of your database class,  it should see if the schema file (e.g. schema.json) exists or not.  If not, let's go in to the creation step:
This is a small example, but it should be fairly simple to see what is going on.  The three object functions being called assume you have used the three queries above to take in their respective arguments and to return the result as an multi-dimensional array.

Conslusion

So if you are like me who is simply too lazy, let the system make it for you.  If you ever change your schema, all you will need to do in order to update your framework is just delete the JSON schema file.  If there is enough demand, I can take you through using the schema it creates in to injesting it to run your queries and to build and validate your forms.

Cheers,
Jesse.