sqr

README

go-sql-sqr - SQL Query Repository for Go

This library enables the use of centralized storage for all SQL queries used in an application. You can either choose to load all queries into a repository, or load them from a filesystem as necessary.

The main intention is to embed a directory structure into the binary as embed.FS, which can then used in the application.

---

Basics

Add the package to your project

go get git.flexabyte.io/flexabyte/go-kit

Import

Next, you can manually add the import statement to your .go-file, or have it added automatically when using it.

import git.flexabyte.io/flexabyte/go-kit/sqr

Embed assets containing queries.

[!IMPORTANT]
The root folder embedded in the application cannot have nested directories.
This means that the directory structure is limited to 1 level of collections, each containing a set of text files with a .sql extension.

Files with another extension will fail to load!

Let's assume to following directory structure in an embedded filesystem:

/assets
|-- /queries
    |-- collection1
    |   |-- create.sql
    |   |-- read.sql
    |   |-- update.sql
    |   |-- delete.sql
    |-- collection2
        |-- list.sql

You can now embed the statement files as follows:

//go:embed assets/queries/*
var f embed.FS

With repository

Create a new repository

// Create query repository from embedded files
var r *sqr.Repository
if r, err = sqr.NewFromFs(f, "assets/queries"); err != nil {
    panic(err)
}

Load a query from the repository

Now the repository has been initialized, we can get a query from a collection:

var query string
if query, err = r.Get("collection1", "create"); err != nil {
    panic(err)
}
fmt.Println("Query:", query)

Without repository

If you don't want to initialize a repository, but rather choose to load SQL queries straight from the filesystem, you can do so as follows:

var query string
if query, err = sqr.LoadQueryFromFs(f, "assets/queries", "collection2", "list"); err != nil {
    panic(err)
}

Prepared statements

We also provide the means to create prepared statements for the queries, either with or without using a repository, as long as a Preparer is passed into the functions.

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

For example:

• *sql.Db
• *sql.Tx

func Prepare[T Preparer](t T, r *Repository, collectionName, queryName string) (*sql.Stmt, error) {}
func PrepareFromFs[T Preparer](t T, f fs.FS, rootPath, collectionName, queryName string) (*sql.Stmt, error) {}

With repository

var createStmt *sql.Stmt
if createStmt, err = sqr.Prepare(db, r, "collection1", "create"); err != nil {
    panic(err)
}

Without repository

var createStmt *sql.Stmt
if createStmt, err = sqr.PrepareFromFs(db, f, "assets/queries", "collection1", "create"); err != nil {
    panic(err)
}

Examples

Two examples are available:

• embbededSqlWithRepository
• embeddedSqlWithoutRepository

Documentation

Overview

Package queryrepo enables the use of centralized storage for all SQL queries used in an application.

Index

• func LoadQueryFromFs(f fs.FS, rootPath, collectionName, queryName string) (string, error)
• func Prepare[T Preparer](t T, r *Repository, collectionName, queryName string) (*sql.Stmt, error)
• func PrepareContext[T Preparer](ctx context.Context, t T, r *Repository, collectionName, queryName string) (*sql.Stmt, error)
• func PrepareFromFs[T Preparer](t T, f fs.FS, rootPath, collectionName, queryName string) (*sql.Stmt, error)
• func PrepareFromFsContext[T Preparer](ctx context.Context, t T, f fs.FS, rootPath, collectionName, queryName string) (*sql.Stmt, error)
• type Preparer
• type Repository
  • func NewFromFs(f fs.FS, rootPath string) (*Repository, error)
  • func (r *Repository) DbPrepare(db *sql.DB, collectionName, queryName string) (*sql.Stmt, error)
  • func (r *Repository) DbPrepareContext(ctx context.Context, db *sql.DB, collectionName, queryName string) (*sql.Stmt, error)
  • func (r *Repository) Get(collectionName, queryName string) (string, error)
  • func (r *Repository) TxPrepare(tx *sql.Tx, collectionName, queryName string) (*sql.Stmt, error)
  • func (r *Repository) TxPrepareContext(ctx context.Context, tx *sql.Tx, collectionName, queryName string) (*sql.Stmt, error)

