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.

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 23 Apr 25, 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 723 Jun 24, 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 162 Jun 24, 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 70 Jun 24, 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 63 Jun 12, 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 72 Jun 22, 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 Jun 22, 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

徐佳军 53 Jan 23, 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 160 May 12, 2022
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

Go微服务 25 Jun 17, 2022
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

Ozzo Framework 551 Jun 3, 2022
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

Augmentable 3k Jun 23, 2022
SQL query helper

SQL query helper

Aleksey Nikitin 1 Nov 7, 2021
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

XO 3.1k Jun 20, 2022
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:

QP Hou 446 Jun 19, 2022
PirateBuilder - Pirate Builder For Golang

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

Lowki 2 Jun 10, 2022
Nerdlight-firmwarebuilder - Nerdlight NodeMCU Firmware Builder CLI

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

Marco Palmisano 1 Feb 12, 2022
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

moznion 2 Jan 11, 2022