SQL в качестве API

SQL в API??? 

Верно, вы уже успели подумать: «это же безумие, предоставлять API, который принимает SQL». Да, это ужасная идея. Особенно, если API обращён к Интернету. Делать так небезопасно, вы напрашиваетесь на атаки в виде SQL-инъекций. Поддержка такого интерфейса превратится в кошмар, а сама реализация бэкенда будет замкнута на конкретную технологию (это будет какая-нибудь база данных ANSI SQL).

Но справедливо ли такое суждение? Время его пересмотреть!

❯ Из всех правил бывают исключения

Программная инженерия – это искусство компромиссов. Универсального решения не существует. Поэтому здесь я представлю такой сценарий, в котором SQL действительно оказывается наилучшей технологией и тем языком, на котором API базы данных полностью раскроется.

Но для начала немного ретроспективы. Зачастую API бывают достаточно простыми. Бывает, что для выполнения задачи достаточно обычного CRUD API, который позволяет выбирать, создавать, обновлять, и удалять записи по ID.

Не спорю, так и есть. А в таком случае нет никакой нужды переусложнять архитектуру.
С другой стороны, существует достаточно и таких приложений, бизнес-логика которых предполагает постоянное расширение возможностей, и из-за этого API со временем становится всё сложнее и сложнее.

Даже на примере простейшего интернет-магазина можно достаточно наглядно продемонстрировать такой сценарий. Допустим, речь об интернет-магазине, который торгует велосипедами. Если в нём реализован классический CRUD API, то это уже достаточно немало, поскольку через него можно добавлять, удалять и обновлять позиции (велосипеды). Также можно строить список товаров и выводить его пользователю. Но что, если пользователю потребуется фильтровать этот список в зависимости от желаемых свойств велосипедов? Такими свойствами могут быть, например, цвет, размер, цена, т. д.

На первый взгляд – ничего сложного. Достаточно будет добавить в запрос несколько полей для фильтрации. Если придерживаться парадигмы HTTP/REST, то получится:

GET /bicycles?color=red&size=large&price=1000-2000

Задача решена.

Но только вы с ней справились – и поступает новое требование: добавить фильтры OR!

Может быть, их придётся сочетать с фильтрами AND, чтобы можно было выразить, например, такие ограничения: нужны велосипеды (из стали И весом от 10 до 20 кг) ИЛИ (из углеволокна И весом от 5 до 10 кг).

Если имеем дело с неизвестными значениями (в базах данных они обычно выражаются через null), то в базу кода скоро просочатся и другие подобные сложности.

И так пока она не превратится в какого-нибудь запутанного авторского макаронного монстра из многоуровневой логики фильтрации. А здесь мы говорим о простейшем сайте интернет-магазина. Знакомо звучит? Если нет – то везёт же вам. :-)

Как правило, в какой-то момент разработчик начинает структурировать эти файлы, а для сравнительно сложных запросов использовать, к примеру, JSON. Может получаться примерно такой код:

{
  "filters": {
    "or": [
      {
        "and": [
          {
            "property": "material",
            "operator": "equals",
            "value": "steel"
          },
          {
            "property": "weight",
            "operator": "between",
            "value": [10, 20]
          }
        ]
      },
      {
        "and": [
          {
            "property": "material",
            "operator": "equals",
            "value": "carbon"
          },
          {
            "property": "weight",
            "operator": "between",
            "value": [5, 10]
          }
        ]
      }
    ]
  }
}

Вот здесь уже каждый должен унюхать, что этот код попахивает.

Почему? Потому что здесь мы, в сущности, изобретаем собственный язык запросов. А изобретение собственного языка, согласитесь – это уже крайняя мера.

❯ SQL нам поможет?

 
Поскольку это статья о SQL в API, как же нам здесь поможет SQL? Определённо, мы не хотим, чтобы клиентский интерфейс интернет-магазина получал результаты только методом запрашивания данных из базы через бэкенд.

Но, признаться, сегодня такая практика уже закрепилась. В Hasura и на подобных ресурсах работа построена именно так: на основе SQL-схемы базы данных генерируется полнофункциональный graphql API.

