sqlset

README

SQLSet

SQLSet is a simple Go library that provides a convenient way to manage and access SQL queries stored in .sql files. It allows you to separate your SQL code from your Go code, making it cleaner and more maintainable.

Features

• Decouple SQL from Go code: Keep your SQL queries in separate .sql files.
• Easy to use: A simple API to get your queries.
• Flexible: Works with any fs.FS, including embed.FS for bundling queries with your application.
• Query Metadata: Associate names and descriptions with your query sets.
• Organized: Structure your queries into logical sets.

Installation

go get github.com/theprogrammer67/sqlset

Usage

1. Create your SQL files.

Create a directory (e.g., queries) and add your .sql files. Each file represents a "query set". The name of the file (without the .sql extension) becomes the query set ID.

Inside each file, define your queries using a special --META comment for metadata and --SQL: comments to mark the beginning of each query.

End the query or metadata block with a special comment --end

queries/users.sql

--META
{
    "name": "User Queries",
    "description": "A set of queries for user management."
}
--end

--SQL:GetUserByID
SELECT id, name, email FROM users WHERE id = ?;
--end

--SQL:CreateUser
INSERT INTO users (name, email) VALUES (?, ?);
--end

1. Embed and load the queries in your Go application.

Use Go's embed package to bundle the SQL files directly into your application binary.

package main

import (
	"embed"
	"fmt"
	"log"

	"github.com/stoi/sqlset"
)

//go:embed queries
var queriesFS embed.FS

func main() {
	// Create a new SQLSet from the embedded filesystem.
	// We pass "queries" as the subdirectory to look into.
	sqlSet, err := sqlset.New(queriesFS)
	if err != nil {
		log.Fatalf("Failed to create SQL set: %v", err)
	}

	// Get a specific query
	query, err := sqlSet.Get("users", "GetUserByID")
	if err != nil {
		log.Fatalf("Failed to get query: %v", err)
	}
	fmt.Println("GetUserByID query:", query)

	// Or, panic if the query is not found
	query = sqlSet.MustGet("users", "CreateUser")
	fmt.Println("CreateUser query:", query)

    // You can also retrieve metadata for all query sets
    metas := sqlSet.GetSetsMetas()
    for _, meta := range metas {
        fmt.Printf("Set ID: %s, Name: %s, Description: %s\n", meta.ID, meta.Name, meta.Description)
    }

    // You can get a list of all query IDs in a specific set
    queryIDs, err := sqlSet.GetQueryIDs("users")
    if err != nil {
        log.Fatalf("Failed to get query IDs: %v", err)
    }
    fmt.Println("Query IDs in 'users' set:", queryIDs) // Output: [CreateUser GetUserByID] (sorted)
}

File Format Specification

• Metadata Block (Optional):

  • Starts with --META.
  • Followed by a JSON object containing id (string, optional), name (string, optional) and description (string, optional).
  • There can be only one metadata block per file.
  • End with --end.

• Query Block (Required):

  • Starts with --SQL:<query_id>, where <query_id> is the unique identifier for the query within the file.
  • The SQL statement follows on the next lines.
  • All text until the next --end block is considered part of the query.

Contributing

Contributions are welcome! If you find a bug or have a feature request, please open an issue. If you want to contribute code, please open a pull request.

1. Fork the repository.
2. Create a new branch (git checkout -b feature/your-feature).
3. Make your changes.
4. Commit your changes (git commit -am 'Add some feature').
5. Push to the branch (git push origin feature/your-feature).
6. Create a new Pull Request.

License

This project is licensed under the MIT License - see the LICENSE file for details.

Documentation

Overview

Package sqlset is a way to store SQL queries separated from the go code. Query sets are stored in the .sql files, every filename without extension is an SQL set ID. Every file contains queries, marked with query IDs using special syntax, see `testdata/valid/*.sql` files for examples. Also file may contain JSON-encoded query set metadata with name and description.

Index

• Variables
• type QuerySet
  • func (qs *QuerySet) GetMeta() QuerySetMeta