Functions

func LoadQueryFromFs

func LoadQueryFromFs(f fs.FS, rootPath, collectionName, queryName string) (string, error)

LoadQueryFromFs retrieves a query from a filesystem. It needs the root path to start the search from, as well as a collection name and a query name. The collection name equals to a direct directory name in the root path. The query name is the file name (without extension) to load the contents from. It returns and empty string and an error if the file cannot be found.

func Prepare

func Prepare[T Preparer](t T, r *Repository, collectionName, queryName string) (*sql.Stmt, error)

Prepare creates a prepared statement for the supplied Preparer by looking up a query in the supplied repository. It returns an nil pointer and an error if either the query cannot be found in the supplied repository, or the statement preparation fails.

func PrepareContext

func PrepareContext[T Preparer](ctx context.Context, t T, r *Repository, collectionName, queryName string) (*sql.Stmt, error)

Prepare creates a prepared statement for the supplied Preparer by looking up a query in the supplied repository using a context. It returns an nil pointer and an error if either the query cannot be found in the supplied repository, or the statement preparation fails.

func PrepareFromFs

func PrepareFromFs[T Preparer](t T, f fs.FS, rootPath, collectionName, queryName string) (*sql.Stmt, error)

PrepareFromFs creates a prepared statement for the supplied Preparer by looking up a query in the supplied filesystem. It returns an nil pointer and an error if either the query cannot be found in the supplied filesystem, or the statement preparation fails.

func PrepareFromFsContext

func PrepareFromFsContext[T Preparer](ctx context.Context, t T, f fs.FS, rootPath, collectionName, queryName string) (*sql.Stmt, error)

PrepareFromFs creates a prepared statement for the supplied Preparer by looking up a query in the supplied filesystem using a context. It returns an nil pointer and an error if either the query cannot be found in the supplied filesystem, or the statement preparation fails.

Types

type Preparer

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

Preparer defines the interface to create a prepared statement.

type Repository

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

A Repository stores multiple collections of queries in a map for later use. Queries can either be retrieved by their name, or be used to create a prepared statement.

func NewFromFs

func NewFromFs(f fs.FS, rootPath string) (*Repository, error)

NewFromFs creates a new repository using a filesystem. It takes a filesystem and a root path to start loading files from and returns an error if files cannot be loaded.

func (*Repository) DbPrepare

func (r *Repository) DbPrepare(db *sql.DB, collectionName, queryName string) (*sql.Stmt, error)

DbPrepare creates a prepared statement for the supplied database handle. It takes a collection name and query name to look up the query to create the prepared statement.

func (*Repository) DbPrepareContext

func (r *Repository) DbPrepareContext(ctx context.Context, db *sql.DB, collectionName, queryName string) (*sql.Stmt, error)

DbPrepareContext creates a prepared statement for the supplied database handle using a context. It takes a collection name and query name to look up the query to create the prepared statement.

func (*Repository) Get

func (r *Repository) Get(collectionName, queryName string) (string, error)

Get retrieves the supplied query from the repository. It takes a collection name and a query name to perform the lookup and returns an empty string and an error if the query cannot be found in the collection.

func (*Repository) TxPrepare

func (r *Repository) TxPrepare(tx *sql.Tx, collectionName, queryName string) (*sql.Stmt, error)

TxPrepare creates a prepared statement for the supplied in-progress database transaction. It takes a collection name and query name to look up the query to create the prepared statement.

func (*Repository) TxPrepareContext

func (r *Repository) TxPrepareContext(ctx context.Context, tx *sql.Tx, collectionName, queryName string) (*sql.Stmt, error)

TxPrepare creates a prepared statement for the supplied in-progress database transaction using a context. It takes a collection name and query name to look up the query to create the prepared statement.