The problem with raw SQL

The problem with raw SQL

The perpetual debate…. which method/tech is better, and in the world of web development and databases there are basically 3 groups of the developers: Developers who swear by raw SQL only, developers who prefer abstraction like Query builders and ORMs, and developers who understand the benefits of both raw SQL and abstraction.

In this post I’ll be looking at Raw SQL and Query builders only; as for ORMs, I think they are the bane of many web applications, too much “magic”, too slow, and not suitable for complex queries, unless you are building a prototype/startup and/or anticipate low volumes of data.

IMHO both Query builders and raw SQL have merits, though I find Query builders to be far more beneficial and elegant, because…

  1. Raw SQL does not scale well.
  2. Raw SQL is anti-DRY.
  3. Raw SQL composition is clumsy (in web development context).

You might look at the below code examples and think, not a big deal – until your web application evolves to 10’s-100th’s models and requires database changes, application refactoring, new functionality etc. or perhaps you want to switch from MySQL to PostgreSQL.

Please do not confuse Models with ORMs in the below examples,  there is no magic and no persistence, these models are just developer created classes which represents a view of data i.e. Users, Orders, Countries etc. or a Model could also be a collection of tables i.e. HumanModel which combines the Users, Countries, Addresses database tables.

Example 1

<?php

    /************ RAW SQL EXAMPLE, fetch orders with user details ************/

    $sql = "SELECT 
                    o.id AS order_id,
                    o.inv,
                    o.date_created,
                    o.amount,
                    o.email,
                    o.user_id AS order_user_id, 
                    u.id,
                    u.company,
                    u.firstname,
                    u.surname,
                    u.email
                FROM orders o 
                    LEFT JOIN users u ON u.id = o.user_id";

    $params = [];
    $where = [];
    if ($orderId) {
        $where[] = "o.id = ?";
        $params[] = $orderId;
    }
    if ($userId) {
        $where[] = "u.id = ?";
        $params[] = $userId;
    }
    if ($where) {
        $sql .= " WHERE " . implode(' AND ', $where);
    }

    $sql .= " LIMIT ? OFFSET ?";
    $params[] = 30;
    $params[] = $offset;

    /* Keep track of all these "?"!!!, also, Cannot return the SQL string for scale/reuse... 
       coz mutated state is unclear, so we only return the result, and then REWRITE this SQL again in
       Nth cases in other areas of the app
    */
    return $this->pdo->fetchAll($sql, $params);

    // Now lets look at the alternative...

    /************ QUERY BUILDER EXAMPLE, fetch orders with user details ************/

    $modelOrders = new Model\Orders();
    $modelUsers = new Model\Users();
    $select = $modelOrders->getBase();

    $select->autoAlias(true)
        ->joinLeft($modelUsers->getBase(), 'users.id = orders.user_id');

    if ($orderId) {
        $select->where->equalTo('orders.id', $orderId);
    }
    if ($userId) {
        $select->where->equalTo('users.id', $userId);
    }

    $select->limit(30)->offset($offset);
    // OR
    return $this->paginate($select, $pageNumber, 30);
    // OR
    return $this->fetchAll($select);
    // OR return the select object for further scale/reuse by other apps/modules
    return $select;


Example 2

<?php
    /************ RAW SQL EXAMPLE ************/
    
    $sql = "SELECT * FROM 
                    countries 
                    WHERE id IN (".(str_repeat('?,', count($ids)-1))."?)";

    return $this->pdo->fetchAll($sql, $ids);

    // Now lets look at the alternative...
    
    /************ QUERY BUILDER ALTERNATIVE ************/
    
    return $this->fetchAll($this->select->from('countries')->where->in('id', $ids));
    // OR
    return $this->fetchAll((new Model\Geo())->getCollection()->where->in('countries.id', $ids));


Example 3

<?php
   /************ RAW SQL EXAMPLE ************/

    $sql = "SELECT * FROM 
                users  
                WHERE firstname LIKE ? OR lastname LIKE ?";

    return $this->pdo->fetchAll($sql, ['%' . $keyword . '%', '%' . $keyword . '%']);

    // Now lets look at the alternative...

    /************ QUERY BUILDER ALTERNATIVE ************/

    return $this->fetchAll($this->select->from('users')->whereOr->like(['firstname', 'lastname'], $keyword, 'full'));
    // OR extend even further
    $select = (new Model\Users())->getCollection()->whereOr->like(['firstname', 'lastname'], $keyword, 'full');
    $select->where->greaterThan('roles.id', $roleId);
    return $this->fetchAll($select);


Example 4

    /************ QUERY BUILDER SWITCH ADAPTOR ************/

    $this->db->setAdaptor('postgreSql');  // previously mysql
    $modelUsers = new Model\Users();
    
    $result = $modelUsers->save($inputData); // validation etc. and insert or update based on PK

    return is_numeric($result) 
        ? $this->fetchRow($modelUsers->getCollection()->where(['id' => $result])) 
        : $modelUsers->getErrors();


As for the performance of Query builders vs raw SQL
– the difference is negligible.

So, when should you use raw SQL – As with any other technology, when you have reached the extent of capabilities of the “tool” – However, I have found that a SQL query needs to be very complex and edge case before you’ll run into restrictions with Query builders (dependant on the vendor)… in which case you can just write raw SQL, only where truly needed and typically where queries doesn’t have to be dynamic or change that often.

Our database services

More about SQL

Category

Leave a Comment

Your email address will not be published. Required fields are marked *