A solution to the SQL vs. ORM dilemma

Schema representation​

In SQL, your schema defined by the set of all data definition language (DDL) commands (e.g. CREATE DATABASE users;) that have been executed against your database. Typically these DDL commands are represented as an ordered set of migration files that can be progressively applied as the schema evolves. Some SQL flavors automatically track the history of all DDL commands; all provide utilities for introspecting the current state of the schema.

CREATE TABLE persons (
  id uuid DEFAULT uuid_generate_v4() UNIQUE,
  name text NOT NULL
);
CREATE TABLE blog_posts (
  id uuid DEFAULT uuid_generate_v4() UNIQUE,
  title text NOT NULL,
  author_id uuid references persons(id)
);

ORM users argue this imperative approach to schema modeling isn’t developer-friendly, as there’s is no written representation of your schema. This makes it difficult to conceptualize the current schema state.

Instead, ORMs provide a way to write your schema declaratively, via class definitions, a data structure, a custom schema definition language, or some other mechanism. Vitally, this representation is usually object-oriented, meaning a “model” can contain explicit references to other models, such as author in the (pseudo-code) example below:

class Person {
  name: string
}

class BlogPost {
  title: string
  author: Person
}

An aside about ORMs

Many older critiques of SQL (including the Vietnam essay, which was written in 2006) assume the Active Record paradigm, in which SQL tables are mapped to corresponding classes. Instances of these classes are intended to correspond and synchronize directly with the underlying database row. The API looks something like this:

const user = new User('user_1234');
user.name = "Bobby Tables";
await user.save();

This introduces complications surrounding overfetching (Neward’s “partial object problem”) and object identity. However these issues are specific to strictly object-oriented languages, in which all objects must be an instance of a class. Neward addresses this:

Well, it’s fair to say JavaScript and Python are now widespread! This post is primarily written with modern slate of JavaScript and Python ORMs in mind. Broadly speaking, these libraries:

• follow the data mapper pattern, in that they return “plain” data structures instead of class instances;

• provide a more functional API;

• depend on object/dictionary literals extensively in their APIs for things like field selection;

await User.update('user_1234', {
  name: "Bobby Tables"
});

Migrations​

The declarative modeling approach begs the question: is the ORM intended as the single source of truth for schema information?

• If not, then you have the dual schema problem. You must keep your ORM definition in sync with the schema of the underlying database, which is presumably modified using another SQL migration tool. This violates the DRY principle and increases maintenence burden.

• If so, then the ORM must provide a migration mechanism: a way to linearize the evolution of the schema models into a series of imperative migration steps (usually DDL scripts). Technically, this also violates the DRY principle, since the same schema is represented in both a declarative and an imperative form (DDL), though this a bit of a gray area, since the DDL is typically auto-generated.

  SQL users contend that these auto-generated migration systems are error-prone, don’t properly handle complex changes such as renames, and rarely support data migrations in the scenarios where they are needed. It’s simpler and safer to hand-write SQL migration logic.

Query syntax​

ORMs simplify the experience of interacting with a database; they provide a stripped-down data model and CRUD API that’s comparatively easy to learn relative to SQL. SQL has a steep learning curve, for a number of reasons.

• It’s a large language with hundreds of keywords, grammar rules, and statement types.

• Due to the large API surface, there are many inconsistencies and edge cases (the treatment of null, for instance).

• Its clause ordering is unexpected, especially the fact that select precedes from.

• Broadly, SQL and the relational paradigm seem foreign to programmers who are accustomed to thinking about problems in an object-oriented way.

On the other hand, you only have to learn it once. SQL is a largely transferrable skill, since SQL is a universal query langauge; it even has an ISO standard.

By contrast, no two ORM APIs are alike. They provide non-native, language-specific ways to model your schema and write queries. You need to learn a new API whenever you switch to the new ORM-du-jour. Plus, as your application gets more complex, you’ll likely hit the limits of what your ORM can represent, in which case you’ll need to fall back to SQL anyway

Result structure​

All SQL queries return a list of scalar-valued tuples, even when JOINing and SELECTing from referenced tables.

SELECT name, posts.title AS post_title
FROM
  users
  LEFT JOIN
  posts ON posts.author_id = users.id

name     | post_title
-------------------------------------------------
"Anakin" | "Why I don't like sand"
"Anakin" | "One weird trick to surviving lava"
"Anakin" | "I've got a bad feeling about this"

To make the results more easily consumed by the client, it’s common to reformat the results into a structured object/dictionary, which introduces complexity into application logic.

