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.

  • MediaWiki doesn't use an ORM so far as I can see.
    – TZHX
    Commented Aug 3, 2016 at 7:30
  • "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. " -- really? I see quite a few, and those are just in the first 2 pages of the first google query I thought of.
    – Jules
    Commented Aug 4, 2016 at 6:49
  • I can see that three people have voted to close this question because it's too broad. But, that is the reason I posted on programmers.stackexhange and not stack overflow. Would appreciate if you could provide some pointers about how to narrow it down. Commented Aug 4, 2016 at 17:10
  • @Jules checking out your links. beardedhacker.com/blog/2014/02/01/… has absolutely nothing to with my question. Commented Aug 4, 2016 at 17:16
  • @jules please read the second paragraph in the other link you shared blog.oio.de/2016/05/12/… it's actually very sensible advise of not using ORMs in few edge cases that ORMs are not good at handling. There's also a list of cases at the bottom of the post (and i agree with all, but one). Commented Aug 4, 2016 at 17:22

4 Answers 4


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.

  • 2
    Pardon my ignorance, but what's a DTO? Commented Aug 3, 2016 at 4:28
  • 2
    Data Transfer Object. Basically, a class with public fields. Commented Aug 3, 2016 at 4:29
  • #5 in your answer, how different is it from Customer.find(customerID) and letting active record handle all the boilerplate? What's the advantage of writing such boilerplate SQL queries? Commented Aug 3, 2016 at 4:30
  • An actual SQL query would only include the SELECTed fields that are required in your DTO. Commented Aug 3, 2016 at 4:31
  • Is stack overflow code open source? Also, don't you need some sort of mapping layer to prevent CRUD boilerplate? Isn't that essentially what an ORM gives you? How is it a problem then? Commented Aug 3, 2016 at 4:33

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.

  • 1
    Your last comment about the ActiveRecord pattern is what is puzzling me. My exposure to ORMs is only via Rails/ActiveRecord and I can't imagine WHY I would want to write all the boilerplate that it takes care of. And what exactly is so bad about that pattern? Sure, it can't handle all the edge cases that raw SQL can, but one can always drop down to raw SQL when required. We do that for running reports all the time. Commented Aug 4, 2016 at 17:08
  • The main reason for not using an ORM is so that you can optimize your queries for specific situations. Implementing the Active Record pattern without an ORM is quite easy: you just add save and find methods to your class, and you can then have different find methods that load different related classes via joins, for example.
    – Jules
    Commented Aug 5, 2016 at 8:52

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
    . . .

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

    . . .
  1. 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)

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.

  1. 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.

  1. 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
        |> 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/@/)

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

  • 1
    Your answer would be better if you added more than just links. Perhaps some small summary of the content provided in the pages linked.
    – Eric King
    Commented Sep 29, 2016 at 1:10
  • @EricKing Updated. Commented Sep 29, 2016 at 9:34

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

FROM      gameTeams AS gameTeam
LEFT JOIN poolTeams AS poolTeam ON poolTeam.poolTeamId = gameTeam.poolTeamId
WHERE gameTeam.gameId IN (?)
ORDER BY gameNumber,slot
    $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

  • Thanks for your reply @Cerad. And thank you for taking the time to actually give code samples. When you say 'ar', do you mean Active Record? Which ORM were you using earlier? Commented Aug 4, 2016 at 17:04
  • AR = aggregate root. Sorry for the confusion. I had been using then Doctrine 2 ORM (docs.doctrine-project.org/projects/doctrine-orm/en/latest) which is based on the data mapper pattern. In the land of PHP, Doctrine is probably the most widely used orm out there.
    – Cerad
    Commented Aug 4, 2016 at 18:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.