sqlb

package module
v0.0.0-...-ece00b7 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 30, 2023 License: Apache-2.0 Imports: 14 Imported by: 2

README

sqlb Build Status

sqlb is a Golang library designed for efficiently constructing SQL expressions. Instead of hand-constructing strings containing raw SQL, users of the sqlb library instead construct query expressions and the sqlb library does the work of producing the raw strings that get sent to a database.

Building SQL expressions, not strings

It's best to learn by example, so let's walk through a common way in which Golang applications might typically work with an underlying SQL database and transform this application to instead work with the sqlb library, showing the resulting gains in both code expressiveness, application speed and memory efficiency.

Our example will be a simple blogging application.

Imagine we have the following set of tables in our database:

CREATE TABLE users (
  id INT NOT NULL,
  email VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL,
  is_author CHAR(1) NOT NULL,
  profile TEXT NULL,
  created_on DATETIME NOT NULL,
  updated_on DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX (email)
);

CREATE TABLE articles (
  id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  content TEXT NOT NULL,
  created_by INT NOT NULL,
  published_on DATETIME NULL,
  PRIMARY KEY (id),
  INDEX ix_title (title),
  FOREIGN KEY fk_users (created_by) REFERENCES users (id)
);

Our blogging application's default home page might return information about the last ten articles published. It's reasonable to believe that the following SQL expression might be used to grab this information from the database:

SELECT
  articles.title,
  articles.content,
  articles.created_on
  users.name,
FROM articles
JOIN users
 ON articles.created_by = users.id
ORDER BY articles.created_on DESC
LIMIT 10

Our Golang code for the server side of our application might look something like this:

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
)

const (
    DSN = "root:password@/blogdb"
)

var db *sql.DB

type Article struct {
    Title string
    AuthorName string
    PublishedOn string
    Content string
}

func getArticles() []*Article {
    qs := `
SELECT
  articles.title,
  articles.content,
  articles.created_on
  users.name,
FROM articles
JOIN users
 ON articles.created_by = users.id
ORDER BY articles.created_on DESC
LIMIT 10
`
    articles := make([]*Article, 0)
    rows, err := db.Query(qs)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        article := &Article{}
        err := rows.Scan(&article.Title, &article.Content,
                         &article.PublishedOn, &article.AuthorName)
        if err != nil {
            log.Fatal(err)
        }
        articles = append(articles, article)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    return articles
}

func handler(w http.ResponseWriter, r *http.Request) {
    articleTemplate := `%s
-----------------------------------------------------
by %s on %s

%s
`
    articles := getArticles()
    for _, article := range articles {
        fmt.Fprintf(w, articleTemplate, article.Title, article.AuthorName,
                    article.PublishedOn, article.Content)
    }
}

func main() {
    if db, err := sql.Open("mysql", DSN); err != nil {
        log.Fatal(err)
    }
    http.HandleFunc("/", handler)
    http.ListenAndServe(":8080", nil)
}

Note: Clearly, I'm not doing proper error handling and I'm hard-coding things like the DSN that should be pulled from a configuration system in this example code.

The above code works, but it's fragile in the face of inevitable change to the application. What if we want to make the number of articles returned configurable? What if we want to allow users to list only articles by a particular author? In both of these cases, we will need to modify the getArticles() function to modify the SQL query string that it constructs:

func getArticles(numArticles int, byAuthor string) []*Articles {
    // Our collection of query arguments
    qargs := make([]interface{}, 0)
    qs := `
SELECT
  articles.title,
  articles.content,
  articles.created_on
  users.name,
FROM articles
JOIN users
 ON articles.created_by = users.id
`
    if byAuthor != "" {
        qs = qs + "WHERE users.name = ? "
        qargs = append(qargs, byAuthor)
    }
    qs = qs + `ORDER BY articles.created_on DESC
LIMIT ?`
    qargs = append(qargs, numArticles)
    articles := make([]*Article, 0)
    rows, err := db.Query(qs, qargs...)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        article := &Article{}
        err := rows.Scan(&article.Title, &article.Content,
                         &article.PublishedOn, &article.AuthorName)
        if err != nil {
            log.Fatal(err)
        }
        articles = append(articles, article)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    return articles
}

As you can see above, the minor enhancements to our application of allowing a configurable number of articles and filtering by author have already begun to make the getArticles() function unwieldy. The string being generated for our SQL SELECT statement is both more difficult to read and less efficient to construct (due to the multiple string concatenations and memory allocations being performed). Adding more filtering capability would bring yet more conditionals and more string concatenation, leading to ever-increasing complexity and reduced code readability.

