Examples of mid/large-scale web-apps built without an ORM?

Question

I've been reading a lot of hate spewed on ORMs, and am genuinely interested in finding out if there is a better way of doing things.

Unfortunately, while there are tons of books, blog posts, and articles about modeling and building apps using various ORMs, none exist for the non-ORM approach.

Are there any mid/large sized open source webapps built without an ORM? Nothing beats reading actual source code.

Alternatively, here's what I'm trying to find out:

1. just because you aren't using an ORM, various associations between tables don't disappear. How do you model them in your app?
2. For simple stuff like loading a row given the PK, how much boilerplate does one have to write? Are there any libraries that ease this pain?
3. For simple stuff like loading 1:many associations, how much boilerplate is required?
4. What does the code look like, when you're exposing a JSON API in your web app? The JSON API will inherently have associations in it (I.e. User JSON will have Posts inside it, which will have Comments inside it). How is this materially different from what an ORM does?
5. What does the low-level API, which implements various business logic, look like? If not objects, what arguments does this API take? What does it return?

My exposure to ORMs is only via Rails/ActiveRecord, and I can't visualise myself writing a DB backed application without duplicating all the boilerplate that Rails takes care of, for me. It could be that hibernate or nhibernate are different beasts and active record is just fine. Which would actually make sense and lay this matter to rest. At least for me.

Answer 1

Stack Overflow uses a micro-orm. Unlike Hibernate, it's just a thin veneer around SQL queries.

Answers to your questions:

1. By writing a SQL query.

2. It can vary from one line of code to about 40. Having ready-made DTO's does help.

3. The DTO looks the same. You might run 2 queries instead of one, and lazy-load the second one.

4. In Javascript, JSON translates directly to Javascript objects. For other languages, you can use something like Newtonsoft.Json to translate the JSON to objects in your language of choice.

5. Here is some example code:

   var result = database.ExecuteSqlQuery<Customer>(
       "SELECT * FROM Customer WHERE CustomerID = {0}", customerID).FirstOrDefault();
   

Where ExecuteSqlQuery returns a List of Customer objects. Customer is a class containing public members corresponding to some or all of your columns in your query.

Dapper is capable of running queries just like this one. It will return a collection of objects of a specific type, or a collection of dynamic objects.

Answer 2

There are various design patterns that are useful for applications that are not using ORM. I'd suggest reading through Martin Fowler's book, Patterns of Enterprise Application Architecture, which includes quite a few useful ones, but here are some short summaries:

• Table Data Gateway - create a class that has operations for manipulating a single table
• Row Data Gateway - create a class that represents a single row in a database table and provides operations that interact with your domain model objects
• Query Object - a class that represents an SQL query, with properties for adding restrictions to the query, selecting columns, etc.
• Transaction Script - an object that encapsulates all the work done with the database during a single operation

It's also quite possible to implement the Active Record pattern that you should be familiar with without using an ORM; you just have to implement each class's find and save methods by hand. But that probably defeats the purpose of not using an ORM, which is generally to allow you to be more flexible in the way you interact with the database.

Answer 3

You should check out Elixir's Ecto, which is a domain specific language (DSL) for interacting with (mostly relational) databases and writing queries. It goes well with the Phoenix framework, which implements the server-side MVC pattern.

To answer your questions:

1. & 3. Modeling relationships is done in the model. Elixir being a functional language, has no classes. Instead you declare table schemas using the Ecto DSL, which essentially extends the Elixir language to provide short and expressive database declarations. For every table in your database you have a model module and a corresponding migration file ( for running a db migration once in each environment(dev, test, prod etc.))

For example a User model can look like the following

    defmodule MyApp.User do
    . . .
      schema "users" do
        field :name, :string
        field :email, :string
        field :bio, :string
        field :number_of_pets, :integer

        has_many :videos, MyApp.Video
        timestamps()
      end
    . . .
    end

It references a Video module which can look like:

    defmodule MyApp.Video do
    . . .
      schema "videos" do
        field :name, :string
        field :approved_at, Ecto.DateTime
        field :description, :string
        field :likes, :integer
        field :views, :integer
        belongs_to :user, MyApp.User

        timestamps()
      end
    . . .
    end

2. This can be done by adding something like the following to the UserController module:

    def show(conn, %{"id" => id}) do
      user = Repo.get!(User, id)   
      render(conn, "show.json", user: user)
    end

In the above, MyApp.User has been aliased as User. The Repo or repository comes with Ecto and along with a database adapter (like postgrex for Postgres) it maps onto the the underlying data store.

