sqlermb

command module
v1.0.0 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Jan 8, 2019 License: Apache-2.0 Imports: 29 Imported by: 0

README

SQLer

SQL-er is a tiny http server that applies the old CGI concept but for SQL queries, it enables you to an endpoint and assign a SQL query to be executed when anyone hits it, also it enables you to define validation rules so you can validate the request body/query params. sqler uses nginx style configuration language (HCL).

Features

  • Standalone with no dependencies.
  • Works with most of SQL databases out there including (SQL Server, MYSQL, SQLITE, PostgreSQL, Cockroachdb)
  • Built-in Validators
  • Built-in sql escaper function
  • Uses (HCL) configuration language
  • You can load multiple configuration files not just one, based on unix glob style pattern
  • Each SQL query could be named as Macro
  • You can use Go text/template within each macro
  • Each macro have its own Context (query params + body params) as .Input which is map[string]interface{}, and .Utils which is a list of helper functions, currently it contains only SQLEscape.
  • You can define authorizers, an authorizer is just a simple webhook that enables sqler to verify whether the request should be done or not.

Configuration Overview

// create a macro/endpoint called "_boot",
// this macro is private "used within other macros" 
// because it starts with "_".
_boot {
    // the query we want to execute
    exec = <<SQL
        CREATE TABLE IF NOT EXISTS `users` (
            `ID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(30) DEFAULT "@anonymous",
            `email` VARCHAR(30) DEFAULT "@anonymous",
            `password` VARCHAR(200) DEFAULT "",
            `time` INT UNSIGNED
        );
    SQL
}

// adduser macro/endpoint, just hit `/adduser` with
// a `?user_name=&user_email=` or json `POST` request
// with the same fields.
adduser {
    // what request method will this macro be called
    // default: ["ANY"]
    methods = ["POST"]

    // authorizers,
    // sqler will attempt to send the incoming authorization header
    // to the provided endpoint(s) as `Authorization`,
    // each endpoint MUST return `200 OK` so sqler can continue, other wise,
    // sqler will break the request and return back the client with the error occured.
    // each authorizer has a method and a url, if you ignored the method
    // it will be automatically set to `GET`.
    // authorizers = ["GET http://web.hook/api/authorize", "GET http://web.hook/api/allowed?roles=admin,root,super_admin"]

    // the validation rules
    // you can specifiy seprated rules for each request method!
    rules {
        user_name = ["required"]
        user_email =  ["required", "email"]
        user_password = ["required", "stringlength: 5,50"]
    }

    // the query to be executed
    exec = <<SQL
        {{ template "_boot" }}

        INSERT INTO users(name, email, password, time) VALUES(
            '{{ .Input.user_name | .SQLEscape }}', 
            '{{ .Input.user_email | .SQLEscape }}',
            '{{ .Input.user_password | .Hash "bcrypt" }}',
            {{ .UnixTime }}
        );

        SELECT * FROM users WHERE id = LAST_INSERT_ID();
    SQL
}

proclist {
    exec = "SHOW PROCESSLIST"
}

tables {
    exec = "SELECT * FROM information_schema.tables"
}

databases {
    exec = "SHOW DATABASES"
}

Supported SQL Engines

  • sqlite3
  • mysql
  • postgresql
  • cockroachdb
  • sqlserver

Supported Validation Rules

  • Simple Validations methods with no args: here
  • Advanced Validations methods with args: here

Supported Uitls

  • .SQLEscape - a sql escape function, {{ "some data" | .SQLEscape }}
  • .Hash <method> - hash the specified input using the specified method [md5, sha1, sha256, sha512, bcrypt], {{ "data" | .Hash "md5" }}
  • .UnixTime - returns the unix time in seconds, {{ .UnixTime }}
  • .UnixNanoTime - returns the unix time in nanoseconds, {{ .UnixNanoTime }}
  • .Uniqid - returns a unique id, {{ .Uniqid }}

License

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Documentation

Overview

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Copyright 2018 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL