SQL vs ORM

в 12:26, , рубрики: crud, laravel, object-relational mapping, orm, php, postgresql, sql, symfony, Блог компании PG Day'17 Russia, Разработка веб-сайтов

Друзья, вновь пришло время авторской колонки корпоративного блога PG Day’17. Предлагаем вашему вниманию сравнительный анализ работы с PostgreSQL из популярных ORM от varanio.

SQL vs ORM - 1

ORM (Object-Relational Mapping), по идее, должен избавить нас от написания SQL запросов и, в идеале, вообще абстрагировать от базы данных (от способа хранения данных), чтобы мы могли работать с классами, в той или иной степени выражающими объекты бизнес-логики, не задаваясь вопросом, в каких таблицах всё это по факту лежит.

Посмотрим, насколько это удается современным библиотекам на PHP. Давайте рассмотрим несколько типичных кейсов и сравним ORM с голым SQL, написанным вручную.

Для примера возьмем две таблицы: книги и авторы книг, отношение многие-ко-многим (у книг может быть много авторов, у авторов может быть много книг). Т.е. в базе это будут books, authors и связующая таблица author_book:

Вот схема 
CREATE TABLE authors (
   id bigserial,
   name varchar(1000) not null,
   primary key(id)
);

CREATE TABLE books (
   id bigserial,
   name VARCHAR (1000) not null,
   text text not null,
   PRIMARY KEY (id)
);

CREATE TABLE author_book (
   author_id bigint REFERENCES authors(id),
   book_id bigint REFERENCES books(id),
   PRIMARY key(author_id, book_id)
);

Рассмотрим несколько кейсов использования.

Кейс 1. Создание записей

Добавим авторов и книг.

Голый SQL

Ну, тут всё просто и прямолинейно:

Голый SQL

    $stmt = $pdo->prepare(
        "INSERT INTO books (name, text) VALUES (:name, :text) RETURNING id"
    );
    $stmt->execute(
        [':name' => 'Книга', ':text' => 'Текст']
    );
    $bookId = $stmt->fetchColumn();

    $stmt = $pdo->prepare(
        "INSERT INTO authors (name) VALUES (:name) RETURNING id"
    );
    $stmt->execute(
        [':name' => 'Автор']
    );
    $authorId = $stmt->fetchColumn();

    $pdo->prepare(
        "INSERT INTO author_book (book_id, author_id) VALUES (:book_id, :author_id)"
    )->execute(
        [':book_id' => $bookId, ':author_id' => $authorId]
    );

Многовато писанины. Можно не использовать прям совсем голый PDO, а взять что-нибудь чуть полаконичнее, какую-нибудь легкую обертку. Но в любом случае надо писать запросы вручную и знать синтаксис SQL.

Laravel (Eloquent SQL)

В Laravel используется ORM под названием Eloquent. Eloquent — это, по сути, ActiveRecord, т.е. отображение таблиц на некие соответствующие им классы ("модели"), причем модель сама умеет себя сохранять.

Итак, делаем две модели. По умолчанию даже имена таблиц нигде указывать не надо, если они называются как классы. Надо указать $timestamps = false, чтобы не сохраняло автоматически время обновления модели.

Классы моделей Eloquent

 namespace App;

 use IlluminateDatabaseEloquentModel;

 class Book extends Model
 {
     public $timestamps = false;

     public function authors()
     {
         return $this->belongsToMany(Author::class);
     }
 }

namespace App;

use IlluminateDatabaseEloquentModel;

class Author extends Model
{
    public $timestamps = false;

    public function books()
    {
        return $this->belongsToMany(Books::class);
    }
}

Как видно, мы запросто описали отношение many-to-many буквально парой строк кода. Создание записей в базе и связь между ними делается достаточно просто:

$book = new AppBook;
$book->name = 'Книга';
$book->text = 'Текст';
$book->save();

$author = new AppAuthor;
$author->name = 'Автор';
$author->save();

// делаем связь
$book->authors()->save($author);

Или списком:

$book = AppBook::create(['name' => 'Книга', 'text' => 'Текст']);
$author = AppAuthor::create(['name' => 'Автор']);
$book->authors()->save($author);

Так, конечно, поприятнее, чем возиться с SQL, и даже запись в связочную таблицу делается очень легко.

Symfony (Doctrine ORM)

