gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Overview

gosq

Go Simple Query builder.

Go Reference Go Report Card

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

It provides syntax to inject arbitrary conditional query piece.

Usage

q, err := gosq.Compile(`
  SELECT
    products.*
    {{ [if] .IncludeReviews [then] ,json_agg(reviews) AS reviews }}
  FROM products
  {{ [if] .IncludeReviews [then] LEFT JOIN reviews ON reviews.product_id = products.id }}
  WHERE category = $1
  OFFSET 100
  LIMIT 10
`, struct{
  IncludeReviews bool
}{
  IncludeReviews: true,
})

or

q, err := gosq.Compile(`
  SELECT
    products.*
    {{ [if] .IncludeReviews [then] ,json_agg(reviews) AS reviews }}
  FROM products
  {{ [if] .IncludeReviews [then] LEFT JOIN reviews ON reviews.product_id = products.id }}
  WHERE category = $1
  OFFSET 100
  LIMIT 10
`, map[string]interface{}{
  "IncludeReviews": true,
})

Installation

go get github.com/sanggonlee/gosq

Documentation

godoc

Why?

For me, if I want to run a query, the best way to build such query is to just write the entire thing in the native SQL and pass it to the runner. For example:

func getProducts(db *sql.DB) {
  q := `
    SELECT *
    FROM products
    WHERE category = $1
    OFFSET 100
    LIMIT 10
  `
  category := "electronics"
  rows, err := db.Query(q, category)
  ...

It's declarative, easy to understand, and everything is in a single place. What You Give Is What You Get.

But we're living in a dynamic world of requirements, and writing static queries like this will quickly get out of hand as new requirements come in. For example, what if we want to optionally join with a table called "reviews"?


I could define a clause and optionally concatenate to the query, like this:

func getProducts(db *sql.DB, includeReviews bool) {
  var (
    reviewsColumn string
    reviewsJoinClause string
  )
  if includeReviews {
    reviewsColumn = `, json_agg(reviews) AS reviews`
    reviewsJoinClause = `LEFT JOIN reviews ON reviews.product_id = products.id`
  }

  q := `
    SELECT products.*
    `+reviewsColumn+`
    FROM products
    WHERE category = $1
    `+reviewsJoinClause+`
    OFFSET 100
    LIMIT 10
  `
  category := "electronics"
  rows, err := db.Query(q, category)

I don't know about you, but I'm already starting to get uncomfortable here. I can think of several reasons here:

  • Dynamically concatenating strings is prone to errors. For example the comma at the start of , json_agg(reviews) AS reviews is very easy to miss.
  • The query parts are starting to scatter around, and you have to jump between the conditional cases to understand what's going on.
  • It's harder to see the overall, cohesive structure of the query. It might not show on this simple example, but as the query gets complex it's often hard to see even the most primary goal of the query.

There are some SQL builder libraries out there, like squirrel or dbr. Maybe they will help?

import sq "github.com/Masterminds/squirrel"

func getProducts(db *sql.DB, includeReviews bool) {
  category := "electronics"
  qb := sq.Select("products.*").
    From("products").
    Where(sq.Eq{"category": category}).
    Offset(100).
    Limit(10)

  if includeReviews {
    qb = qb.Column("json_agg(reviews) AS reviews").
      LeftJoin("reviews ON reviews.product_id = products.id")
  }

  q, args, _ := qb.ToSql()
  rows, err := db.Query(q, args...)

That looks a lot better! It's easier to understand, and we've addressed some of the issues we saw earlier, especially around missing commas.

But I'm still not 100% happy. That's too much Go code sneaked into what is really just a SQL query. Still a little hard to understand it as a whole. Also it didn't solve the problem of having to jump around the conditional cases to understand logic. This will get only worse as we have more and more conditional statements.

At the end, what I'd like is a SQL query that can dynamically respond to arbitrary requirements.


How about some really simple conditionals embedded in a SQL query, rather than SQL query chunks embedded in application code? Something like this, maybe?

func getProducts(includeReviews bool) {
  type queryArgs struct {
    IncludeReviews bool
  }
  q, err := gosq.Compile(`
    SELECT
      products.*
      {{ [if] .IncludeReviews [then] ,json_agg(reviews) AS reviews }}
    FROM products
    {{ [if] .IncludeReviews [then] LEFT JOIN reviews ON reviews.product_id = products.id }}
    WHERE category = $1
    OFFSET 100
    LIMIT 10
  `, queryArgs{
    IncludeReviews: true,
  })
  rows, err := db.Query(q, category)

And here we are, gosq is born.

Note, this still doesn't address the problem with the preceeding comma. I can't think of a good way to address it in this solution - any suggestion for improvement is welcome.

You might also like...
An easy-use SQL builder.

EQL An easy-use SQL builder. Design We are not English native speaker, so we use Chinese to write the design documents. We plan to translate them to E

A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

SQL query helper

SQL query helper

Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

Go fearless SQL. Sqlvet performs static analysis on raw SQL queries in your Go code base.

Sqlvet Sqlvet performs static analysis on raw SQL queries in your Go code base to surface potential runtime errors at build time. Feature highlights:

PirateBuilder - Pirate Builder For Golang
PirateBuilder - Pirate Builder For Golang

PirateBuilder Builder You need to extract the file "PirateBuilder.rar". Start "P

Nerdlight-firmwarebuilder - Nerdlight NodeMCU Firmware Builder CLI

nerdlight-firmwarebuilder ⚒ ⚡️ Nerdlight NodeMCU Firmware Builder CLI ⚒ Descript

Tag based url Query parameters Constructor.

taqc 🚕 Tag based url Query parameters Constructor. (This is pronounced as same as "taxi") Synopsis type Query struct { Foo string `ta

Releases(v1.1.0)
Owner
Sang-gon Lee
Sang-gon Lee
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 26 Sep 26, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 886 Nov 21, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 180 Nov 26, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 89 Nov 22, 2022
Data-builder - Data builder with golang

databuilder import "github.com/go-coldbrew/data-builder" Index Variables func Is

Coldbrew 0 Feb 5, 2022
Go database query builder library for PostgreSQL

buildsqlx Go Database query builder library Installation Selects, Ordering, Limit & Offset GroupBy / Having Where, AndWhere, OrWhere clauses WhereIn /

Arthur Kushman 84 Nov 23, 2022
Simple query builder for MongoDB

?? greenleaf - simple, type safe and easy to use query builder for MongoDB Installation To install use: go get github.com/slavabobik/greenleaf Quick

Slava 75 Nov 27, 2022
a golang library for sql builder

Gendry gendry is a Go library that helps you operate database. Based on go-sql-driver/mysql, it provides a series of simple but useful tools to prepar

DiDi 1.4k Nov 28, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 56 Nov 17, 2022
golang orm and sql builder

gosql gosql is a easy ORM library for Golang. Style: var userList []UserModel err := db.FetchAll(&userList, gosql.Columns("id","name"), gosql.

RushTeam 167 Nov 19, 2022