4. Consider the following snippets:

    # Create a query
    query = from p in Post,
              join: c in Comment, where: c.post_id == p.id

    # Extend the query
    query = from [p, c] in query,
              select: {p.title, c.body}

Essentially, you use the above to query your database, handing off the required results to a render function that would render JSON.

5. Elixir has Changesets that are essentially a series (or a pipeline) of transformations that incoming data (the struct) undergoes before it is ready to be committed to the database. While handling the incoming struct, Changesets allow for filtering, casting, validation and definition of constraints. For example, the following is what a changeset function can look like (it is part of the MyApp.User module (the model)) :

    def changeset(struct, params \\ %{}) do
        struct
        |> cast(params, [:name, :email, :bio, :number_of_pets])
        |> validate_required([:name, :email, :bio, :number_of_pets])
        |> validate_length(:bio, min: 2)
        |> validate_length(:bio, max: 140)
        |> validate_format(:email, ~r/@/)
    end

In case you have check constraints, they are also part of the above pipeline and are also to be found in a migration file.

Answer 4

I have a small soccer tournament application: http://ng2016.zayso.org/schedule/game

Within the schedule domain, Game is an ar(aggregate root) with associations to GameTeams and GameOfficials.

The game finder looks like:

public function findGames(array $criteria)
{
    $conn = $this->gameConn;

    // Find unique game ids (might require several queries)
    $gameIds = $this->findGameIds($conn,$criteria);

    // Load the games
    $games = $this->findGamesForIds($conn,$gameIds);

    // Load the teams
    $wantTeams = isset($criteria['wantTeams']) ? $criteria['wantTeams'] : true;
    if ($wantTeams) {
        $games = $this->joinTeamsToGames($conn, $games);
    }

    // Load the officials
    $wantOfficials = isset($criteria['wantOfficials']) ? $criteria['wantOfficials'] : false;
    if ($wantOfficials) {
        $games = $this->joinOfficialsToGames($conn,$games);
    }

    // Convert to objects
    $gameObjects = [];
    foreach($games as $game) {
        $gameObjects[] = Game::createFromArray($game);
    }
    // Done
    return $gameObjects;
}

So we find a bunch of games using key/value arrays, optionally add teams and officials then convert to a Game object.

A simple query loads the games from the database:

private function findGamesForIds(Connection $conn, $gameIds)
{
    if (!count($gameIds)) {
        return [];
    }
    $sql = 'SELECT * FROM games WHERE gameId IN (?) ORDER BY gameNumber';
    $stmt = $conn->executeQuery($sql,[$gameIds],[Connection::PARAM_STR_ARRAY]);
    $games = [];
    while($game = $stmt->fetch()) {
        $game['teams']     = [];
        $game['officials'] = [];
        $game['gameNumber'] = (integer)$game['gameNumber'];
        $games[$game['gameId']] = $game;
    }
    return $games;
}

Easy enough. Note that I don't try transforming table column names into object property names. I just use whatever is in the table.

Adding the teams is a bit more work:

private function joinTeamsToGames(Connection $conn, array $games)
{
    if (!count($games)) {
        return [];
    }
    $sql = <<<EOD
SELECT 
  gameTeam.gameTeamId,gameTeam.gameId,gameTeam.gameNumber,
  gameTeam.slot,gameTeam.misconduct,
  poolTeam.regTeamId,poolTeam.regTeamName,
  poolTeam.division,poolTeam.poolTeamKey

FROM      gameTeams AS gameTeam
LEFT JOIN poolTeams AS poolTeam ON poolTeam.poolTeamId = gameTeam.poolTeamId
WHERE gameTeam.gameId IN (?)
ORDER BY gameNumber,slot
EOD;
    $stmt = $conn->executeQuery($sql,[array_keys($games)],[Connection::PARAM_STR_ARRAY]);
    while($gameTeam = $stmt->fetch()) {
        $gameId = $gameTeam['gameId'];
        $games[$gameId]['teams'][$gameTeam['slot']] = $gameTeam;
    }
    return $games;
}

Notice that in this context, a GameTeam is actually stored in two different tables. This is the sort of thing that the ORM I used previously had problems with. I could easily retrieve a GameTeam with a related PoolTeam but there was no straight forward way to merge the two together.

I should also point out that GameTeam.misconduct is a serialized value object.

I have several other ar's with similar finders. Overall I think this approach is easier to maintain then my previous approach based on ORM. The ORM did some of the boiler plate query work for you but I found that it was difficult to customize.

Complete source code (warts and all) here: https://github.com/cerad/ng2016