footrest

README

footrest - A REST API server from Go sql.DB

go install

go install github.com/shu-go/footrest@latest

or, go get to customize

go get github.com/shu-go/footrest

Start from SQLite

Generate a config file

footrest gen

The command generates footrest.config.

Edit the config file

In this case, you use sqlite, test.db.

{
  "Format": {
    "QueryOK": "{\"result\": [%]}",
    "ExecOK": "{\"result\": %}",
    "Error": "{\"error\": %}"
  },
  "Params": {
    "Select": "select",
    "Where": "where",
    "Order": "order",
    "Rows": "rows",
    "Page": "page"
  },
  "Timeout": 5000,         <-- ms, you should edit
  "Addr": ":12345",        <-- host:port, you should edit
  "Root": "/",             <-- you should edit
  "DBType": "sqlite",      <-- driverName in sql.Open, you MUST edit
  "Connection": "test.db", <-- dataSourceName in sql.Open, you MUST edit
  "ShiftJIS": false,       <-- you should edit
  "Debug": false
}

Prepare test.db

Create test.db with your favorite tool.

You will use a table "table1" ("ID" INTEGER, "Text1" TEXT) with some records in it.

Run

footrest

URI

http://{config.addr}{config.root}/{object_in_the_rdbms}

REST (GET)

Now, go to http://localhost:12345/table1.

All records in the table table1 are output as JSON form.

REST (GET with query params)

GET requests accepts some parameters.

The column conditions form is {column_name}={operator}{arg}.

ID > 1

http://localhost:12345/table1?id=>1

• operator: >
• arg: 1

ID >= 1

http://localhost:12345/table1?id=>=1

• operator: >=
• arg: 1

ID != 1

http://localhost:12345/table1?id=!1

• operator: !
• arg: 1

Text1 like aaa%

http://localhost:12345/table1?text1=%25aaa%25

• operator: %25
• arg: aaa%25

ID = 1

http://localhost:12345/table1?id=1

• operator: = if omitted
• arg: 1

REST (GET with special where query param)

You use S-expr to describe conditions.

(ID >= 2) AND (Text1 LIKE 'aaa%')

http://localhost:12345/table1?where=(and (>= .id 2) (like .text1 aaa%25))

Refer to source file dialect.go to see what operators are defined.

NOTE: COLUMN NAME IS DESCRIBED AS .{COLUMN_NAME}.

REST (GET with special order query param)

Pass order a comma separated list.

ORDER BY Text1, ID DESC

http://localhost:12345/table1?order=text1,-id

REST (GET with special select query param)

Pass select a comma separated list.

SELECT ID

http://localhost:12345/table1?select=id

REST (GET paginated with special rows and page query params)

Both rows and page are required to paginated.

http://localhost:12345/table1?rows=50&page=1

REST (POST)

No query params.

Pass JSON in a request body.

REST (PUT)

Query params:

• column conditions
• special where query param
• special upsert query param
  • upsert=1

Pass JSON to update in a request body.

REST (DELETE)

Query params:

• column conditions
• special where query param

REST (buik)

Post JSON array of Objects to /!bulk.

POST http://localhost:12345/!bulk HTTP/1.1
content-type: application/json

[
  {
    "method": "DELETE",
    "table": "Table1",
    "where": {"Col1": "'123'", "Col2": ">=100"}
  },
  {
    "method": "POST",
    "table": "Table1",
    "values": {"Col3": "12345", "Col4": 23456}
  },
  {
    "method": "PUT",
    "table": "Table1",
    "where": {"Col1": "'123'", "Col2": ">=100"},
    "values": {"Col3": "23456", "Col4": 34567}
  }
]

Methods:

• POST
• PUT
• UPSERT
• DELETE

It is designed to be customized.

Adding a supported DBMSes

• Copy a file dialect/sqlite/sqlite.go and customize.
• Edit a file cmd/footrest/main.go
  • Import the dialect and driver.

Remarks

Security

No security verifications.

Do not use this package for public or commercial purposes or in any other situation where security is required.

DBMS

This package depends on Rows.ColumnTypes() returns appropriate result.

Documentation

Index

• Constants
• func Columns(cols ...string) []string
• func RegisterDialect(name string, d *Dialect)
• type Config
  • func DefaultConfig() *Config
  • func (c Config) Context() (context.Context, context.CancelFunc)
• type Dialect
  • func DefaultDialect() Dialect
  • func GetDialect(name string) *Dialect
  • func (d *Dialect) AddOperator(name string, format string, f ...OperatorFormatter)
