SQLer
is a tiny software that enables you to expose your simple SQL
queries directly to the web so you can access it using any HTTP client, in another words, it helps you to expose this select * from mytable
to be the backend of GET /mytabledata
endpoint.
Not just RESTful
APIs, but also REDIS
protocol, so you can do this redis-cli -p3678 mytabledata
!
Sometimes I need to write APIs for MVPs quickly and what I really do is installing any envionrment i.e laravel
, django
, rails
... etc to just create some endpoints that will execute SQL
queries. I asked myself, why there isn't a tool that accelerates that process!
SQLer
will help you as a developer who wants to write quick APIs or as a DBA who who wants to create an abstraction on his database to add new feature for other Web/Mobile Apps.
You can look at the following diagram
SQLer
is built in Golang
// 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 {
validators {
user_name_is_empty = "$input.user_name && $input.user_name.trim().length > 0"
user_email_is_empty = "$input.user_email && $input.user_email.trim(' ').length > 0"
user_password_is_not_ok = "$input.user_password && $input.user_password.trim(' ').length > 5"
}
bind {
name = "$input.user_name"
email = "$input.user_email"
password = "$input.user_password"
}
methods = ["POST"]
authorizer = <<JS
(function(){
log("use this for debugging")
token = $input.http_authorization
response = fetch("http://requestbin.fullcontact.com/zxpjigzx", {
headers: {
"Authorization": token
}
})
if ( response.statusCode != 200 ) {
return false
}
return true
})()
JS
// include some macros we declared before
include = ["_boot"]
exec = <<SQL
INSERT INTO users(name, email, password, time) VALUES(:name, :email, :password, UNIX_TIMESTAMP());
SELECT * FROM users WHERE id = LAST_INSERT_ID();
SQL
}
// list all databases, and run a transformer function
databases {
exec = "SHOW DATABASES"
}
// list all tables from all databases
tables {
exec = "SELECT `table_schema` as `database`, `table_name` as `table` FROM INFORMATION_SCHEMA.tables"
}
// a macro that aggregates `databases` macro and `tables` macro into one macro
databases_tables {
aggregate = ["databases", "tables"]
}