By contrast, ORMs provide an object-oriented API for nested fetching that returns a structured object that is more immediately useful than SQL’s “array of arrays”.

{
  "name": "Anakin",
  "posts": [
    {"title": "Why I don't like sand"},
    {"title": "One weird trick to surviving lava"},
    {"title": "I've got a bad feeling about this"},
  ]
}

Language integration​

ORMs provide a code-first API to express queries, whereas raw SQL queries are often expressed as plain strings. These query strings are often more concise than the equivalent ORM operation and allow queries to be represented in a language-agnostic way.

On the other hand, ORM APIs can benefit from programming language’s functionality, syntax highlighting, autocompletion, auto-formatting, and other tooling that is increasingly common in modern dev environments.

But perhaps the most important consideration is the ability of ORMs to provide fully-typed query results inside statically typed languages like TypeScript. Without an ORM, users must write both the SQL queries and its expected type signature, and manually keep them in sync. This violates the DRY principle and increases maintenance burden on the developer.

Performance​

Since ORMs usually execute SQL queries under the hood, they can only hope to match the performance of an equivalent optimized SQL query; in practice, though, ORMs are often much slower.

Nested fetch operations are typically split into a set of simpler, serially-executed SQL queries. This requires several round-trip requests to the database; depending on the server-database latency characteristics, this can have disastrous performance ramifications.

On the other hand, a naive approach to writing highly connected (JOIN-heavy) queries in SQL will result in a cartesian explosion in the result set (AKA a “join bomb”) that can severely hurt performance.

SELECT name, f.username, p.title, c.content
FROM
  users u
  LEFT JOIN follows ON follows.target_id = u.id
  LEFT JOIN users f ON follows.source_id = f.id
  LEFT JOIN posts p ON posts.author_id = u.id
  LEFT JOIN comments c ON comments.post_id = p.id

-- 10 gazillion results returned

Power​

ORMs provide a limited set of CRUD functionality: simple queries, nested queries, the ability to filter by some limited set of operators, nested mutations, inserts, updates, and deletes. Advanced options may support upserts, basic aggregations, and grouping.

SQL, by contrast, is a full-fledged query language that supports a full library of functions and operators, computed properties, subqueries, window functions, advanced grouping and analytical queries, type conversion operations, set operations like union and distinct, common table expressions, recursive queries…the list goes on.

And that’s just the query language; SQL schemas are also richer and more sophisticated. They have a rich typesystem consisting of string, boolean, numeric, geometric, monetary, temporal, and geographical datatypes, plus computed properties, stored procedures, database views, triggers, and more.

Schema representation​

EdgeDB schemas are expressed in .esdl files using our declarative, object-oriented schema declaration language.

# default.esdl

type Movie {
  required property title -> str;
  multi link actors -> Person;
}

type Person {
  required property name -> str;
}

EdgeDB has a robust type system that’s most comprehensive that most ORMs, but without the bloat that’s common among RDBMSs.

str
bool
int16
int32
int64
float32
float64
uuid
bigint
decimal
sequence
datetime
duration
cal::local_datetime
cal::local_date
cal::local_time
json

# plus enums, arrays, and tuples

These primitive data types form the building blocks for declaring object types, which contain properties and links to other object types. The “link” concept allows object to directly reference other objects, like “associations” or “relations” in ORM parlance.

Computed properties, indexes, constraints, default values, and custom scalar types are fully supported. Under the hood, everything is stored in a fully normalized way.

Migrations​

Migrations are created interactively via the edgedb command-line tool. Your current schema files are compared against the current database schema and outputs edgeql files that contain DDL commands.

$ 

edgedb migration create

Did you create object type 'Movie'? [y/n]
> y
Did you create object type 'Person'? [y/n]
> y
Created ./dbschema/migrations/00001.edgeql.

The migration planning logic is built into the database itself, not the CLI or a third-party tool. Similarly, migration history is automatically tracked and fully introspectable. Migrations are represented as .edgeql files containing DDL commands.

CREATE MIGRATION m1ug4vx3zouenfd3vdp3uxu2j62ng74n5np7pk7orsvypeykuxpowq
  ONTO initial
{
  CREATE TYPE default::Person {
    CREATE REQUIRED PROPERTY name -> std::str;
  };
  CREATE TYPE default::Movie {
    CREATE MULTI LINK actors -> default::Person;
    CREATE REQUIRED PROPERTY title -> std::str;
  };
};

Users who prefer imperative schema modeling can write migration scripts directly. Those who prefer declarative modeling can use SDL. Or mix-and-match; it’s entirely possible to add custom DDL migrations alongside the auto-generated ones.

