SQL
Connection
How to make connection to Mysql or Postgres using single json configuration
{
"id": "sample-database-connection",
"type": "sql",
"driver": "mysql", //mysql|postgres
"name": "Sample database name",
"dns": "_databaseUsername_:_databasePassword_@tcp(example.com:3310)/_databaseName_?parseTime=true",
"maxOpenConnections": 100,
"connectionMaxLifetimeSec": 10,
"maxIdleConnections": 30,
"connectionMaxIdleTimeSec": 10
}
Select
Select non paginated data from database Fetch single or multiple items from database without using pagination
{
"type": "sql-select",
"id": "products-by-id",
"connection": "sample-database-connection",
"query": "SELECT * FROM `product` where id IN(:id)",
"resultType": "list",
"params": [
{
"name": "id",
"type": "int",
"required": true,
"list": true,
"default": [0]
}
]
}
Select paginated
Fetch multiple items from database with build in pagination Select paginated data from database
{
"type": "sql-select-paginated",
"id": "products-paginated",
"connection": "sample-database-connection",
"query": "SELECT * FROM `product` limit :offset, :limit",
"queryCount": "SELECT count(*) as c FROM `product`",
"params": [
{
"name": "limit",
"type": "int",
"default": 10,
"required": true
},
{
"name": "page",
"type": "int",
"default": 1,
"required": true
}
]
}
Every sql-select-paginated query must contain (:page, :limit) or :offset params, they are special, created and calculated by the system
Query builder
Build complex query and exec statements based on external parameters Complex SQL query builder
{
"type": "sql-builder",
"id": "sample-query",
"connection": "sample-database-connection",
"file": "sample.js",
"permissions": [
"admin",
"user"
],
"public": true,
"params": [
{
"name": "name",
"required": true
},
{
"name": "code",
"required": true
}
]
}
If you would like to select data without pagination
let query = "SELECT * FROM estates where `code` = :code limit 3"
builder.setQuery(query)
builder.setParams({ code: "EN", name: request.params.name})
If you would like to select data with pagination
let query = "SELECT * FROM estates where `code` = :code limit 3"
let queryCount = "SELECT count(*) as c FROM estate_type"
builder.setQuery(query)
builder.setCountQuery(queryCount)
builder.setParams({ code: "EN", name: request.params.name})
If you would like to run exec statement like update, delete, insert or any other
let queryExec = "Insert into `estate` (`player_id`, `deviceId`, `created_at`) VALUES (1, :name, NOW())"
builder.setParams({ code: "EN", name: request.params.name})
builder.setQueryExec(queryExec)
Insert
Insert statement Insert data without writing any code
{
"type": "sql-insert",
"id": "order-item-insert",
"connection": "sample-database-connection",
"table": "order_item",
"selectAfterInsert": "SELECT product_id as productId, quantity FROM order_item where id = :lastInsertId",
"params": [
{
"columnName": "order_id",
"name": "orderId",
"required": true
},
{
"columnName": "product_id",
"name": "productId",
"required": true
},
{
"columnName": "quantity",
"name": "quantity",
"required": true
}
]
}
selectAfterInsert - query that will be run after insert, you could count total number of items in database and show it to the user,
new parameter is available there :lastInsertId it is auto generated by apizilla, you can use other params too
required - required parameters must be provided, otherwise query won’t start, non required params will be omitted in insert
Update
Update data without writing any code
{
"type": "sql-update",
"id": "order-set-status-update",
"connection": "sample-database-connection",
"table": "order",
"whereCondition": "`id` = :id",
"params": [
{
"column": "status",
"name": "status",
"required": true
},
{
"name": "id",
"required": true
},
{
"name": "createdAt",
"column": "created_at",
"sqlValue": "NOW()"
}
]
}
column field is responsible for creating SET part of sql query, i.e UPDATE ... SET column = :paramName, if you skip it, then it won’t generate SET data.
If you add column to id param in example below, you will end up with query like this
UPDATE `order` SET status = :status, id = :id, createdAt = NOW() WHERE id = :id
whereCondition - update conditions
Delete
Delete data without writing any code Delete statement
{
"type": "sql-exec",
"id": "client-delete",
"connection": "sample-database-connection",
"query": "DELETE FROM users WHERE id IN(:id) AND user_id = :currentUser",
"params": [
{
"required": true,
"name": "id"
},
{
"name": "currentUser",
"serverValue": "$.user.id",
"required": true
}
]
}
Transaction
Perform complex database transactions
{
"type": "javascript",
"id": "order-new",
"file": "order-new.js",
"public": true,
"params": []
}
let orderId = uuid(),
currentTransaction = transaction("sample-database-connection"),
orderData = null;
try {
orderData = currentTransaction.run("orders-insert", {
"id": orderId,
"amount": totalPrice,
"currentUserId": 1,
}).Data
currentTransaction.run("order-item-insert", {
"orderId": orderData.id,
"productId": 1,
"quantity": 1
})
currentTransaction.run("order-item-insert", {
"orderId": orderData.id,
"productId": 2,
"quantity": 22
})
currentTransaction.commit()
} catch (e) {
currentTransaction.rollback()
throw {"error": e}
}
setStatusCode(200)
setResult({
orderId: orderData.id
})
Sql security
All the sql queries are run as prepared statement
You cannot create prepare statement (with order as param) It won’t run properly!
{
"type": "sql-select",
"id": "player-list",
"connection": "sample-database-connection",
"query": "SELECT player.* FROM app.player where username is not null ORDER BY id :orderDirection",
"resultType": "list",
"params": [
{
"paramName": "orderDirection",
"type": "string",
"required": true
}
]
}
Just use Query Builder
Params
Options you can use to define param
sqlValue - use database specific values like NOW()