В доктрине используется подход DataMapper. По уверениям документации, объекты бизнес-логики отделены от способа сохранения. Здесь объекты получаются из Репозитория (Repository), т.е. сущность не знает как себя получить, это знает только Repository, а для сохранения потребуется EntityManager.

Сгенерировать классы из существующих таблиц можно одним движением: 

bin/console doctrine:mapping:import --force AppBundle yml
bin/console doctrine:generate:entities AppBundle

Первая команда создаст yml-файлы для сущностей, описывающие типы полей в базе, взаимосвязь объектов (например, many-to-many) и т.д. Вторая команда создаст классы сущностей.

Прямо скажем, yml получились немаленькие, и они набиты подробностями о структуре таблиц и их связях. Вообще, можно обойтись и без yml, всё делая в аннотациях классов. Но когда классы сущностей совершенно отделены от базы, это больше соответствует концепции DDD.

Зато сами классы-сущности у нас получились совершенно простые, т.е. POJO (plain old php object):

Классы-сущности

namespace AppBundleEntity;

/**
 * Authors
 */
class Authors
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * @var
 DoctrineCommonCollectionsCollection
     */
    private $book;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->book = new DoctrineCommonCollectionsArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Authors
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Add book
     *
     * @param AppBundleEntityBooks $book
     *
     * @return Authors
     */
    public function addBook(AppBundleEntityBooks $book)
    {
        $this->book[] = $book;

        return $this;
    }

    /**
     * Remove book
     *
     * @param AppBundleEntityBooks $book
     */
    public function removeBook(AppBundleEntityBooks $book)
    {
        $this->book->removeElement($book);
    }

    /**
     * Get book
     *
     * @return DoctrineCommonCollectionsCollection
     */
    public function getBook()
    {
        return $this->book;
    }
}


namespace AppBundleEntity;

/**
 * Books
 */
class Books
{
    /**
     * @var integer
     */
    private $id;

    /**
     * @var string
     */
    private $name;

    /**
     * @var string
     */
    private $text;

    /**
     * @var DoctrineCommonCollectionsCollection
     */
    private $author;

    /**
     * Constructor
     */
    public function __construct()
    {
        $this->author = new DoctrineCommonCollectionsArrayCollection();
    }

    /**
     * Get id
     *
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set name
     *
     * @param string $name
     *
     * @return Books
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }

    /**
     * Get name
     *
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * Set text
     *
     * @param string $text
     *
     * @return Books
     */
    public function setText($text)
    {
        $this->text = $text;

        return $this;
    }

    /**
     * Get text
     *
     * @return string
     */
    public function getText()
    {
        return $this->text;
    }

    /**
     * Add author
     *
     * @param AppBundleEntityAuthors $author
     *
     * @return Books
     */
    public function addAuthor(AppBundleEntityAuthors $author)
    {
        $this->author[] = $author;

        return $this;
    }

    /**
     * Remove author
     *
     * @param AppBundleEntityAuthors $author
     */
    public function removeAuthor(AppBundleEntityAuthors $author)
    {
        $this->author->removeElement($author);
    }

    /**
     * Get author
     *
     * @return DoctrineCommonCollectionsCollection
     */
    public function getAuthor()
    {
        return $this->author;
    }
}

Создаем объекты и сохраняем. Примерно так:

$em = $this->getDoctrine()->getManager();

$author = new Authors();
$author->setName("Автор");

$book =  new Books();
$book->setName("Книга");
$book->setText("Текст");

$book->addAuthor($author);
$author->addBook($book);

$em->persist($book);
$em->persist($author);
$em->flush();

Вывод

В целом, использование ORM для простых случаев создания записей в таблицах является более предпочтительным способом, чем чистый SQL. Методы setName и т.д. в коде читаются лучше, чем SQL-запрос. Нет жесткой зависимости от БД.

Кейс 2. Обновление названия книги

Голый SQL

$stmt = $pdo->prepare('UPDATE books SET name=:name WHERE id=:id');
$stmt->execute([
    ':name' => 'Книга 2', ':id' => 1
]);

Laravel (Eloquent)

$book = AppBook::find(1);
$book->name = 'Книга 2';
$book->save();

Symfony

$em = $this->getDoctrine()->getManager();
$repository = $em->getRepository(Books::class);
$book = $repository->find(1);
$book->setName("Книга 2");
$em->persist($book);

Вывод

Обновление какого-то поля в целом тоже вполне можно делать через ORM, не вдаваясь в детали SQL.

Кейс 3. Получить список названий книг с авторами

