README
¶
uri2sql
A Go library that safely converts URI query parameters into SQL queries, designed for building flexible REST APIs with database backends.
Installation
go get codeberg.org/ohzqq/usql
Description
uri2sql transforms query parameters in HTTP requests into safe, parameterized SQL queries. It provides a robust way to expose database querying capabilities through REST APIs without risking SQL injection.
Features
- Converts query parameters to SQL with proper parameter binding
- Supports field selection with JSONB navigation
- Filtering with complex conditions and operators
- Sorting on multiple fields
- Grouping by multiple fields
- Pagination with limit and skip
- Full-text search
- Type casting
- Field aliasing
- Protection against SQL injection
Usage
package main
import (
"fmt"
"net/url"
"github.com/dsl400/uri2sql"
)
func main() {
// Parse a URL with query parameters
r, _ := url.Parse("/books?fields=title,author&cond=price>={10}&sort=!published_date&limit=20")
// Create a new uri2sql instance
u := uri2sql.NewUri2Sql(&uri2sql.Config{})
// Parse the URL into an SQL query
query, err := u.Parse("books", r, nil)
if err != nil {
fmt.Printf("Error: %v\n", err)
return
}
// Get the resulting SQL and arguments
sql := query.SQL()
args := query.Args()
fmt.Printf("SQL: %s\n", sql)
fmt.Printf("Args: %v\n", args)
// Output:
// SQL: SELECT title, author FROM books WHERE price >= $1 ORDER BY published_date DESC LIMIT 20
// Args: [10]
}
Query Parameter Documentation
Fields
Select which fields to return:
/books?fields=title,author
Select fields from JSONB data:
/books?fields=metadata.tags.[0],metadata.publisher
Alias fields:
/books?fields=title:book_title,author:writer
Cast field types:
/books?fields=integer(year),numeric(price)
Conditions
Filter results with WHERE conditions:
/books?cond=price>={10} and author={Hemingway}
Support for multiple operators:
/books?cond=price<={50} or title like {%fantasy%}
Nested conditions:
/books?cond=(price>={10} and rating<={5}) or author={Hemingway}
Sorting
Sort by fields (ascending by default):
/books?sort=title,published_date
Sort in descending order with !:
/books?sort=!price,title
Grouping
Group results by fields:
/books?group=author,genre
Pagination
Limit the number of results:
/books?limit=20
Skip a number of results:
/books?skip=40
Search
Perform a full-text search:
/books?search=fantasy
Configuration
Configure the library behavior:
config := &uri2sql.Config{
MaxLimit: 500, // Maximum allowed limit
DefaultLimit: 50, // Default limit if not specified
DefaultJsonbColumn: "data", // Default JSONB column name
}
u := uri2sql.NewUri2Sql(config)
Custom Parsers
Register custom parsers for special query parameters:
u := uri2sql.NewUri2Sql(config,
uri2sql.WithCustomParser("custom_param", myCustomParser))
License
This project is licensed under the GNU General Public License v3.0 - see the LICENSE file for details.
Documentation
¶
Index ¶
- Variables
- func QueryParamNames() []string
- type Builder
- type BuilderOpts
- type Opts
- type Params
- func (u *Params) AddAlias(f ...string)
- func (u *Params) AddArg(v ...any)
- func (u *Params) AddField(f ...string)
- func (s *Params) EncodeValues() url.Values
- func (u *Params) GetAliasses() []string
- func (u *Params) GetArgs() []any
- func (u *Params) GetDefaultJsonbColumn() string
- func (u *Params) GetFields() []string
- func (u *Params) GetMappedField(n string) string
- func (p *Params) Parse(opts ...Opts) error
- func (p *Params) ParseURL(ur string, opts ...Opts) error
- func (u *Params) SQL() string
- func (o *Params) Validate() error
- type QueryParam
Constants ¶
This section is empty.
Variables ¶
var DefaultLimit = 100
var DefaultMaxLimit = 1000
var ErrInvalidQueryParam = fmt.Errorf("not a valid QueryParam, try [%s]", strings.Join(_QueryParamNames, ", "))
Functions ¶
func QueryParamNames ¶ added in v0.1.9
func QueryParamNames() []string
QueryParamNames returns a list of possible string values of QueryParam.
Types ¶
type Builder ¶ added in v0.1.11
type Builder struct {
// contains filtered or unexported fields
}
func NewBuilder ¶ added in v0.1.11
func NewBuilder(args ...BuilderOpts) *Builder
NewBuilder returns a configured *Builder.
type BuilderOpts ¶ added in v0.1.11
type BuilderOpts func(o *Builder)
func WithDefaultJsonbColumn ¶ added in v0.1.11
func WithDefaultJsonbColumn(opt string) BuilderOpts
func WithDefaultLimit ¶ added in v0.1.11
func WithDefaultLimit(opt int) BuilderOpts
func WithMaxLimit ¶ added in v0.1.11
func WithMaxLimit(opt int) BuilderOpts
func WithParsers ¶ added in v0.1.11
func WithParsers(opt parser.Registry) BuilderOpts
type Opts ¶ added in v0.1.11
type Opts func(o *Params)
func WithBuilder ¶ added in v0.1.11
func WithFieldMap ¶ added in v0.1.11
type Params ¶ added in v0.1.11
type Params struct {
*Builder `json:"-" query:"-" url:"-"`
Query string `env:"-" flag:"query" desc:"query" query:"query,omitempty" qs:"query,omitempty" url:"query"`
Limit int `env:"-" flag:"limit" desc:"Max results per page [default: 20]" query:"limit,omitempty" qs:"limit,omitempty" url:"limit"`
Offset int `env:"-" flag:"skip" desc:"offset for pagination" query:"offset,omitempty" qs:"offset,omitempty" url:"offset"`
Fields string `env:"-" flag:"fields" desc:"Fields: 'f1,f2'" query:"fields,omitempty" qs:"fields,omitempty" url:"fields"`
Search string `env:"-" flag:"search" desc:"search string" query:"search,omitempty" qs:"search,omitempty" url:"search"`
Table string `env:"-" flag:"table" desc:"table or endpoint" query:"table,omitempty" qs:"table,omitempty" url:"table"`
Sort string `env:"-" flag:"sort" desc:"field to sort" query:"sort,omitempty" qs:"sort,omitempty" url:"sort"`
Group string `env:"-" flag:"group" desc:"group the results" query:"group,omitempty" qs:"group,omitempty" url:"group"`
// contains filtered or unexported fields
}
Params represents query params.
func Parse ¶ added in v0.1.11
Parse takes a table, *url.URL, and Options to parse the url query/search params into a SQL statement and args.
func ParseValues ¶ added in v0.1.11
ParseQuery url.Values, and Opts query/search params into a SQL statement and args.
func (*Params) EncodeValues ¶ added in v0.1.11
EncodeValues encodes the struct to url.Values.
func (*Params) GetAliasses ¶ added in v0.1.11
Aliasses returns the column alliases.
func (*Params) GetDefaultJsonbColumn ¶ added in v0.1.11
GetDefaultJsonbColumn gets the default JSONB column.
func (*Params) GetMappedField ¶ added in v0.1.11
GetMappedField returns the field alias, if available.
type QueryParam ¶ added in v0.1.9
type QueryParam string
QueryParam is the ENUM( fields, table, group, limit, offset, sort, query )
const ( // Fields is a QueryParam of type fields. Fields QueryParam = "fields" // Table is a QueryParam of type table. Table QueryParam = "table" // Group is a QueryParam of type group. Group QueryParam = "group" // Limit is a QueryParam of type limit. Limit QueryParam = "limit" // Offset is a QueryParam of type offset. Offset QueryParam = "offset" // Sort is a QueryParam of type sort. Sort QueryParam = "sort" // Query is a QueryParam of type query. Query QueryParam = "query" )
func ParseQueryParam ¶ added in v0.1.9
func ParseQueryParam(name string) (QueryParam, error)
ParseQueryParam attempts to convert a string to a QueryParam.
func QueryParamValues ¶ added in v0.1.9
func QueryParamValues() []QueryParam
QueryParamValues returns a list of the values for QueryParam
func (QueryParam) IsValid ¶ added in v0.1.9
func (x QueryParam) IsValid() bool
IsValid provides a quick way to determine if the typed value is part of the allowed enumerated values
func (QueryParam) String ¶ added in v0.1.9
func (x QueryParam) String() string
String implements the Stringer interface.
Source Files
Directories