Query syntax​

EdgeQL is designed to solve some of SQL’s more unintuitive design aspects. For starters, its object-oriented nature allows for JOIN-less deep fetching with a new syntactic structure: the shape.

select Movie {
  title,
  actors: {
    name
  },
  reviews: {
    rating,
    author: {
      name
    }
  }
} filter .title = "Dune"

Eliminating JOINs alone is a big step towards a query language that is more intuitive for developers who are primarily familiar with object-based languages (which is most of them).

Within the scope of the select statement, you can refer to links and properties with “leading dot notation”, such as .title in the query above. This is another novel syntactic structure, known as a path. These are a powerful way to reference linked objects in a concise way.

select Movie {
  title,
  actors: { name },
  num_actors := count(.actors), # computed property
  reviewers := .reviews.author.name, # another computed
} filter "Zendaya" in .actors.name

Another key characteristic of EdgeQL is its composability; you can cleanly nest EdgeQL queries inside each other.

insert Movie {
  title := "Spider-Man: No Way Home",
  director := (insert Person { name := "Jon Watts" }),
  actors := (
    select Person
    filter .name in {"Zendaya", "Tom Holland"}
  )
}

This degree of composability isn’t possible in SQL due to it’s strict distinction between table expressions and scalar expressions. EdgeQL eliminates this distinction, opting instead for a more elegant set-theoretic foundation.

Result structure​

Like ORMs, EdgeQL return a structured result that matches the visual structure of the query.

select Movie {
  title,
  actors: {
    name
  }
}

{
  "title": "Dune",
  "actors": [
    {name: "Timothee Chalamet"},
    {name: "Jason Momoa"},
    {name: "Rebecca Ferguson"}
  ]
}

Language integration​

EdgeQL queries can be written as strings, similarly to SQL.

import {createClient} from "edgedb";

const client = createClient();
const result = await client.query(`select Person { name }`);

We’ve also built a query builder for TypeScript that can represent any EdgeQL query and automatically infers the result type. The query builder is a schema-aware client for writing queries that is generated by introspecting your schema.

import {createClient} from "edgedb";
import e from "../dbschema/edgeql-js"; // <- the query builder

const client = createClient();

const myQuery = e.select(e.Movie, movie => ({
  id: true,
  title: true,
  actors: { name: true},
  filter: e.op('Zendaya', 'in', movie.actors.name)
}))

const result = await myQuery.run(client);
// { title: string; actors: { name: string }[] }[]

Performance​

All EdgeQL queries are compiled into a single, optimized PostgreSQL query that can be executed in a single round-trip, solving the ORM latency problem.

Since EdgeDB leverages Postgres’s query engine, the compiled queries can leverage Postgres’s legendary performance and feature set. For highly-connected JOIN-heavy queries, EdgeDB defuses the “join bomb” problem by performing all JOINs inside subqueries and aggregating the results, instead of naively JOINing at the top level. This solution isn’t possible in all SQL implementations.

Power​

EdgeQL’s composable nature, set-theoretic basis, robust system of types and casting, expressive shape and path syntax, JSON support, and comprehensive standard library of functions and operators makes it both powerful and intuitive.

select Movie {
  title,
  actors: { name },
  avg_rating := math::mean(.reviews.rating)
}

Because EdgeQL and EdgeDB’s schema definition language are closely married, your schema types can include computed fields, indexes, and constraints that correspond to complex EdgeQL expressions.

# default.esdl

type Movie {
  required property title -> str;
  multi link actors -> Person;
  num_actors := count(.actors); # computed
}

type Person {
  required property name -> str {
    constraint min_length(0);
  };
  multi link acted_in := .<actors[is Movie]; # backlink
  index on (str_trim(.title));
}

Abstract type mixins allows for the modeling sophisicated data domains without redundancy.

abstract type Item {
  required property name -> str;
  required property weight -> float64;
}

type Weapon extending Item {
  required property range -> int64;
}

type Shield extending Item {
  required property defense -> int64;
}

type Player {
  required property username -> str { constraint exclusive; }
  multi link inventory -> Item;
}

And polymorphic queries allow for painless retrieval:

select Player {
  name,
  inventory: {
    name,
    [is Weapon].range,
    [is Shield].defense,
  }
}
filter .username = "Zezima"

EdgeQL is a full-fledged query language that is rapidly approaching feature parity with SQL. The last major missing SQL feature is group by, which just landed in the 2.0 nightlies. Other features on the roadmap include access control (also coming in 2.0), database views, triggers, window functions, and GIS extensions; view the full roadmap for details.