Для тестов создадим такие записи в таблице:

Данные

delete from author_book;
delete from books;
delete from authors;

insert into authors 
(id, name) 
values 
(1, 'Автор 1'),
(2, 'Автор 2'),
(3, 'Автор 3');

insert into books 
(id, name, text) 
values 
(1, 'Книга 1', 'Много текста 1'),
(2, 'Книга 2', 'Много текста 2'),
(3, 'Книга 3', 'Много текста 3');

insert into author_book 
(author_id , book_id) 
values 
  (1,1),
  (1,2),
  (2,2),  
  (3,3);

Голый SQL

Если брать голый SQL для вывода списка книг с авторами, то это будет примерно так (допустим, авторов хотим получить в виде json):

select 
  b.id as book_id, 
  b.name as book_name, 
  json_agg(a.name) as authors 
from books b 
   inner join author_book ab
      on b.id = ab.book_id
   INNER join authors a 
      on ab.author_id = a.id
GROUP BY 
   b.id

Результат:

 book_id | book_name |        authors         
---------+-----------+------------------------
       1 | Книга 1   | ["Автор 1"]
       3 | Книга 3   | ["Автор 3"]
       2 | Книга 2   | ["Автор 1", "Автор 2"]
(3 rows)

Laravel

Сделаем сначала втупую из мануалов а-ля "Getting Started":

    $books = AppBook::all();

    /** @var $author AppAuthor */
    foreach ($books as $book) {
        print $book->name . "n";
        foreach ($book->authors as $author) {
            print $author->name . ";";
        }
    }

Код получился гораздо проще, чем голый SQL. Все просто и понятно, works like magic. Только при детальном рассмотрении магия там достаточно фиговая. Eloquent делает аж 4 запроса:

select * from "books";
-- и еще по запросу на каждую книгу: 
select 
   "authors".*, 
   "author_book"."book_id" as "pivot_book_id", 
   "author_book"."author_id" as "pivot_author_id" 
from "authors" 
   inner join "author_book" 
       on "authors"."id" = "author_book"."author_id" 
where "author_book"."book_id" = ?

Для маленького ненагруженного сайта сойдет и так. Но если сайт нагружен и таблицы содержат много строк и данных, то это провал.

Во-первых, конструкции select * и select authors.*. За такое сразу партбилет на стол. Если книги будут "жирными" ("Война и Мир" или "Британская энциклопедия"), то ни к чему тянуть сразу их текст, когда нужен только список названий. К тому же, со временем в таблицах количество полей обычно все нарастает и нарастает, т.е. такое приложение будет работать всё медленнее и медленнее, жрать всё больше и больше памяти. Я уж не говорю о том, что количество запросов authors.* равно количеству книг.

Что тут можно предпринять? Во-первых, можно указать, какие поля берем из книги, т.е (['id', 'name']). Ну, и использовать with() для т.н. "eager loading". Итого:

$books = AppBook::with('authors')->get(['id', 'name']);

Стало немного получше, но всё равно далеко от идеала:

select "id", "name" from "books";
select 
  "authors".*, 
  "author_book"."book_id" as "pivot_book_id", 
  "author_book"."author_id" as "pivot_author_id" 
from "authors" 
  inner join "author_book" 
    on "authors"."id" = "author_book"."author_id" 
where 
  "author_book"."book_id" in (?, ?, ?);

Тут две проблемы: authors идут всё равно со звездочкой. Кроме того, появился оператор in() с перечислением всех id, который нормально работает при маленьком количестве книг, но для большого списка это будет работать очень медленно, по крайней мере в PostgreSQL. Хотя, конечно, быстрее, чем по запросу на каждый. И с этим уже, похоже, ничего не сделать, по крайней мере я ничего не нашел.

Точнее, помимо ORM есть еще Query Builder:

     $result = DB::table('books')
         ->join('author_book', 'books.id', '=', 'author_book.book_id')
         ->join('authors', 'author_book.author_id', '=', 'authors.id')
         ->select('books.id', 'books.name', 'authors.name')
         ->get();

Но это, повторяю, не ORM. Это тот же SQL, только вместо пробелов стрелочки и куча методов, которые надо знать дополнительно.

Symfony

Для начала тоже попробуем по-простому:

$doctrine = $this->getDoctrine();
$books = $doctrine
    ->getRepository(Books::class)
    ->findAll();

foreach ($books as $book) {
    print $book->getName() . "n";
    foreach ($book->getAuthor() as $author) {
        print $author->getName() . ";";
    }
}

