Object-Relational Mapping

If you think learning and writing SQL looks challenging, you’re not alone. Early full-stack developers did as well. In addition, there was the additional need to convert the responses from the relational database from text into objects the program could use. It shouldn’t be surprising that libraries quickly were adopted to manage this process. The most basic of these are drivers, simple programs which manage the connection between the database and the program using it, sending SQL queries to the database and parsing the results into data types native to the language (usually an array of arrays or an array of dictionaries - the outer array for the rows, and the inner array or dictionary for the column values within the row).

However, the use of drivers was soon supplanted by Object Relational Mapping (ORM), a software layer that sits between the database and the dynamic web server program. But unlike a driver, ORM libraries provide additional conversion logic. Most will convert function calls to SQL statements and execute them on the server, and all will convert the results of a query into an object in the language of the server.

Let’s look at a few ORMs in practice.

ActiveRecord

The ActiveRecord ORM is part of the Ruby on Rails framework. It takes advantage of the Ruby programming language’s dynamic nature to vastly simplify the code a developer must write to use a database. If we use our previous example of the database containing persons, addresses, and jobs tables, we would write classes to represent each table that inherit from the ActiveRecord base class:

# persons.rb
class Person < ApplicationRecord 
  has_many :addresses
  has_and_belongs_to_many :jobs
end

# addresses.rb 
class Address < ApplicationRecord 
  belongs_to :person 
end 

# jobs.rb 
class Job < ApplicationRecord 
    has_and_belongs_to :person
end

Then, to retrieve all people we would use a query method of the Job class::

allPeople = Person.all()

Or to retrieve all doctors, we would use a query method of the Job class:

allDoctors = Person.includes(:jobs).where(jobs: {name: "Doctor"})

While we haven’t been working in the Ruby language, I show this to help you understand just how far from SQL some ORMs go. For a Ruby programmer, this is far more comfortable syntax than SQL, much like LINQ’s method syntax is often more comfortable to C# programmers.

That said, there are always some queries - especially when we’re trying to squeeze out the most efficiency possible or working with a convoluted database - that are beyond the simple conversion abilities of an ORM to manage. So most ORMs offer a way to run a SQL query directly on the database as well.

Finally, you should understand that any ORM that provides a functional interface for constructing SQL queries is effectively a domain-specific language that duplicates the functionality of SQL. The SQL generated by these libraries can be far less efficient than a hand-written SQL query written by a knowledgeable programmer. While it can be more comfortable to program in, it may be more valuable to spend the effort developing a solid understanding of SQL and how relational databases work.

Entity Framework

The .NET platform has its own ORM similar to ActiveRecord, the Entity Framework. Like ActiveRecord, you can create the entire database by defining model objects, and then generating migration scripts that will create the database to match (this is called code first migrations in entity framework lingo). As with ActiveRecord, you must define the relationships between tables. To set up the objects (and tables) we saw in the ActiveRecord example, we would write:

public class Person 
{
  public int ID {get; set;}

  public string LastName {get; set;}

  public string FirstName {get; set;}

  public Address Address {get; set;}

  public ICollection<Job> Jobs {get; set;} 
}

public class Address 
{
  public int ID {get; set;}

  public string Line1 {get; set;}

  public string Line2 {get; set;}

  public string City {get; set;}

  public string State {get; set;}

  public int Zip {get; set;}

  public ICollection<People> People {get; set;}
}  

public class Job 
{
  public int ID {get; set;}

  public string Name {get; set;}

  public string ICollection<Person> People {get; set;}
}

Note that this is a lot more verbose; the Entity Framework approach doesn’t rely on the database telling it what the available, columns are, rather the model classes are used to determine what the database should be. We also have to set up the relationships, which are done using a class that extends DbContext, i.e.:

public class ExampleContext : DbContext 
{
  public DbSet<Person> Persons {get; set;}

  public DbSet<Address> Addresses {get; set;}

  public DbSet<Job> Jobs {get; set;}

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Person>().ToTable("People");
    modelBuilder.Entity<Address>().ToTable("Addresses");
    modelBuilder.Entity<Job>().ToTable("Jobs");
    modelBuilder.Entity<JobPerson>().ToTable("JobsPersons");
    modelBuilder.Entity<Person>()
      .HasOne(a => a.Address)
      .WithMany(b => b.Persons);
    modelBuilder.Entity<Person>().
      .HasMany(a => a.Jobs)
      .WithMany(b => b.Persons);
  }
}

Note the use of the .HasOne() and .HasMany() methods to set up the relationships between the tables. With this established, we can run queries using an instance of the ExampleContext:

var context = new ExampleContext();

// Get all people
var allPeople = context.Person.All();

// Get all doctors
var allDoctors = context.Person
  .Include(p => p.Jobs)
  .Where(p => p.Jobs.Includes(j => j.Name == "Doctor"));

Massive

For Node applications, I’ve really come to prefer MassiveJS, an ORM for the Postgres relational database. Like other ORMs, it converts query results into a JavaScript object or array of objects, with the column names as keys and values as values, converted to the appropriate type.

It does provide a slew of functions for generating queries programmatically, like other ORMs. But it also allows you to specify a folder in your project where you can place SQL files that are automatically converted to JavaScript functions that correspond to parameterized queries in the sql files. For example, to do our “All doctors” query, we would write a file scripts/allDoctors.sql:

SELECT * FROM persons INNER JOIN jobs_persons ON persons.id = jobs_persons.person_id INNER JOIN jobs ON jobs.id = jobs_persons.job.id WHERE jobs.name = "Doctor";

While this may seem less convenient than the ActiveRecord or Entity Framework approaches, it is immediately clear to a seasoned SQL programmer what the tables involved are and how they are related. Moreover, a good SQL programmer can often write a more efficient query than an ORM library can generate.