• type FootREST
  • func New(conn *sql.DB, dialect string, enc encoding.Encoding, useSchema bool, ...) *FootREST
  • func NewConn(driverName, dataSourceName string, enc encoding.Encoding, useSchema bool, ...) (*FootREST, *sql.DB, error)
  • func (r *FootREST) BuildDeleteStmt(table string, whereSExpr string) (string, []any, error)
  • func (r *FootREST) BuildGetStmt(table string, selColumns []string, whereSExpr string, orderColumns []string, ...) (string, []any, error)
  • func (r *FootREST) BuildPostStmt(table string, values any) (string, []any, error)
  • func (r *FootREST) BuildPutStmt(table string, values map[string]any, whereSExpr string) (string, []any, error)
  • func (r *FootREST) Bulk(ctx context.Context, b bulkReq) (int64, error)
  • func (r *FootREST) BulkGet(ctx context.Context, b bulkGetReq) (bulkRecordSet, error)
  • func (r *FootREST) Delete(ctx context.Context, table string, where string) (int64, error)
  • func (r *FootREST) Get(ctx context.Context, table string, selColumns []string, whereSExpr string, ...) (recordSet, error)
  • func (r *FootREST) Post(ctx context.Context, table string, values any) (int64, error)
  • func (r *FootREST) Put(ctx context.Context, table string, set map[string]any, where string) (int64, error)
  • func (r *FootREST) Serve()
• type Operator
  • func (o Operator) ApplyFormat(args ...string) (string, error)
• type OperatorFormatter
• type ResponseFormat
• type SpecialParams

Constants

const DefaultOperatorFormat = `$1 {OPERATOR} $2`

Functions

func Columns

func Columns(cols ...string) []string

Columns is for FootREST.Get(ctx, "my_table", Columns("a", "b", "c"), ...)

func RegisterDialect

func RegisterDialect(name string, d *Dialect)

RegisterDialect add *Dialect d into a global map.

See doc about Dialect.

Types

type Config

type Config struct {
	Format ResponseFormat
	Params SpecialParams

	Timeout int64

	Addr string
	Root string
}

func DefaultConfig

func DefaultConfig() *Config

func (Config) Context

func (c Config) Context() (context.Context, context.CancelFunc)

type Dialect

type Dialect struct {
	Operators   map[string]Operator // key(Operator.Name) must be upper case.
	Placeholder func(int) string
	Arg         func(int, any) any

	IsValidName func(string) bool

	Paginate func(uint, uint) [2]string // (rows_per_page,page) -> stmt
}

The best way to define a dialect is use DefaultDialect() and tweek the returned dialect.

func DefaultDialect

func DefaultDialect() Dialect

func GetDialect

func GetDialect(name string) *Dialect

func (*Dialect) AddOperator

func (d *Dialect) AddOperator(name string, format string, f ...OperatorFormatter)

type FootREST

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

func New

func New(conn *sql.DB, dialect string, enc encoding.Encoding, useSchema bool, config *Config) *FootREST

New creates new FootREST with already Opened connection(*sql.DB).

func NewConn

func NewConn(driverName, dataSourceName string, enc encoding.Encoding, useSchema bool, config *Config) (*FootREST, *sql.DB, error)

NewConn opens a connection and creates new FootREST with the conn.

func (*FootREST) BuildDeleteStmt

func (r *FootREST) BuildDeleteStmt(table string, whereSExpr string) (string, []any, error)

func (*FootREST) BuildGetStmt

func (r *FootREST) BuildGetStmt(table string, selColumns []string, whereSExpr string, orderColumns []string, rowsPerPage, page uint) (string, []any, error)

func (*FootREST) BuildPostStmt

func (r *FootREST) BuildPostStmt(table string, values any) (string, []any, error)

func (*FootREST) BuildPutStmt

func (r *FootREST) BuildPutStmt(table string, values map[string]any, whereSExpr string) (string, []any, error)

func (*FootREST) Bulk

func (r *FootREST) Bulk(ctx context.Context, b bulkReq) (int64, error)

func (*FootREST) BulkGet

func (r *FootREST) BulkGet(ctx context.Context, b bulkGetReq) (bulkRecordSet, error)

func (*FootREST) Delete

func (r *FootREST) Delete(ctx context.Context, table string, where string) (int64, error)

func (*FootREST) Get

func (r *FootREST) Get(ctx context.Context, table string, selColumns []string, whereSExpr string, orderColumns []string, rowsPerPage, page uint) (recordSet, error)

func (*FootREST) Post

func (r *FootREST) Post(ctx context.Context, table string, values any) (int64, error)

func (*FootREST) Put

func (r *FootREST) Put(ctx context.Context, table string, set map[string]any, where string) (int64, error)

func (*FootREST) Serve

func (r *FootREST) Serve()

type Operator

type Operator struct {
	Name      string
	Format    string            // "$1 == $2", "$1 BETWEEN $2 AND $3"
	Formatter OperatorFormatter // optional
}

func (Operator) ApplyFormat

func (o Operator) ApplyFormat(args ...string) (string, error)

type OperatorFormatter

type OperatorFormatter func(args ...string) (string, error)

type ResponseFormat

type ResponseFormat struct {
	QueryOK string
	ExecOK  string
	Error   string
}

type SpecialParams

type SpecialParams struct {
	Select string
	Where  string
	Upsert string
	Order  string
	Rows   string
	Page   string
}