dotsql

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/gchaincl/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)

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.

TODO

• Enable text interpolation inside queries using text/template
• sqlx integration

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/gchaincl/dotsql

Index

• type DotSql
  • func Load(r io.Reader) (*DotSql, error)
  • func LoadFromFile(sqlFile string) (*DotSql, error)
  • func LoadFromString(sql string) (*DotSql, error)
  • func Merge(dots ...*DotSql) *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]string
  • 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)
• type Execer
• type ExecerContext
• type Preparer
• type PreparerContext
• type QueryRower
• type QueryRowerContext
• type Queryer
• type QueryerContext
• type Scanner
  • func (s *Scanner) Run(io *bufio.Scanner) map[string]string

Examples

• LoadFromFile

Types

type DotSql

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

DotSql represents a dotSQL queries holder.

func Load

func Load(r io.Reader) (*DotSql, error)

Load imports sql queries from any io.Reader.

func LoadFromFile

func LoadFromFile(sqlFile string) (*DotSql, error)

LoadFromFile imports SQL queries from the file.

Example

package main

import (
	"database/sql"
	"log"

	"github.com/gchaincl/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-user-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)
}

func LoadFromString

func LoadFromString(sql string) (*DotSql, error)

LoadFromString imports SQL queries from the string.

func Merge

func Merge(dots ...*DotSql) *DotSql

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) Exec

func (d DotSql) Exec(db Execer, name string, args ...interface{}) (sql.Result, error)

Exec is a wrapper for database/sql's Exec(), using dotsql named query.

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

func (d DotSql) Prepare(db Preparer, name string) (*sql.Stmt, error)

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) Query

func (d DotSql) Query(db Queryer, name string, args ...interface{}) (*sql.Rows, error)

Query is a wrapper for database/sql's Query(), 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) QueryMap

func (d DotSql) QueryMap() map[string]string

QueryMap returns a map[string]string of loaded queries

func (DotSql) QueryRow

func (d DotSql) QueryRow(db QueryRower, name string, args ...interface{}) (*sql.Row, error)

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.

func (DotSql) Raw

func (d DotSql) Raw(name string) (string, error)

Raw returns the query, everything after the --name tag

type Execer

type Execer interface {
	Exec(query string, args ...interface{}) (sql.Result, error)
}

Execer is an interface used by Exec.

type ExecerContext

type ExecerContext interface {
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

ExecerContext is an interface used by ExecContext.

type Preparer

type Preparer interface {
	Prepare(query string) (*sql.Stmt, error)
}

Preparer is an interface used by Prepare.

type PreparerContext

type PreparerContext interface {
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
}

PreparerContext is an interface used by PrepareContext.

type QueryRower

type QueryRower interface {
	QueryRow(query string, args ...interface{}) *sql.Row
}

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.

type Queryer

type Queryer interface {
	Query(query string, args ...interface{}) (*sql.Rows, error)
}

Queryer is an interface used by Query.

type QueryerContext

type QueryerContext interface {
	QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error)
}

QueryerContext is an interface used by QueryContext.

type Scanner

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

func (*Scanner) Run

func (s *Scanner) Run(io *bufio.Scanner) map[string]string