sqlb is designed to solve this problem.

Rewriting our application to use sqlb

Let's rewrite our example application above to use the sqlb library instead of manually constructing SQL strings.

We start by initializing sqlb's reflection system in our application's main() entrypoint:

import (
    "github.com/jaypipes/sqlb"
)

var meta sqlb.Meta

func main() {
    if db, err := sql.Open("mysql", DSN); err != nil {
        log.Fatal(err)
    }
    if err := sqlb.Reflect(sqlb.DIALECT_MYSQL, db, &meta); err != nil {
        log.Fatal(err)
    }
}

The sqlb.Meta struct is now populated with information about the database, including metadata about tables, columns, indexes, and relations. You can use this meta information when constructing sqlb Query Expressions.

Let's transform our original getArticles() function -- before we added support for a configurable number of articles and filtering by author -- to use sqlb:


func getArticles() []*Article {
    articles := meta.Table("articles")
    users := meta.Table("users")

    q := sqlb.Select(articles.C("title"), articles.C("content"),
                     articles.C("created_by"), users.C("name"))
    q.Join(users, sqlb.Equal(articles.C("author"), users.C("id")))
    q.OrderBy(articles.C("created_by").Desc())
    q.Limit(10)

    articles := make([]*Article, 0)
    rows, err := sqlb.Query(db, q)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        article := &Article{}
        err := rows.Scan(&article.Title, &article.Content,
                         &article.PublishedOn, &article.AuthorName)
        if err != nil {
            log.Fatal(err)
        }
        articles = append(articles, article)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    return articles
}

The above code ends up producing an identical SQL string as the original code, however the sqlb version uses only a single memory allocation to construct the SQL string (when q.String() is called).

Let's add in functionality to have a configurable number of returned articles and optionally filter for a specific author's articles.