Однако такой подход обходится дорого, требует ориентации на конкретную технологию баз данных и вдобавок замыкает вас на эту технологию и лишь в ограниченной степени поддаётся настраиванию. Зачем так делать, если есть гораздо более дешёвая альтернатива: предоставить в вашем API подмножество SQL.

Этот подход красив сразу в трёх отношениях:

1. SQL – хорошо известный и подробно документированный язык. Работа с SQL поддерживается почти во всех языках программирования. Вам не потребуется изобретать собственный синтаксический анализатор SQL или что-либо подобное, тогда как вам придётся писать именно такие вещи, если вы изобретёте «свой» язык запросов. (Кстати: при таком подходе даже не обязательно прибегать именно к SQL, но именно SQL в большинстве случаев подойдёт лучше всего, так как это общепонятный и стандартизированный язык запросов).
2. Можно предоставлять новые подмножества SQL и по мере необходимости со временем добавлять всё больше и больше функций, выполняя бизнес-требования – и не нарушая при этом работу уже имеющихся запросов и применяемого инструментария.
3. В данном случае мы устраняем целый класс недоразумений, которые могут возникать при преобразовании в SQL и обратно (если на бекенде так или иначе используется SQL-база данных). Кроме того, язык SQL компактный, и его легко читать каждому.

Но, может быть, это небезопасно? Разве нам не приходится выполнять SQL, который поступает из клиентской части непосредственно в базу данных? С точки зрения безопасности – сущий кошмар. Действительно, те, кто пытался так делать, убеждались, что это чрезвычайно неправильно. Не повторяйте их ошибок!

В самом деле, чтобы такая система работала правильно, нужно построить работу точно так, как и с любым другим API: валидировать ввод и обеззараживать, а только потом выполнять. Причём, мы говорим о подходе с чистого листа. Мы принимаем SQL только от таких запросов, работать с которыми мы специально разрешаем. Все прочие запросы отклоняются.

Но обратите внимание: при этом мы не валидируем строку SQL! Сначала мы разбираем строку SQL, как структуру данных, затем валидируем эту структуру данных и, наконец, преобразуем эту структуру данных обратно в SQL.

Иными словами: тот SQL, что применяется к базе данных, может несколько отличаться от того SQL, что был получен от API.

И это хорошо. Ведь в таком случае мы по мере необходимости можем вносить изменения в структуру SQL (например, добавлять ограничения или видоизменять идентификаторы, в частности, имена столбцов). При таком подходе наш API сможет работать даже с NoSQL-базами данных!

Да, вы всё правильно поняли: если SQL используется в API, это ещё не означает, что мы должны применять его непосредственно при работе с базой данных (ANSI-) SQL. Например, мы вполне можем сгенерировать запрос Elasticsearch.

Что ж, довольно поговорили о концепциях. Как всё это выражается на практике, в случае с реальным кодом?

Надеюсь, дорогой читатель, вы можете позволить себе роскошь работать с таким языком программирования, в котором можно аккуратно моделировать абстрактные синтаксические деревья, а также встроены такие полезные возможности, как, например, сопоставление с образцом, применяемое при декомпозиции. Если нет… советую изучить несколько языков, в которых такие возможности поддерживаются, на дворе 2024 год как-никак (извините, Go-разработчики, если я кого-то задел).

Далее я приведу примеры кода на языке Scala, но, думаю, код должен быть вам понятен, даже если вы не знаете Scala.

❯ Определяем структуру данных

Вернёмся к примеру с велосипедами, который мы уже обсуждали выше. Чтобы не усложнять, давайте применим SQL только в той части, которая отвечает за фильтрацию данных.

Как бы выглядел такой фильтр на SQL? Полностью выписанный запрос на SQL может иметь следующий вид:

SELECT *
FROM bicycles
WHERE    (material = 'steel'  AND weight BETWEEN 10 AND 20)
      OR (material = 'carbon' AND weight BETWEEN 5  AND 10)

Здесь нас интересует только выражение where:

(material = 'steel' AND weight BETWEEN 10 AND 20) OR (material = 'carbon' AND weight BETWEEN 5 AND 10)

В коде такая структура данных обычно моделируется в виде рекурсивного дерева:

