README
¶
dotsql
A Golang library for using SQL.
It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and use it with ease.
Dotsql is heavily inspired by yesql.
Installation
$ go get github.com/qustavo/dotsql
Usage
First of all, you need to define queries inside your sql file:
-- name: create-users-table
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255),
email VARCHAR(255)
);
-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)
-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?
-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1
--name: drop-users-table
DROP TABLE users
Notice that every query has a name tag (--name:<some name>),
this is needed to be able to uniquely identify each query
inside dotsql.
With your sql file prepared, you can load it up and start utilizing your queries:
// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")
// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")
// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "main@example.com")
rows, err := dot.Query(db, "find-users-by-email", "main@example.com")
row, err := dot.QueryRow(db, "find-one-user-by-email", "user@example.com")
stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()
You can also merge multiple dotsql instances created from different sql file inputs:
dot1, err := dotsql.LoadFromFile("queries1.sql")
dot2, err := dotsql.LoadFromFile("queries2.sql")
dot := dotsql.Merge(dot1, dot2)
Text Interpolation
text/template-style text interpolation is supported.
To use, call .WithData(any) on your dotsql instance to
create a new instance which passes those values into the templating library.
-- name: count-users
SELECT count(*) FROM users {{if .exclude_deleted}}WHERE deleted IS NULL{{end}}
dotsql.WithData(map[string]any{"exclude_deleted": true}).Query(db, "count-users")
Embeding
To avoid distributing sql files alongside the binary file, you will need to use tools like
gotic to embed / pack everything into one file.
SQLX
Documentation
¶
Overview ¶
Package dotsql provides a way to separate your code from SQL queries.
It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and use it with ease.
For more usage examples see https://github.com/qustavo/dotsql
Index ¶
- type DotSql
- func (d DotSql) Exec(db Execer, name string, args ...interface{}) (sql.Result, error)
- func (d DotSql) ExecContext(ctx context.Context, db ExecerContext, name string, args ...interface{}) (sql.Result, error)
- func (d DotSql) Prepare(db Preparer, name string) (*sql.Stmt, error)
- func (d DotSql) PrepareContext(ctx context.Context, db PreparerContext, name string) (*sql.Stmt, error)
- func (d DotSql) Query(db Queryer, name string, args ...interface{}) (*sql.Rows, error)
- func (d DotSql) QueryContext(ctx context.Context, db QueryerContext, name string, args ...interface{}) (*sql.Rows, error)
- func (d DotSql) QueryMap() map[string]*template.Template
- func (d DotSql) QueryRow(db QueryRower, name string, args ...interface{}) (*sql.Row, error)
- func (d DotSql) QueryRowContext(ctx context.Context, db QueryRowerContext, name string, args ...interface{}) (*sql.Row, error)
- func (d DotSql) Raw(name string) (string, error)
- func (d DotSql) WithData(data any) DotSql
- type Execer
- type ExecerContext
- type Preparer
- type PreparerContext
- type QueryRower
- type QueryRowerContext
- type Queryer
- type QueryerContext
- type Scanner
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type DotSql ¶
type DotSql struct {
// contains filtered or unexported fields
}
DotSql represents a dotSQL queries holder.
func LoadFromFile ¶
LoadFromFile imports SQL queries from the file.
Example ¶
package main
import (
"database/sql"
"log"
"github.com/qustavo/dotsql"
)
func main() {
db, err := sql.Open("sqlite3", ":memory:")
if err != nil {
panic(err)
}
dot, err := dotsql.LoadFromFile("queries.sql")
if err != nil {
panic(err)
}
/* queries.sql looks like:
-- name: create-users-table
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR(255),
email VARCHAR(255)
);
-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)
-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?
-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1
--name: drop-users-table
DROP TABLE users
*/
// Exec
if _, err := dot.Exec(db, "create-users-table"); err != nil {
panic(err)
}
if _, err := dot.Exec(db, "create-user", "user@example.com"); err != nil {
panic(err)
}
// Query
rows, err := dot.Query(db, "find-users-by-email", "user@example.com")
if err != nil {
panic(err)
}
defer rows.Close()
for rows.Next() {
var id int
var name, email string
err = rows.Scan(&id, &name, &email)
if err != nil {
panic(err)
}
log.Println(email)
}
// QueryRow
row, err := dot.QueryRow(db, "find-one-user-by-email", "user@example.com")
if err != nil {
panic(err)
}
var id int
var name, email string
row.Scan(&id, &name, &email)
log.Println(email)
}
Output:
func LoadFromString ¶
LoadFromString imports SQL queries from the string.
func Merge ¶
Merge takes one or more *DotSql and merge its queries It's in-order, so the last source will override queries with the same name in the previous arguments if any.
func (DotSql) ExecContext ¶
func (d DotSql) ExecContext(ctx context.Context, db ExecerContext, name string, args ...interface{}) (sql.Result, error)
ExecContext is a wrapper for database/sql's ExecContext(), using dotsql named query.
func (DotSql) Prepare ¶
Prepare is a wrapper for database/sql's Prepare(), using dotsql named query.
func (DotSql) PrepareContext ¶
func (d DotSql) PrepareContext(ctx context.Context, db PreparerContext, name string) (*sql.Stmt, error)
PrepareContext is a wrapper for database/sql's PrepareContext(), using dotsql named query.
func (DotSql) QueryContext ¶
func (d DotSql) QueryContext(ctx context.Context, db QueryerContext, name string, args ...interface{}) (*sql.Rows, error)
QueryContext is a wrapper for database/sql's QueryContext(), using dotsql named query.
func (DotSql) QueryRow ¶
QueryRow is a wrapper for database/sql's QueryRow(), using dotsql named query.
func (DotSql) QueryRowContext ¶
func (d DotSql) QueryRowContext(ctx context.Context, db QueryRowerContext, name string, args ...interface{}) (*sql.Row, error)
QueryRowContext is a wrapper for database/sql's QueryRowContext(), using dotsql named query.
type ExecerContext ¶
type ExecerContext interface {
ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}
ExecerContext is an interface used by ExecContext.
type PreparerContext ¶
type PreparerContext interface {
PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}
PreparerContext is an interface used by PrepareContext.
type QueryRower ¶
QueryRower is an interface used by QueryRow.
type QueryRowerContext ¶
type QueryRowerContext interface {
QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row
}
QueryRowerContext is an interface used by QueryRowContext.
Source Files