Pure-Ruby SQL builder. Translates Ruby Hashes (or parsed JSON) into MySQL/MariaDB query strings.
- No runtime dependencies
- String and Symbol keys are both accepted
- Target: MySQL 8.0+
gem install json2sqlOr in your Gemfile:
gem "json2sql"require "json2sql"All entry points are stateless class methods that return a SQL string.
Json2sql::SelectRunner.build(hash) → StringThe result is always a SELECT JSON_OBJECT(…) query. Multiple top-level keys produce multiple named subqueries wrapped in a single outer JSON_OBJECT.
Json2sql::SelectRunner.build(
"users" => { "columns" => ["id", "name", "email"] }
)Conditions live under the "and" or "or" key.
Json2sql::SelectRunner.build(
"users" => {
"columns" => ["id", "name"],
"and" => {
"active" => 1, # Integer → col = 1
"name" => "john", # String → col LIKE '%john%'
}
}
)| Key | SQL |
|---|---|
{ "=" => value } |
col = value |
{ "!=" => value } or { "<>" => value } |
col != value |
{ ">" => value } |
col > value |
{ ">=" => value } |
col >= value |
{ "<" => value } |
col < value |
{ "<=" => value } |
col <= value |
{ "in" => [1, 2, 3] } |
col IN (1, 2, 3) |
{ "!in" => [1, 2] } |
col NOT IN (1, 2) |
{ "like" => "%.com" } |
col LIKE '%.com' |
{ "!like" => "%.com" } |
col NOT LIKE '%.com' |
{ "contains" => "john" } |
col LIKE '%john%' |
{ "first" => "Jo" } |
col LIKE 'Jo%' |
{ "last" => "son" } |
col LIKE '%son' |
{ "null" => true } |
col IS NULL |
{ "null" => false } |
col IS NOT NULL |
Json2sql::SelectRunner.build(
"users" => {
"columns" => ["id", "name"],
"and" => {
"age" => { ">=" => 18 },
"role" => { "!in" => [0, 9] },
"deleted_at" => { "null" => true },
"email" => { "last" => ".com" }
}
}
)Use "$.table.column" syntax to reference another column instead of a literal value:
"and" => { "author_id" => { "=" => "$.users.id" } }
# → `posts`.`author_id` = `users`.`id`"and" => {
"active" => 1,
"or" => { "role" => 1, "admin" => 1 }
}"order" => { "created_at" => "desc", "name" => "asc" }"limit" => 20, "offset" => 40Adding "options" => ["total"] wraps the result in { "data": […], "total": N } by running an additional COUNT(*) subquery.
Json2sql::SelectRunner.build(
"users" => {
"columns" => ["id", "name"],
"and" => { "active" => 1 },
"order" => { "created_at" => "desc" },
"limit" => 20,
"offset" => 0,
"options" => ["total"]
}
)Function columns in SELECT use objects in the columns array with alias, function and params.
You can use generic SQL CAST in function columns via:
{"alias" => "field", "function" => "CAST", "params" => ["source_column", "TYPE"]}
To return 64-bit integer columns as JSON strings (avoiding precision loss in JS clients), use TYPE = "CHAR":
Json2sql::SelectRunner.build(
"devices" => {
"columns" => [
{ "alias" => "id", "function" => "CAST", "params" => ["id", "CHAR"] },
"name"
]
}
)This generates a JSON value like CAST(devices.id AS CHAR) for the aliased key.
Json2sql::SelectRunner.build(
"users" => {
"columns" => ["id", "name"],
"children" => {
"posts" => { "columns" => ["id", "title"] }
}
}
)
# JOIN condition: `posts`.`user_id` = `users`.`id`Json2sql::SelectRunner.build(
"posts" => {
"columns" => ["id", "title"],
"parents" => {
"users" => { "columns" => ["id", "name"] }
}
}
)
# JOIN condition: `posts`.`user_id` = `users`.`id`Nesting is recursive — children can have their own children.
Json2sql::SelectRunner.build(
"users" => { "columns" => ["id", "name"] },
"products" => { "columns" => ["id", "price"] }
)
# → SELECT JSON_OBJECT('users', (…), 'products', (…));Json2sql::InsertRunner.build(hash) → String"columns" is a Hash of column => value.
Json2sql::InsertRunner.build(
"users" => { "columns" => { "name" => "João", "email" => "joao@example.com" } }
)
# → INSERT INTO `users` (`name`, `email`) VALUES ('João', 'joao@example.com');Pass an Array of row hashes:
Json2sql::InsertRunner.build(
"tags" => [
{ "columns" => { "name" => "ruby" } },
{ "columns" => { "name" => "rails" } }
]
)
# → INSERT INTO `tags` (`name`) VALUES ('ruby');
# INSERT INTO `tags` (`name`) VALUES ('rails');Json2sql::UpdateRunner.build(hash) → StringJson2sql::UpdateRunner.build(
"users" => {
"columns" => { "name" => "Maria", "active" => 1 },
"and" => { "id" => 42 }
}
)
# → UPDATE `users` SET `users`.`name` = 'Maria', `users`.`active` = 1 WHERE (`users`.`id` = 42);Json2sql::DeleteRunner.build(hash) → StringJson2sql::DeleteRunner.build(
"users" => { "and" => { "id" => 42 } }
)
# → DELETE FROM `users` WHERE (`users`.`id` = 42);| Ruby type | SQL output |
|---|---|
Integer |
raw number |
Float |
raw number |
String |
'escaped value' |
Single quotes in strings are doubled (O'Brien → 'O''Brien'). Backslashes are escaped.
Table and column names are sanitized by stripping characters outside [a-zA-Z0-9_-]. Malformed identifiers become mangled but harmless (e.g. "users; DROP TABLE" → `usersDROPTABLE`). Values are always wrapped in quoted literals.
Json2sql::QueryPolicy sanitizes a raw input Hash before passing it to any Runner. Use it as a server-side access control layer — it enforces which tables, columns, children and parents a caller may query, and injects forced WHERE conditions to prevent IDOR.
policy = Json2sql::QueryPolicy.new(
mode: :allow,
tables: {
orders: {
columns: %w[id total status],
children: { order_items: { columns: %w[id price] } },
parents: { users: { columns: %w[id name] } },
where: { "and" => { "user_id" => 42 } }
}
}
)
safe_input = policy.apply(raw_params)
sql = Json2sql::SelectRunner.build(safe_input)| Value | Behaviour |
|---|---|
:allow (default) |
Only tables listed in tables: are accessible. Tables absent from the config are blocked entirely. Empty tables: blocks everything. |
:deny |
All tables pass. After column filtering, tables with no remaining columns are removed. Same rule applies to children and parents. |
{
table_name => {
columns: [...], # column list
children: { child => { columns: [...], ... } },
parents: { parent => { columns: [...], ... } },
where: { "and" => { col => val } }
}
}| Key | :allow mode |
:deny mode |
|---|---|---|
columns absent or nil |
all columns blocked | columns untouched |
columns: [...] |
only listed columns pass; function-column Hashes always pass | listed columns are removed |
children / parents absent |
all relations blocked | relations untouched |
children: { t => {...} } |
only listed child tables pass | listed child tables are removed |
where: { "and" => {...} } |
forced conditions merged into "and"; forced keys overwrite user-supplied values |
same |
Forced where keys always overwrite the user-supplied value for the same column:
# config: where: { "and" => { "user_id" => 42 } }
# user sends: "and" => { "user_id" => 999 }
# result: "and" => { "user_id" => 42 } ← attacker cannot overridechildren and parents configs are recursive — each nested table accepts its own columns, children, parents and where.
Json2sql::QueryPolicy.new(
tables: {
orders: {
columns: %w[id total],
children: {
order_items: {
columns: %w[id price],
parents: { products: { columns: %w[id name] } }
}
}
}
}
)Both are accepted — normalized internally.
Json2sql::QueryPolicy.new(tables: { orders: { columns: %w[id] } })
# same as
Json2sql::QueryPolicy.new(tables: { "orders" => { "columns" => %w[id] } })- No boolean equality — use
1/0.true/falseonly works with the"null"operator. "options" => ["total"]doubles query cost — runs two subqueries. Ensure proper indexes.- Empty
inarray emitsIN (NULL)— always false, intentional. LATERALsubqueries — requires MySQL 8.0+.
bundle exec rake test # run test suite
bundle exec rake build # build .gem to pkg/
bundle exec rake release # tag + push + publish to RubyGems.org