sealed trait Expr
  case class Column(name: String) extends Expr
  case class And(left: Expr, right: Expr) extends Expr
  case class Or(left: Expr, right: Expr) extends Expr
  case class Between(expr: Expr, lower: IntegerValue, upper: IntegerValue) extends Expr
  case class Parenthesis(expr: Expr) extends Expr
  case class Equals(column: Column, value: Value) extends Expr
  case class ValueExpr(value: Value) extends Expr
  // ... дополнительные выражения для реализации других возможностей SQL 

sealed trait Value
  case class StringValue(value: String) extends Value
  case class IntegerValue(value: Int) extends Value
  // ... дополнительные значимые типы, которые могут пригодиться нам для разнообразных значений в SQL

Вот и всё, что нам потребуется для описания вышеупомянутого фильтра, а также значительно более сложных фильтров. Как правило, самостоятельно определять приведённую выше структуру данных нам не придётся. Следует воспользоваться библиотекой, в которой уже предоставляются такие определения.

❯ Обработка SQL

Такая библиотека также поможет нам при синтаксическом анализе SQL, поэтому на самом деле всё, что нам потребуется сделать – это:

val testSqlStringFromAPI: String =
  "(material = 'steel' AND weight BETWEEN 10 AND 20) OR (material = 'carbon' AND weight BETWEEN 5 AND 10)"

val filterExpression: Either[Error, Expr] =
  sql_library.parse(testSqlStringFromAPI)

filterExpression match {
  case Left(error) =>
    println(s"The SQL was invalid!") // здесь возвращаем на клиент ошибку 400
  case Right(sqlExpression: Expr) =>
    ??? // Вот здесь начинается самое интересное! Приступаем к обработке SQL 
}

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

Пока давайте исходить из того, что пока не разрешаем условия OR в нашем API.

А чтобы пример не разрастался, давайте просто воспроизведем наш SQL как строку. На практике, конечно, можно было бы вновь собрать SQL при помощи всё той же библиотеки.

В следующем коде мы обходим структуру SQL и валидируем/преобразуем её так, как нам нужно:

val columns = List("material", "weight", "color") // Некоторые столбцы, используемые/разрешённые в нашем API

// Рекурсивная функция для обхода структуры данных
def processSqlExpr(expr: Expr): String = expr match {
    case Column(name) =>
      if(columns.contains(name))
        name
      else
        throw new Exception(s"Column $name is unknown and not supported!")
    case And(left, right) =>
      s"(${processSqlExpr(left)} and ${processSqlExpr(right)})"
    case Or(left, right) =>
      throw new Exception("Or-clauses are not supported yet!")
    case Between(expr, lower, upper) =>
      s"${processSqlExpr(expr)} between ${processSqlValue(lower)} and ${processSqlValue(upper)}"
    // Следующий код убирает двойные фигурные скобки! :-)
    case Parenthesis(Parenthesis(expr)) =>
      s"(${processSqlExpr(expr)})"
    case Parenthesis(expr) =>
      s"(${processSqlExpr(expr)})"
    case Equals(column, value) =>
      s"${column.name} = ${processValue(value)}"
    case ValueExpr(value) =>
      processSqlValue(value)
  }

def processSqlValue(value: Value) = value match {
  case StringValue(value) =>
    s"'$value'"
  case IntegerValue(value) =>
    value.toString
}

Вот и всё, что нам требуется для работы с фильтрами любой сложности. Давайте испробуем, что у нас получилось:

processSqlExpr("(material = 'steel' AND weight BETWEEN 10 AND 20) OR (material = 'carbon' AND weight BETWEEN 5 AND 10)")
// Ошибка: условия OR пока не поддерживаются!

processSqlExpr("((material = 'steel' AND weight BETWEEN 10 AND 20))")
// вывод: (material = 'steel' and weight between 10 and 20)
// ^^^ Обратите внимание: орфографически SQL немного отличается от ввода; кроме того, мы удалили двойные фигурные скобки.

Если впоследствии мы решим добавить поддержку условий OR, то нам всего лишь потребуется заменить

throw new Exception("Or-clauses are not supported yet!")

на