Код первой попытки почти такой же как в Laravel. SQL-запросы, в общем, тоже:

SELECT 
    t0.id AS id_1, 
    t0.name AS name_2, 
    t0.text AS text_3 
FROM books t0;

-- и еще 3 запроса таких:
SELECT 
    t0.id AS id_1, 
    t0.name AS name_2 
FROM authors t0 
    INNER JOIN author_book 
         ON t0.id = author_book.author_id 
WHERE 
     author_book.book_id = ?

Т.е. перечислены все поля, и по запросу на каждую книгу с перечислением, опять же, всех полей.

У стандартных методов типа findAll и т.д., похоже, нет способа указать, что мне надо только такие-то поля и сразу приджойнить такие-то таблицы. Но, зато в Доктрине есть SQL-подобный синтаксис DQL, абстрагированный от конкретной СУБД, которым можно воспользоваться. Он оперирует не таблицами, а сущностями.

 $query = $this->getDoctrine()->getManager()->createQuery('
    SELECT 
         partial b.{id, name}, partial a.{id, name} 
    FROM AppBundleEntityBooks b 
       JOIN b.author a'
 );
 $books = $query->getResult();

Ну да, получилось типа того, что надо, один запрос, с одним полем:

SELECT 
   b0_.id AS id_0, 
   b0_.name AS name_1, 
   a1_.id AS id_2, 
   a1_.name AS name_3 
FROM 
   books b0_ 
INNER JOIN author_book a2_ 
   ON b0_.id = a2_.book_id 
INNER JOIN authors a1_ 
   ON a1_.id = a2_.author_id

Выводы

На мой взгляд, простой SQL выглядит проще и стандартнее. Кроме того, в ORM-подходах мы не смогли полностью сферически абстрагироваться от базы данных, нам пришлось подстроиться под реальный мир.

DQL в принципе сойдет на замену SQL, и он не особо привязан к СУБД, но это еще один странноватый синтаксис, который надо учить отдельно.

Кейс 4. Чуть более сложный UPDATE

Допустим, стоит задача обновить двум последним авторам имя на "Жорж".

голый SQL

Тут всё просто, запрос с подзапросом.

UPDATE authors
SET name = 'Жорж'
WHERE id in (
    SELECT id
    FROM authors
    ORDER BY id DESC
    LIMIT 2
);

Laravel

Сначала я попробовал сделать так:

 AppAuthor::orderBy('id', 'desc')->take(2)->update(["name" => "Жорж"]);

Это было бы здорово и красиво, однако не сработало. Точнее сработало, но заменило записи всем авторам, а не только двум.

Тогда, покурив мануал и SO, удалось родить такую конструкцию:

AppAuthor::whereIn(
        'id',
        function($query) {
            $query->select('id')
                ->from((new AppAuthor())->getTable())
                ->orderBy('id', 'desc')
                ->limit(2);
        }
    )->update(['name' => 'Жорж']);

Это работает хорошо, хоть и не особо читабельно. Да и query builder опять какой-то подъехал.

Symfony

Сразу скажу, что выразить через DQL мне этот запрос вообще не удалось, с вложенными подзапросами там всё плохо.

Есть, конечно, query builder, но получалось что-то совсем зубодробительное, и я бросил эту затею. ORM должен помогать экономить время, а не наоборот. Надеюсь, опытные симфонисты в коментах подскажут какой-нибудь легкий и изящный способ сделать update с подзапросом.

Вывод

Несмотря на привлекательную идею использовать классы бизнес-логики и не вдаваться в детали реализации хранения данных, сделать это удается далеко не всегда.

Как всегда обычно и бывает, истина где-то посередине. Для простых CRUD-операций ORM вполне может сэкономить время разработки и улучшить читабельность кода. Однако шаг вправо, шаг влево — и гораздо удобнее пользоваться нативным SQL. Например, сложные выборки/обновления (особенно, аналитические отчеты с оконными функциями и рекурсивными CTE). Компромиссным вариантом является маппинг результатов нативных запросов на объекты, Доктрина это позволяет.

В споре ORM vs SQL не победил никто.

Тем временем, всех кто намучался с ORM, тормозящими запросами и плохой производительностью в рабочих ситуациях, приглашаем на PG Day'17. У нас подготовлено для вас множество различных докладов и мастер-классов для самых разных баз данных!

Автор: rdruzyagin

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js