Любое приложение, работающее с запросами SQL, может извлечь выгоду из использования построителя запросов для улучшения читаемости кода, удобства обслуживания и безопасности. На самом деле в Golang существует множество различных библиотек, которые делают именно это. Здесь, в Vaunt, мы попробовали много разных вариантов, прежде чем, наконец, решили создать свой собственный. В конечном счете, мы хотели что-то безопасное и обеспечивающее замену переменных, чтобы предотвратить SQL-инъекцию, но при этом быть читаемым и иметь возможность использовать условные операторы. Поэтому мы создали новую библиотеку под названием tqla, выпущенную и анонсированную в конце прошлого года. Подробнее об этом можно прочитать в этой статье.

До создания tqla мы в основном использовали Squirrel для логики построения SQL-запросов — и настоятельно рекомендуем его. Мы по-прежнему используем Squirrel в некоторых областях, но постепенно начали заменять и внедрять новую логику построения запросов с помощью tqla. Мы обнаружили множество случаев, когда tqla улучшала нашу способность поддерживать наш код и устранять проблемы, с которыми мы сталкивались при использовании других конструкторов операторов.

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

Недавно в Vaunt мы провели миграцию базы данных с CockroachDB на TiDB. Хотя CockroachDB был производительным и надежным, в конечном итоге мы столкнулись с решением добавить в наш стек технологий поддержку базы данных OLAP. Это было необходимо для поддержки нашей аналитической работы над нашим продуктом для анализа сообщества с открытым исходным кодом. Чтобы сохранить небольшое технологическое пространство, мы решили двигаться вперед с TiDB и воспользоваться преимуществами HTAP-архитектуры базы данных. 

CockroachDB в значительной степени совместим с PostgreSQL, и мы использовали синтаксис PostgreSQL для многих наших SQL-запросов. Чтобы перейти на TiDB, нам пришлось изменить несколько наших таблиц и обновить запросы, чтобы они использовали синтаксис MySQL. В нескольких местах во время миграции мы обнаружили, что неправильно использовали операторы построения условных запросов, и у нас не было надлежащих тестов, позволяющих обнаружить, что операторы генерируются неправильно.

Демонстрация

В README Squirrel есть пример того, как можно использовать построение условных запросов для обновления операторов с помощью дополнительных фильтров:

if len(q) > 0 {
    users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

Вот реальный, но упрощенный пример того, как мы обновили один из наших запросов, чтобы условно объединить таблицы и добавить дополнительный фильтр:

psql := squirrel.StatementBuilder.PlaceholderFormat(squirrel.Question)

statementBuilder := psql.Select(`i.id`).
From("vaunt.installations i").
Where(`entity_name = ?`, name)

if len(provider) > 0 {
    statementBuilder.Where(`provider = ?`, provider)
}

if len(repo) > 0 {
    statementBuilder.Join(`repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'))`)
    statementBuilder.Where(`r.name = ?`, repo)
}

Можете ли вы обнаружить проблему в коде? Если нет, не волнуйтесь — это тоже ускользнуло от наших проверок кода, пока мы не запустили тесты. 

Проблема в том, что мы забыли обновить построитель операторов результатами функций построителя. Например, фильтр условий поставщика должен выглядеть так:

if len(provider) > 0 {
    statementBuilder = statementBuilder.Where(`provider = ?`, provider)
}

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

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

Использование tqla

Приведенная выше демонстрация с использованием Squirrel была переписана, и ее эквивалент в tqla выглядит следующим образом:

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question))
if err != nil {
    return nil, err
}

query, args, err := t.Compile(`
    SELECT i.id
    FROM vaunt.installations as i
    {{ if .Repo }}
    JOIN vaunt.repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'), '$')
    {{ end }}
    WHERE entity_name = {{ .Name}}
    {{ if .Provider }}
    AND i.provider = {{ .Provider }}
    {{ end }}
    {{ if .Repo }}
    AND r.name = {{ .Repo }}
    {{ end }}
    `, data)
if err != nil {
    return nil, err
}

Как видите, синтаксис шаблона tqla упрощает включение условных предложений. Tqla автоматически заменяет переменные, которые мы устанавливаем, указанными заполнителями и предоставляет аргументы, которые мы можем использовать с нашим драйвером sql для выполнения оператора.

Как и в случае с Squirrel, этот подход к построению операторов легко тестировать, поскольку мы можем создавать различные наборы объектов данных для передачи построителю шаблонов и проверки выходных данных.

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

Пользовательские функции

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

Вот пример того, как мы использовали функцию для преобразования значения time.Time из Golang в sql.NullTime, что позволяет нам выполнять вставку в наши объекты данных без необходимости его предварительного преобразования:

funcs := template.FuncMap{
    "time": func(t time.Time) sql.NullTime {
        if t.IsZero() {
            return sql.NullTime{Valid: false}
        }
        return sql.NullTime{Time: t, Valid: true}
    },
}

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question), tqla.WithFuncMap(funcs))
if err != nil {
    return err
}

Поскольку эта функция определена в нашей карте функций tqla, мы теперь можем свободно использовать ее в наших шаблонах запросов, предоставив ей параметр из объекта данных, который является полем time.Time. Мы даже можем вызывать эту функцию несколько раз в одном и том же шаблоне с разными полями.

Вот упрощенный пример:

statement, args, err := t.Compile(`
    INSERT INTO events
        (name, created_at, merged_at, closed_at)
    VALUES ( 
        {{ .Name }},
        {{ time .CreatedAt }},
        {{ time .MergedAt }},
        {{ time .ClosedAt }}
    )`, eventData)

Заключение

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

Мы сделали tqla открытым исходным кодом, чтобы поделиться этой библиотекой в ​​надежде, что она предоставит хороший вариант для других пользователей, которым нужен простой, удобный и безопасный способ создания SQL-запросов во многих различных типах приложений.

Если вам интересно, пожалуйста, проверьте репозиторий и поставьте ему звездочку, если он вам чем-то поможет. Не стесняйтесь запрашивать какие-либо функции или сообщать об ошибках!

Мы всегда открыты для отзывов и предложений.

Чтобы быть в курсе будущих разработок, подписывайтесь на нас на X или присоединяйтесь к нашему Discord!