s"(${processSqlExpr(left)} or ${processSqlExpr(right)})"

Фактически, теперь мы без всяких усилий можем предоставить через наш API почти любую возможность SQL.

❯ Итоги 

Поскольку в SQL гораздо больше возможностей и синтаксических тонкостей, чем показано в этом примере, функция processSqlExpr() получится немного длиннее, так как в ней нужно предусмотреть обработку более разнообразных случаев (или же предусмотреть универсальный случай, который будет выдавать ошибку всякий раз при попытке использовать неподдерживаемую возможность).

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

Если впоследствии мы захотим активировать дополнительные возможности SQL, то с лёгкостью сможем это сделать, а все уже имеющиеся SQL-запросы будут работать, как и ранее. Здесь мы имеем в виду более сложные возможности, например, различные виды разбивки на страницы, пределы, упорядочивание, группирование, т. д.

Кроме того, обратите внимание: мы по-прежнему без проблем можем изменить схему базы данных, достаточно только следить, чтобы названия предоставляемых в API столбцов «column-names» оставались прежними. При обработке нам всего лишь потребуется отобразить их на новую схему.

Постойте! 

Итак, теперь вы вполне представляете, как такой подход может быть реализован с технической точки зрения. Но важно понимать, что это всё равно нишевое решение. Оно сопряжено с определёнными недостатками, которые желательно хорошо себе представлять!

Документация и обработка ошибок 

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

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

Кроме того, если будет предоставлен неверный/недействительный SQL, то соответствующие сообщения об ошибках, поступающие с бэкенда, должны быть достаточно информативными.

Поскольку обычно запрос будет содержать SQL в виде строки, никакой дополнительной структуры в нём не будет. Это значит, что у тех, кто его выполняет, почти не будет инструментов, которые обеспечили бы поддержку при такой работе.

Положительный момент в том, что SQL обычно легче поддаётся чтению и синтаксическому разбору, чем крупные структуры JSON. Кроме того, SQL можно с лёгкостью скопировать в SQL-редактор, где автоматически расставляется нужное форматирование и предоставляется поддержка синтаксиса.

Немного о производительности

Синтаксический разбор SQL – это простая операция, и, если осуществлять её правильно, то она может лишь пренебрежимо сказаться на общей производительности API. Но в тех случаях, когда любая задержка критична, либо приходится обрабатывать огромную массу запросов (и, следовательно, выжимать производительность до капли) SQL в API, пожалуй, использовать не нужно.

Кроме того, как бывает обычно и с API graphql, необходимо учитывать возможность DDoS-атак. Поскольку SQL (в том виде, как он реализован выше) по умолчанию допускает произвольные вложения без каких-либо контрмер (например, не ограничивается глубина вложения или количество условий), такой практикой легко злоупотреблять. В таком случае даже немногочисленные запросы могут оказывать серьёзную нагрузку на сервер.

Зависимость от производителя

Притом, что язык SQL считается стандартом, всё равно через API могут предоставляться конкретные функции или возможности базы данных. В таком случае впоследствии будет сложно переключиться на использование другой технологии базы данных на бэкенде.

Правда, эта проблема не менее актуальна и для других API, никак не связанных с SQL, поэтому, строго говоря, данное явление нельзя считать недостатком применения SQL в API. Правда, бывает соблазнительно добавлять в API всё больше фич SQL, коль скоро API уже использует SQL, а фичи так легко добавлять.
 

❯ И напоследок

Как правило, следует ожидать, что для API вообще не потребуется всей той гибкости, которую предлагает язык SQL. А многие люди слишком долго пренебрегали SQL, пожалуй, когда-то такое отношение действительно было оправданным.

С другой стороны, если сторониться настолько мощного и гибкого решения, каким является SQL, вы обречены снова и снова изобретать велосипед и привносить в вашу базу кода массу ситуативной сложности, если с точки зрения пользователя API действительно должен быть гибким.

А как было показано выше, сила SQL действительно может быть безопасной и удобной.
Надеюсь, эта статья убедила вас, что не нужно отметать с порога такое решение как SQL, а взвешивать все «за» и «против», когда приходится иметь дело с необычной ситуации, в которой приходится применять достаточно сложный API.