func getArticles(numArticles int, byAuthor string) []*Articles {
    articles := meta.Table("articles")
    users := meta.Table("users")

    q := sqlb.Select(articles.C("title"), articles.C("content"),
                     articles.C("created_by"), users.C("name"))
    q.Join(users, sqlb.Equal(articles.C("author"), users.C("id")))
    if byAuthor != "" {
        q.Where(sqlb.Equal(users.C("name"), byAuthor))
    }
    q.OrderBy(articles.C("created_by").Desc())
    q.Limit(numArticle)

    articles := make([]*Article, 0)
    rows, err := sqlb.Query(db, q)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    for rows.Next() {
        article := &Article{}
        err := rows.Scan(&article.Title, &article.Content,
                         &article.PublishedOn, &article.AuthorName)
        if err != nil {
            log.Fatal(err)
        }
        articles = append(articles, article)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    return articles
}

No more manually constructing and reconstructing strings or tracking query arguments. sqlb handles the SQL string construction for you as well as properly construct the slice of query arguments, allowing you to write custom query code in a more natural and efficient manner.

License

sqlb is licensed under the Apache license version 2. See the COPYING file for more information.

Documentation

Overview

Use and distribution licensed under the Apache license version 2.

See the COPYING file in the root project directory for full text.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Query 

func Query(
	db *sql.DB,
	el types.Element,
) (*sql.Rows, error)

Query accepts a `database/sql` `DB` handle and a `pkg/types.Element` and calls the `databases/sql.DB.Query` method on the SQL string produced by the `Element`.

func QueryContext 

func QueryContext(
	ctx context.Context,
	db *sql.DB,
	el types.Element,
) (*sql.Rows, error)

QueryContext accepts a `databases/sql` `DB` handle and a `pkg/types.Element` and calls the `databases/sql.DB.Query` method on the SQL string produced by the `Element`.

func T 

func T(s *schema.Schema, name string) *identifier.Table

T returns a TableIdentifier of a given name from a supplied Schema

Types

type DeleteQuery 

type DeleteQuery struct {
	// contains filtered or unexported fields
}

func Delete 

func Delete(t *identifier.Table) *DeleteQuery

Delete returns a DeleteQuery given a table that will produce a DELETE SQL statement

func (*DeleteQuery) ArgCount 

func (q *DeleteQuery) ArgCount() int

func (*DeleteQuery) Error 

func (q *DeleteQuery) Error() error

func (*DeleteQuery) IsValid 

func (q *DeleteQuery) IsValid() bool

func (*DeleteQuery) Scan 

func (q *DeleteQuery) Scan(s types.Scanner, b *strings.Builder, qargs []interface{}, idx *int)

func (*DeleteQuery) Size 

func (q *DeleteQuery) Size(s types.Scanner) int

func (*DeleteQuery) Where 

func (q *DeleteQuery) Where(e *expression.Expression) *DeleteQuery

type InsertQuery 

type InsertQuery struct {
	// contains filtered or unexported fields
}

func Insert 

func Insert(t *identifier.Table, values map[string]interface{}) *InsertQuery

Given a table and a map of column name to value for that column to insert, returns an InsertQuery that will produce an INSERT SQL statement

func (*InsertQuery) ArgCount 

func (q *InsertQuery) ArgCount() int

func (*InsertQuery) Error 

func (q *InsertQuery) Error() error

func (*InsertQuery) IsValid 

func (q *InsertQuery) IsValid() bool

func (*InsertQuery) Scan 

func (q *InsertQuery) Scan(s types.Scanner, b *strings.Builder, qargs []interface{}, idx *int)

func (*InsertQuery) Size 

func (q *InsertQuery) Size(s types.Scanner) int

type SelectQuery 

type SelectQuery struct {
	// contains filtered or unexported fields
}

func Select 

func Select(items ...interface{}) *SelectQuery

func (*SelectQuery) ArgCount 

func (q *SelectQuery) ArgCount() int

func (*SelectQuery) As 

func (q *SelectQuery) As(alias string) *SelectQuery

Returns a pointer to a new SelectQuery that has aliased its inner selection to the supplied name

func (*SelectQuery) C 

func (q *SelectQuery) C(name string) types.Projection

Returns the projection of the underlying SelectStatement that matches the name provided

func (*SelectQuery) Error 

func (q *SelectQuery) Error() error

func (*SelectQuery) GroupBy 

func (q *SelectQuery) GroupBy(cols ...types.Projection) *SelectQuery

func (*SelectQuery) Having 

func (q *SelectQuery) Having(e *expression.Expression) *SelectQuery

func (*SelectQuery) IsValid 

func (q *SelectQuery) IsValid() bool

func (*SelectQuery) Join 

func (q *SelectQuery) Join(right interface{}, on *expression.Expression) *SelectQuery

func (*SelectQuery) Limit 

func (q *SelectQuery) Limit(limit int) *SelectQuery

func (*SelectQuery) LimitWithOffset 

func (q *SelectQuery) LimitWithOffset(limit int, offset int) *SelectQuery

func (*SelectQuery) OrderBy 

func (q *SelectQuery) OrderBy(scols ...types.Sortable) *SelectQuery

func (*SelectQuery) OuterJoin 

func (q *SelectQuery) OuterJoin(right interface{}, on *expression.Expression) *SelectQuery

func (*SelectQuery) Scan 

func (q *SelectQuery) Scan(s types.Scanner, b *strings.Builder, qargs []interface{}, idx *int)

func (*SelectQuery) Size 

func (q *SelectQuery) Size(s types.Scanner) int

func (*SelectQuery) Where 

func (q *SelectQuery) Where(e *expression.Expression) *SelectQuery

type UpdateQuery 

type UpdateQuery struct {
	// contains filtered or unexported fields
}

func Update 

func Update(t *identifier.Table, values map[string]interface{}) *UpdateQuery

Given a table and a map of column name to value for that column to update, returns an UpdateQuery that will produce an UPDATE SQL statement

func (*UpdateQuery) ArgCount 

func (q *UpdateQuery) ArgCount() int

func (*UpdateQuery) Error 

func (q *UpdateQuery) Error() error

func (*UpdateQuery) IsValid 

func (q *UpdateQuery) IsValid() bool

func (*UpdateQuery) Scan 

func (q *UpdateQuery) Scan(s types.Scanner, b *strings.Builder, qargs []interface{}, idx *int)

func (*UpdateQuery) Size 

func (q *UpdateQuery) Size(s types.Scanner) int

func (*UpdateQuery) Where 

func (q *UpdateQuery) Where(e *expression.Expression) *UpdateQuery

Source Files

View all Source files

Directories

Path Synopsis
pkg
errors
grammar
grammar/clause
grammar/element
grammar/expression
grammar/function
grammar/identifier
grammar/sortcolumn
grammar/statement
scanner
schema
testutil
types

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.