• Stars
    star
    6,755
  • Rank 5,848 (Top 0.2 %)
  • Language
    Go
  • License
    Other
  • Created almost 11 years ago
  • Updated 7 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Fluent SQL generation for golang

Stability: Maintenance

Squirrel is "complete".

Bug fixes will still be merged (slowly). Bug reports are welcome, but I will not necessarily respond to them. If another fork (or substantially similar project) actively improves on what Squirrel does, let me know and I may link to it here.

Squirrel - fluent SQL generator for Go

import "github.com/Masterminds/squirrel"

GoDoc Build Status

Squirrel is not an ORM. For an application of Squirrel, check out structable, a table-struct mapper

Squirrel helps you build SQL queries from composable parts:

import sq "github.com/Masterminds/squirrel"

users := sq.Select("*").From("users").Join("emails USING (email_id)")

active := users.Where(sq.Eq{"deleted_at": nil})

sql, args, err := active.ToSql()

sql == "SELECT * FROM users JOIN emails USING (email_id) WHERE deleted_at IS NULL"
sql, args, err := sq.
    Insert("users").Columns("name", "age").
    Values("moe", 13).Values("larry", sq.Expr("? + 5", 12)).
    ToSql()

sql == "INSERT INTO users (name,age) VALUES (?,?),(?,? + 5)"

Squirrel can also execute queries directly:

stooges := users.Where(sq.Eq{"username": []string{"moe", "larry", "curly", "shemp"}})
three_stooges := stooges.Limit(3)
rows, err := three_stooges.RunWith(db).Query()

// Behaves like:
rows, err := db.Query("SELECT * FROM users WHERE username IN (?,?,?,?) LIMIT 3",
                      "moe", "larry", "curly", "shemp")

Squirrel makes conditional query building a breeze:

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

Squirrel wants to make your life easier:

// StmtCache caches Prepared Stmts for you
dbCache := sq.NewStmtCache(db)

// StatementBuilder keeps your syntax neat
mydb := sq.StatementBuilder.RunWith(dbCache)
select_users := mydb.Select("*").From("users")

Squirrel loves PostgreSQL:

psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)

// You use question marks for placeholders...
sql, _, _ := psql.Select("*").From("elephants").Where("name IN (?,?)", "Dumbo", "Verna").ToSql()

/// ...squirrel replaces them using PlaceholderFormat.
sql == "SELECT * FROM elephants WHERE name IN ($1,$2)"


/// You can retrieve id ...
query := sq.Insert("nodes").
    Columns("uuid", "type", "data").
    Values(node.Uuid, node.Type, node.Data).
    Suffix("RETURNING \"id\"").
    RunWith(m.db).
    PlaceholderFormat(sq.Dollar)

query.QueryRow().Scan(&node.id)

You can escape question marks by inserting two question marks:

SELECT * FROM nodes WHERE meta->'format' ??| array[?,?]

will generate with the Dollar Placeholder:

SELECT * FROM nodes WHERE meta->'format' ?| array[$1,$2]

FAQ

  • How can I build an IN query on composite keys / tuples, e.g. WHERE (col1, col2) IN ((1,2),(3,4))? (#104)

    Squirrel does not explicitly support tuples, but you can get the same effect with e.g.:

    sq.Or{
      sq.Eq{"col1": 1, "col2": 2},
      sq.Eq{"col1": 3, "col2": 4}}
    WHERE (col1 = 1 AND col2 = 2) OR (col1 = 3 AND col2 = 4)

    (which should produce the same query plan as the tuple version)

  • Why doesn't Eq{"mynumber": []uint8{1,2,3}} turn into an IN query? (#114)

    Values of type []byte are handled specially by database/sql. In Go, byte is just an alias of uint8, so there is no way to distinguish []uint8 from []byte.

  • Some features are poorly documented!

    This isn't a frequent complaints section!

  • Some features are poorly documented?

    Yes. The tests should be considered a part of the documentation; take a look at those for ideas on how to express more complex queries.

License

Squirrel is released under the MIT License.

More Repositories

1

glide

Package Management for Golang
Go
8,155
star
2

sprig

Useful template functions for Go templates.
Go
4,108
star
3

html5-php

An HTML5 parser and serializer for PHP.
HTML
1,539
star
4

semver

Work with Semantic Versions in Go
Go
1,188
star
5

go-in-practice

Repository for Manning Publications Go in Practice
Go
353
star
6

structable

Golang struct-to-table database mapper
Go
293
star
7

vcs

VCS Repo management through a common interface in Go
Go
194
star
8

learning-helm

HTML
108
star
9

goutils

GoUtils is a Go implementation of some string manipulation libraries of Apache Commons. This is an open source project aimed at providing Go users with utility functions to manipulate strings in various ways.
Go
98
star
10

cookoo

A chain-of-command framework written in Go
Go
60
star
11

vert

Command line version testing: Compare versions at the CLI for use in shell scripts and make files.
Go
59
star
12

go-fileserver

A Go Fileserver where you can specify custom NotFound and Error response handlers.
Go
48
star
13

glide-report

Go
28
star
14

stability

Share the stability of a project
28
star
15

Fortissimo

A Chain Of Command (CoCo) framework for PHP.
PHP
25
star
16

log-go

A Golang logging interface with some reference implementations.
Go
21
star
17

kitt

Make your CLI apps pretty
Go
16
star
18

engine

An HTML theme engine for Go
Go
15
star
19

godir

Go Path Tool: A utility for working with Go and filesystem paths.
Go
12
star
20

codl

Codl: The Cookoo Domain Language
Go
11
star
21

Fortissimo-CLI-Base

A skeleton for creating Fortissimo CLI applications.
PHP
11
star
22

glide.sh

The website for Glide
HTML
10
star
23

convert

A mathematical conversion library.
Go
8
star
24

rmvcsdir

remove version control directories
Go
7
star
25

formenc

Decode form values using a Go unmarshal
Go
6
star
26

httputil

Framework-free HTTP utilities
Go
4
star
27

cookoo-cli-tutorial

A Git-based tutorial for using Cookoo to write command line apps.
Go
3
star
28

Villain

A CMS to be feared
PHP
3
star
29

cookoo-web-tutorial

A Git-based dive into building Cookoo web apps
Shell
3
star
30

Fortissimo-CLI

A CLI Setup for Fortissimo.
PHP
2
star
31

glide-ppa

Shell
2
star
32

Fortissimo-Base

A base setup for Fortissimo based site.
PHP
2
star
33

Fortissimo-Twig

Integration between Fortissimo and Twig
PHP
1
star
34

fortissimo-commons

Common commands for the Fortissimo framework.
PHP
1
star