• type QuerySetMeta
• type SQLQueriesProvider
• type SQLSet
  • func New(fsys fs.FS) (*SQLSet, error)
  • func (s *SQLSet) Get(setID string, queryID string) (string, error)
  • func (s *SQLSet) GetQueryIDs(setID string) ([]string, error)
  • func (s *SQLSet) GetSetsMetas() []QuerySetMeta
  • func (s *SQLSet) MustGet(setID string, queryID string) string
• type SQLSetsProvider

Variables

var (
	// ErrNotFound is the base error for when an item is not found.
	ErrNotFound = errors.New("not found")
	// ErrQuerySetNotFound indicates that a specific query set was not found.
	ErrQuerySetNotFound = fmt.Errorf("query set %w", ErrNotFound)
	// ErrQueryNotFound indicates that a specific query was not found within a set.
	ErrQueryNotFound = fmt.Errorf("query %w", ErrNotFound)
	// ErrInvalidSyntax is returned when the parser encounters a syntax error in a .sql file.
	ErrInvalidSyntax = errors.New("invalid SQLSetList syntax")
	// ErrMaxLineLenExceeded is returned when a line in a .sql file is too long,
	// which may indicate a corrupted file.
	ErrMaxLineLenExceeded = errors.New("line too long, possible line corruption")
)

Types

type QuerySet

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

QuerySet represents a single set of queries, usually from a single .sql file.

func (*QuerySet) GetMeta

func (qs *QuerySet) GetMeta() QuerySetMeta

GetMeta returns the metadata associated with the query set.

type QuerySetMeta

type QuerySetMeta struct {
	// ID is the unique identifier for the set, derived from the filename.
	ID string `json:"id"`
	// Name is a human-readable name for the query set, from the metadata block.
	Name string `json:"name"`
	// Description provides more details about the query set, from the metadata block.
	Description string `json:"description,omitempty"`
}

QuerySetMeta holds the metadata for a query set.

type SQLQueriesProvider

type SQLQueriesProvider interface {
	// Get returns a query by set ID and query ID.
	// If the set or query is not found, it returns an error.
	Get(setID string, queryID string) (string, error)
	// MustGet returns a query by set ID and query ID.
	// It panics if the set or query is not found.
	MustGet(setID string, queryID string) string
}

SQLQueriesProvider is the interface for getting SQL queries.

type SQLSet

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

SQLSet is a container for multiple query sets, organized by set ID. It provides methods to access SQL queries and metadata. Use New to create a new instance.

func New

func New(fsys fs.FS) (*SQLSet, error)

New creates a new SQLSet by walking the directory tree of the provided fsys. It parses all .sql files it finds and adds them to the SQLSet. The walk starts from the root of the fsys. If you are using embed.FS and your queries are in a subdirectory, you should create a sub-filesystem using fs.Sub.

Example with embed.FS:

//go:embed queries
var queriesFS embed.FS

sqlSet, err := sqlset.New(queriesFS)

func (*SQLSet) Get

func (s *SQLSet) Get(setID string, queryID string) (string, error)

Get retrieves a specific SQL query by its set ID and query ID. It returns an error if the query set or the query itself cannot be found.

func (*SQLSet) GetQueryIDs

func (s *SQLSet) GetQueryIDs(setID string) ([]string, error)

GetQueryIDs returns a sorted slice of all query IDs within a specific query set.

func (*SQLSet) GetSetsMetas

func (s *SQLSet) GetSetsMetas() []QuerySetMeta

GetSetsMetas returns a slice of metadata for all the query sets loaded. The order of the returned slice is not guaranteed.

func (*SQLSet) MustGet

func (s *SQLSet) MustGet(setID string, queryID string) string

MustGet is like Get but panics if the query set or query is not found. This is useful for cases where the query is expected to exist and its absence is a critical error.

type SQLSetsProvider

type SQLSetsProvider interface {
	// GetSetsMetas returns metadata for all registered query sets.
	GetSetsMetas() []QuerySetMeta
	// GetQueryIDs returns a slice of all query IDs.
	GetQueryIDs(setID string) ([]string, error)
}

SQLSetsProvider is the interface for getting information about query sets.