MentDB WeakMQL Recipes
← innov-ai.com All profiles Portal
Profiles / Programming
CD

CRUD & data API developer

Generate SCRUD scripts from a table, patch and install them, write typed read and write APIs, check data quality and manage connections per environment. Uses the tuto_db_h2 database of the SQL developer recipes; the last recipe removes it.

10 recipes on this page · open in the interactive view

Recipes
  1. Inspect the table before writing the API
  2. Generate and review every SCRUD operation
  3. Install the generated insert, merge and select services
  4. Install the generated update and delete on the key
  5. An export service in seven formats
  6. A read API with typed parameters
  7. A write API with business rules in one transaction
  8. Data quality check on the table
  9. Declare the connections of each environment
  10. Tear down the tutorial database
executed on MentDB

Inspect the table before writing the API

Before generating an API, the developer reads the structure: tables, columns with types, keys and NOT NULL, what the editor grids receive, and the DDL to recreate the table elsewhere.

Pipeline
01 · tablesList the tablessql show tables→02 · descColumns and keyssql show desc→03 · gridEditor grid payloadsql show data→04 · ddlGenerated CREATE TABLEscrud create
Script
admin
#1. List the tables and describe one;
-> "[tables]" (sql show tables "tuto_db_h2");
json load "tuto_db_desc" (sql show desc "tuto_db_h2" "orders");
-> "[nb]" (json count "tuto_db_desc" /data);
-> "[cols]" "";
for (-> "[i]" 0) (< [i] [nb]) (++ "[i]") {
	-> "[cols]" (concat [cols] (json select "tuto_db_desc" (concat "/data[" [i] "]/FIELD")) " " (json select "tuto_db_desc" (concat "/data[" [i] "]/TYPE")) (if (equal (json select "tuto_db_desc" (concat "/data[" [i] "]/KEY")) "PRI") {" PK"} {""}) (if (equal (json select "tuto_db_desc" (concat "/data[" [i] "]/IS_NULLABLE")) "NO") {" NOT NULL"} {""}) [_n_]);
};
json unload "tuto_db_desc";
#2. What the editor grids receive, read as JSON;
json load "tuto_db_grid" (sql show data "tuto_db_h2" "SELECT * FROM orders WHERE qty > 1" "orders qty > 1");
-> "[grid_rows]" (json count "tuto_db_grid" /data);
-> "[grid_types]" (json select "tuto_db_grid" /column_types);
json unload "tuto_db_grid";
-> "[raw]" (sql select "tuto_db_h2" "SELECT customer, SUM(qty * price) AS amount FROM orders GROUP BY customer" "150,90" "amount by customer");
json load "tuto_db_grid" (string substr [raw] (string instr [raw] "{"));
-> "[sizes]" (string regexp_replace (json select "tuto_db_grid" /column_sizes) "\s+" "");
json unload "tuto_db_grid";
#3. The DDL to recreate the table elsewhere;
-> "[ddl]" (scrud create "tuto_db_h2" "orders");
concat "Tables: " [tables] [_n_] [cols] "Grid rows with qty > 1: " [grid_rows] ", grid column sizes sent by sql select: " [sizes] [_n_] "Grid column types: " (string regexp_replace [grid_types] "\s+" "") [_n_] "Generated DDL, first lines:" [_n_] (atom get [ddl] 1 [_n_]) [_n_] (atom get [ddl] 2 [_n_]) [_n_] (atom get [ddl] 3 [_n_]);
Result
mentdb
Tables: ["orders"]
id integer PK NOT NULL
customer varchar(40) NOT NULL
product varchar(40)
qty integer
price numeric
created date
Grid rows with qty > 1: 3, grid column sizes sent by sql select: ["150","90"]
Grid column types: ["LONG","STRING","STRING","LONG","DOUBLE","STRING"]
Generated DDL, first lines:
CREATE TABLE public.orders (
	id integer NOT NULL ,
	customer varchar(40) NOT NULL ,
Go further
  • Open the same query as a grid in the editor with sql select.
  • The generated DDL keeps the types of the table, the precision of numeric and the primary key: it runs as is on another database.
executed on MentDB

Generate and review every SCRUD operation

SCRUD generates ready to install MQL scripts for a table. The recipe generates the nine operations and reviews them: method and name, parameters, size, number of DML statements, empty WHERE clauses and lock parameters.

Pipeline
01 · selectRead servicesscrud select→02 · writeInsert, update, merge, deletescrud update→03 · batchCopy and import jobsscrud csv_to_db→04 · reviewCount what mattersstring regexp_count
Script
admin
#1. A sample CSV is needed by the CSV import generator;
file mkdir "tmp/tuto_db_in";
file create "tmp/tuto_db_in/orders.csv" "id;customer;product;qty;price;created" "UTF-8";
#2. Generate every SCRUD operation of the table;
json load "tuto_db_gen" "{}";
json iobject "tuto_db_gen" / "select" (scrud select "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "insert" (scrud insert "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "update" (scrud update "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "merge" (scrud merge "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "delete" (scrud delete "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "parse" (scrud parse "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "export" (scrud export "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "db_to_db" (scrud db_to_db "tuto_db_h2" "orders") STR;
json iobject "tuto_db_gen" / "csv_to_db" (scrud csv_to_db "tuto_db_h2" "orders" "tmp/tuto_db_in/orders.csv" ";" "\"" "") STR;
#3. Review each generated script;
-> "[review]" "";
json parse_obj "tuto_db_gen" "/" "[op]" "[code]" {
	-> "[review]" (concat [review] (string rpad [op] "." 11) " "
		(string regexp_extract [code] "script create (\w+ \"[^\"]+\")" 1)
		", params " (string count [code] "(var \"")
		", lines " (string count [code] [_n_])
		", sql dml " (string count [code] "sql dml")
		", empty WHERE " (string regexp_count [code] "WHERE\s*(;|\")")
		", lock params " (string count [code] "parameter add")
		[_n_]);
};
json unload "tuto_db_gen";
file delete "tmp/tuto_db_in";
[review];
Result
mentdb
select..... get "tuto_db_h2.orders.select", params 1, lines 42, sql dml 0, empty WHERE 0, lock params 0
merge...... post "tuto_db_h2.orders.merge", params 6, lines 56, sql dml 1, empty WHERE 0, lock params 0
insert..... post "tuto_db_h2.orders.insert", params 6, lines 51, sql dml 1, empty WHERE 0, lock params 0
update..... put "tuto_db_h2.orders.update", params 6, lines 45, sql dml 1, empty WHERE 0, lock params 0
csv_to_db.. exe "tuto_db_h2.orders.csv_to_db", params 1, lines 114, sql dml 3, empty WHERE 0, lock params 3
db_to_db... exe "tuto_db_h2.orders.db_to_db", params 1, lines 125, sql dml 3, empty WHERE 0, lock params 5
parse...... exe "tuto_db_h2.orders.parse_and_action", params 1, lines 53, sql dml 0, empty WHERE 0, lock params 0
delete..... delete "tuto_db_h2.orders.delete", params 1, lines 35, sql dml 1, empty WHERE 0, lock params 0
export..... exe "tuto_db_h2.orders.export", params 4, lines 65, sql dml 0, empty WHERE 0, lock params 0
Go further
  • The primary key is detected on H2: select, update, delete and export filter on WHERE id= and take an [id] parameter.
  • db_to_db and csv_to_db create lock parameters: give them your own prefix before installing.
executed on MentDB

Install the generated insert, merge and select services

The generated scripts are renamed with the tuto.db prefix and installed with eval as generated, then called like web services: insert an order, upsert it again with merge, read it back by its key with select.

Pipeline
01 · generateGenerate three servicesscrud insert→02 · renameOur namesstring replace→03 · installCreate the scriptseval→04 · callCall themexecute→05 · cleanRemove the scriptsscript delete
Script
admin
#1. Generate the insert, merge and select services from the table;
-> "[ins]" (scrud insert "tuto_db_h2" "orders");
-> "[mrg]" (scrud merge "tuto_db_h2" "orders");
-> "[sel]" (scrud select "tuto_db_h2" "orders");
#2. Give them our names;
-> "[ins]" (string replace [ins] "\"tuto_db_h2.orders.insert\"" "\"tuto.db.orders.insert\"");
-> "[mrg]" (string replace [mrg] "\"tuto_db_h2.orders.merge\"" "\"tuto.db.orders.merge\"");
-> "[sel]" (string replace [sel] "\"tuto_db_h2.orders.select\"" "\"tuto.db.orders.select\"");
#3. Install them;
eval [ins];
eval [mrg];
eval [sel];
#4. Call the web services as a client would, the select reads one order by its key;
-> "[n]" (execute "tuto.db.orders.insert.post" "[id]" "8" "[customer]" "Nakamura" "[product]" "Dock USB-C" "[qty]" "2" "[price]" "129.00" "[created]" "2026-10-31");
-> "[m]" (execute "tuto.db.orders.merge.post" "[id]" "8" "[customer]" "Nakamura" "[product]" "Dock USB-C" "[qty]" "4" "[price]" "125.00" "[created]" "2026-10-31");
json load "tuto_db_sel" (execute "tuto.db.orders.select.get" "[id]" "8");
-> "[rows]" (json count "tuto_db_sel" /data);
-> "[label]" (json select "tuto_db_sel" /table);
-> "[row8]" (concat "qty " (json select "tuto_db_sel" /data[0]/qty) ", price " (json select "tuto_db_sel" /data[0]/price));
json unload "tuto_db_sel";
#5. Remove the tutorial scripts;
script delete "tuto.db.orders.insert.post";
script delete "tuto.db.orders.merge.post";
script delete "tuto.db.orders.select.get";
concat "Insert: " [n] " | merge on the same id: " [m] " | rows from the select service for id 8: " [rows] " | label: " [label] [_n_] "Order 8 after the merge: " [row8] [_n_] "Scripts left: " (+ (script exist "tuto.db.orders.insert.post") (script exist "tuto.db.orders.merge.post") (script exist "tuto.db.orders.select.get"));
Result
mentdb
Insert: 1 | merge on the same id: 2 | rows from the select service for id 8: 1 | label: orders
Order 8 after the merge: qty 4, price 125.00
Scripts left: 0
Go further
  • Expose them over HTTP: script generate url gives the URL to call.
  • Add type checks in the generated param blocks, the generator puts true everywhere.
executed on MentDB

Install the generated update and delete on the key

The generator puts the primary key in the WHERE clause and gives the delete an [id] parameter. The recipe checks this in the generated code, installs both services without any patch, updates order 8, checks that no other row changed, then deletes it.

Pipeline
01 · generateUpdate and deletescrud delete→02 · reviewWHERE id and the id parameterstring regexp_extract→03 · installCreate the scripts as generatedeval→04 · callUpdate then deleteexecute→05 · checkOnly order 8 changedsql value
Script
admin
#1. Generate the update and delete services;
-> "[upd]" (scrud update "tuto_db_h2" "orders");
-> "[del]" (scrud delete "tuto_db_h2" "orders");
#2. Review: the primary key is in the WHERE clause and the delete takes the id;
-> "[upd_where]" (string regexp_extract [upd] "WHERE\s+(id=)" 1);
-> "[del_where]" (string regexp_extract [del] "WHERE\s+(id=)" 1);
-> "[del_params]" (string count [del] "(var \"");
#3. Rename and install them as generated;
eval (string replace [upd] "\"tuto_db_h2.orders.update\"" "\"tuto.db.orders.update\"");
eval (string replace [del] "\"tuto_db_h2.orders.delete\"" "\"tuto.db.orders.delete\"");
#4. Correct order 8, then delete it;
-> "[u]" (execute "tuto.db.orders.update.put" "[id]" "8" "[customer]" "Nakamura" "[product]" "Dock USB-C" "[qty]" "3" "[price]" "119.00" "[created]" "2026-10-31");
sql connect "tuto_db_s1" {cm get "tuto_db_h2";};
-> "[after_upd]" (sql row "tuto_db_s1" "SELECT qty, price FROM orders WHERE id=8");
-> "[others]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders WHERE id<>8 AND qty=3 AND price=119.00");
-> "[d]" (execute "tuto.db.orders.delete.delete" "[id]" "8");
-> "[left]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders WHERE id=8");
-> "[total]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders");
sql disconnect "tuto_db_s1";
#5. Remove the tutorial scripts;
script delete "tuto.db.orders.update.put";
script delete "tuto.db.orders.delete.delete";
concat "Generated WHERE: update " [upd_where] "..., delete " [del_where] "..., delete parameters: " [del_params] [_n_] "Updated: " [u] " -> " (string regexp_replace [after_upd] "\s+" " ") ", other rows changed: " [others] [_n_] "Deleted: " [d] ", order 8 left: " [left] ", rows in the table: " [total];
Result
mentdb
Generated WHERE: update id=..., delete id=..., delete parameters: 1
Updated: 1 -> { "price": "119.00", "qty": "3" }, other rows changed: 0
Deleted: 1, order 8 left: 0, rows in the table: 7
Go further
  • Keep the generated code in your repository and install it with script merge in each environment.
  • Check the number of impacted lines: 0 means the id does not exist.
executed on MentDB

An export service in seven formats

The generated export script takes a folder, a file name, a format checked by an enum and the order id. Installed as tuto.db.orders.export.exe, it exports order 1 in each format, the files are counted, a wrong format is refused, and everything is removed.

Pipeline
01 · generateExport scriptscrud export→02 · installCreate iteval→03 · loopOne call per formatexecute→04 · checkFiles createdfile dir_list→05 · cleanFolder and scriptfile delete
Script
admin
#1. Generate the export service, rename it and install it;
-> "[exp]" (scrud export "tuto_db_h2" "orders");
-> "[exp]" (string replace [exp] "\"tuto_db_h2.orders.export\"" "\"tuto.db.orders.export\"");
if (script exist "tuto.db.orders.export.exe") {script delete "tuto.db.orders.export.exe";};
eval [exp];
-> "[formats]" (string regexp_extract [exp] "is_enum \[format\] \"([^\"]+)\"" 1);
#2. Export order 1 in every format into a work folder;
-> "[dir]" "tmp/tuto_db_exp";
file mkdir [dir];
-> "[report]" "";
for (-> "[i]" 1) (<= [i] (atom size [formats] ",")) (++ "[i]") {
	-> "[f]" (atom get [formats] [i] ",");
	execute "tuto.db.orders.export.exe" "[directory]" [dir] "[filename]" "order_1" "[format]" [f] "[id]" "1";
	-> "[ext]" (if (equal [f] "excel") {"xls"} {if (equal [f] "excelx") {"xlsx"} {[f]}});
	-> "[report]" (concat [report] [f] "=" (file exist (concat [dir] "/order_1." [ext])) " ");
};
#3. A wrong format is refused by the typed parameter;
try {execute "tuto.db.orders.export.exe" "[directory]" [dir] "[filename]" "order_1" "[format]" "docx" "[id]" "1";} {-> "[refused]" (string regexp_extract [err] "\[\[\[ (.+?) \]\]\]" 1);} "[err]";
#4. Clean up;
json load "tuto_db_ls" (file dir_list [dir]);
-> "[nb_files]" (json count "tuto_db_ls" /);
json unload "tuto_db_ls";
file delete [dir];
script delete "tuto.db.orders.export.exe";
concat "Formats: " [formats] [_n_] "Created: " [report] [_n_] "Files in the folder: " [nb_files] [_n_] "docx: " [refused];
Result
mentdb
Formats: json,xml,csv,excel,excelx,html,pdf
Created: json=1 xml=1 csv=1 excel=1 excelx=1 html=1 pdf=1
Files in the folder: 7
docx: Sorry, the variable '[format]' in the script 'tuto.db.orders.export.exe' is not valid (type is_enum [format] "json,xml,csv,excel,excelx,html,pdf";).
Go further
  • Schedule it every night with a job.
  • Add a date to the file name with date sysdate.
executed on MentDB

A read API with typed parameters

A hand written get script returns the orders of a customer above a minimum amount. The parameters are typed, so a bad value is refused before any SQL runs. The recipe also generates the URL and the MQL call to give to the front-end team.

Pipeline
01 · createGet script with typed paramsscript create→02 · callCall itexecute→03 · docURL for the frontscript generate url→04 · docMQL callscript generate execute→05 · cleanRemove itscript delete
Script
admin
#1. A read web service with typed parameters;
if (script exist "tuto.db.orders.by_customer.get") {script delete "tuto.db.orders.by_customer.get";};
script create get "tuto.db.orders.by_customer" false 1
  (param
  	(var "[customer]" {type is_varchar [customer] 40} "The customer name" is_null:false is_empty:false "Dupont")
  	(var "[min_amount]" {type is_double [min_amount]} "Minimum amount of a line" is_null:false is_empty:false "0")
  )
  "Return the orders of a customer in JSON."
{
	try {
		sql connect "tuto_db_ws" {cm get "tuto_db_h2"};
		-> "[res]" (sql to json "tuto_db_ws" "orders" (concat "SELECT id, product, qty, price, qty * price AS amount FROM orders WHERE customer=" (sql encode [customer]) " AND qty * price >= " (sql encode [min_amount]) " ORDER BY id"));
		sql disconnect "tuto_db_ws";
		[res]
	} {
		try {sql disconnect "tuto_db_ws"} {} "[sub_err]";
		exception (1) ([err]);
	} "[err]";
} "Return a JSON object with columns and data";
#2. Call it as a client would;
json load "tuto_db_r" (execute "tuto.db.orders.by_customer.get" "[customer]" "O'Brien" "[min_amount]" "0");
-> "[nb]" (json count "tuto_db_r" /data);
-> "[first]" (json select "tuto_db_r" /data[0]/product);
json unload "tuto_db_r";
#3. The URL and the MQL call to give to the front-end team;
-> "[url_params]" (string regexp_extract (script generate url "tuto.db.orders.by_customer.get") "\?(.+)" 1);
-> "[mql_call]" (script generate execute "tuto.db.orders.by_customer.get");
#4. A bad parameter is refused before any SQL runs;
try {execute "tuto.db.orders.by_customer.get" "[customer]" "Dupont" "[min_amount]" "ten";} {-> "[refused]" (string regexp_extract [err] "\[\[\[ (.+?) \]\]\]" 1);} "[err]";
script delete "tuto.db.orders.by_customer.get";
concat "Orders of O'Brien: " [nb] ", first product: " [first] [_n_] "URL parameters: " [url_params] [_n_] [mql_call] [_n_] "With min_amount=ten: " [refused];
Result
mentdb
Orders of O'Brien: 2, first product: Monitor 27
URL parameters: x-token=&x-user=&customer=&min_amount=
execute "tuto.db.orders.by_customer.get"
	"[customer]" "Dupont"
	"[min_amount]" "0"
;
With min_amount=ten: Sorry, the variable '[min_amount]' in the script 'tuto.db.orders.by_customer.get' is not valid (type is_double [min_amount];).
Go further
  • Add paging parameters and a LIMIT clause.
  • Grant the script to a group of API users with group grant script.
executed on MentDB

A write API with business rules in one transaction

A post script inserts an order and checks a credit limit inside the same transaction: a duplicate id, a zero quantity and a customer above 2000 are refused, only the valid order is committed.

Pipeline
01 · createPost script with rulesscript create→02 · txInsert, check, commitsql auto_commit→03 · refuseBusiness errorsexception→04 · testFour requestsjson parse_array
Script
admin
#1. A write web service: typed input, business rules, one transaction;
if (script exist "tuto.db.orders.add.post") {script delete "tuto.db.orders.add.post";};
script create post "tuto.db.orders.add" false 1
  (param
  	(var "[id]" {type is_int [id]} "The order id" is_null:false is_empty:false "9")
  	(var "[customer]" {type is_varchar [customer] 40} "The customer" is_null:false is_empty:false "Silva")
  	(var "[product]" {type is_varchar [product] 40} "The product" is_null:false is_empty:false "Webcam")
  	(var "[qty]" {type is_int [qty]} "The quantity" is_null:false is_empty:false "1")
  	(var "[price]" {type is_decimal [price] 10 2} "The unit price" is_null:false is_empty:false "59.00")
  )
  "Add an order if the customer stays under 2000 of orders."
{
	if (<= [qty] 0) {exception (1) ("qty must be positive");};
	try {
		sql connect "tuto_db_ws" {cm get "tuto_db_h2"};
		sql auto_commit "tuto_db_ws" false;
		sql dml "tuto_db_ws" (concat "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (" (sql encode [id]) ", " (sql encode [customer]) ", " (sql encode [product]) ", " (sql encode [qty]) ", " (sql encode [price]) ", CURRENT_DATE)");
		-> "[total]" (sql value "tuto_db_ws" (concat "SELECT SUM(qty * price) FROM orders WHERE customer=" (sql encode [customer])));
		if (> [total] 2000) {exception (1) (concat "credit limit exceeded: " [total]);};
		sql commit "tuto_db_ws";
		sql disconnect "tuto_db_ws";
		concat "{\"status\": \"created\", \"id\": " [id] ", \"customer_total\": " [total] "}"
	} {
		try {sql rollback "tuto_db_ws"; sql disconnect "tuto_db_ws";} {} "[sub_err]";
		exception (1) ([err]);
	} "[err]";
} "Return a JSON status";
#2. Make the recipe replayable, then try four requests;
sql connect "tuto_db_s1" {cm get "tuto_db_h2";};
sql dml "tuto_db_s1" "DELETE FROM orders WHERE id IN (9, 10)";
-> "[log]" "";
json load "tuto_db_req" "[
 {\"id\": \"9\", \"customer\": \"Silva\", \"product\": \"Webcam\", \"qty\": \"1\", \"price\": \"59.00\"},
 {\"id\": \"1\", \"customer\": \"Silva\", \"product\": \"Webcam\", \"qty\": \"1\", \"price\": \"59.00\"},
 {\"id\": \"10\", \"customer\": \"Silva\", \"product\": \"Webcam\", \"qty\": \"0\", \"price\": \"59.00\"},
 {\"id\": \"10\", \"customer\": \"Nakamura\", \"product\": \"Laptop\", \"qty\": \"1\", \"price\": \"1249.00\"}
]";
json parse_array "tuto_db_req" "/" "[r]" {
	json load "tuto_db_one" [r];
	try {
		-> "[answer]" (execute "tuto.db.orders.add.post" "[id]" (json select "tuto_db_one" /id) "[customer]" (json select "tuto_db_one" /customer) "[product]" (json select "tuto_db_one" /product) "[qty]" (json select "tuto_db_one" /qty) "[price]" (json select "tuto_db_one" /price));
	} {
		-> "[answer]" (concat "ERROR " (string regexp_extract [err] "(qty must be positive|credit limit exceeded: [0-9.]+|SQLSTATE|[A-Z][a-z ]+violation)" 1));
	} "[err]";
	-> "[log]" (concat [log] "id " (json select "tuto_db_one" /id) " " (json select "tuto_db_one" /customer) ": " [answer] [_n_]);
};
json unload "tuto_db_one";
json unload "tuto_db_req";
-> "[rows]" (sql value "tuto_db_s1" "SELECT COUNT(*) FROM orders");
sql disconnect "tuto_db_s1";
script delete "tuto.db.orders.add.post";
concat [log] "Rows in the table: " [rows];
Result
mentdb
id 9 Silva: {"status": "created", "id": 9, "customer_total": 215.50}
id 1 Silva: ERROR Unique index or primary key violation
id 10 Silva: ERROR qty must be positive
id 10 Nakamura: ERROR credit limit exceeded: 2498.00
Rows in the table: 8
Go further
  • Return HTTP friendly JSON errors from the catch block.
  • Log every refused request with log write.
executed on MentDB

Data quality check on the table

A house rule is saved as a reusable algorithm, the rules per field are built from the algorithm catalogue, two dirty rows arrive, and dq analyse gives the share of valid values per field and rule.

Pipeline
01 · ruleSave a custom algorithmdq algorithm set→02 · catalogueRead algorithm codedq algorithm get→03 · analyseRun the analysisdq analyse→04 · reportRead the resultjson parse_obj→05 · cleanRemove the algorithmdq algorithm remove
Script
admin
#1. A house rule as a reusable algorithm: product names start with a capital letter;
dq algorithm set "tuto_db_product_name" (mql {
	type is_matches_regex [VAR] "[A-Z][A-Za-z0-9 -]*";
});
-> "[algo_ok]" (dq algorithm exist "tuto_db_product_name");
json load "tuto_db_cat" (dq algorithm show);
-> "[nb_algos]" (json count "tuto_db_cat" /);
json unload "tuto_db_cat";
#2. Rules per field, the code of each algorithm is read from the catalogue;
json load "tuto_db_rules" "{\"customer\": \"is_varchar,is_null_or_empty\", \"product\": \"tuto_db_product_name\", \"qty\": \"is_small_int\", \"created\": \"is_date\"}";
json load "tuto_db_dq" "[]";
json parse_obj "tuto_db_rules" "/" "[field]" "[algos]" {
	json load "tuto_db_f" "{}";
	json iobject "tuto_db_f" / field [field] STR;
	json iobject "tuto_db_f" / algo "{}" OBJ;
	for (-> "[i]" 1) (<= [i] (atom size [algos] ",")) (++ "[i]") {
		-> "[a]" (atom get [algos] [i] ",");
		json iobject "tuto_db_f" /algo [a] (string replace (dq algorithm get [a]) "[VAR]" (concat "[T_" [field] "]")) STR;
	};
	json iarray "tuto_db_dq" / (json doc "tuto_db_f") OBJ;
};
#3. Two bad rows arrive from an import, labels allow 15 characters;
sql connect "tuto_db_s1" {cm get "tuto_db_h2";};
sql dml "tuto_db_s1" "DELETE FROM orders WHERE id IN (98, 99)";
sql dml "tuto_db_s1" "INSERT INTO orders (id, customer, product, qty, price, created) VALUES (98, '', 'usb cable', 40000, 2.50, '2026-10-31'), (99, 'Leclerc-Dumontier SA', 'Hub', 1, 25.00, NULL)";
#4. Run the analysis and read the editor answer as JSON;
-> "[raw]" (dq analyse "tuto_db_h2" (json doc "tuto_db_dq") "orders" "SELECT * FROM orders");
json load "tuto_db_res" (string substr [raw] (string instr [raw] "{"));
-> "[out]" "";
json parse_array "tuto_db_res" "/data" "[line]" {
	json load "tuto_db_l" [line];
	-> "[out]" (concat [out] (json select "tuto_db_l" /field) ":");
	json parse_obj "tuto_db_l" "/" "[k]" "[v]" {
		if (and (not (equal [k] "field")) (is not null [v])) {-> "[out]" (concat [out] " " [k] " " [v]);};
	};
	-> "[out]" (concat [out] [_n_]);
};
json unload "tuto_db_l";
#5. Which rows have an empty customer: analyse show lists the rows where a rule is true;
-> "[raw]" (dq analyse show "tuto_db_h2" (json doc "tuto_db_dq") "is_null_or_empty" "customer" "orders" "SELECT * FROM orders");
json load "tuto_db_res" (string substr [raw] (string instr [raw] "{"));
-> "[empty_ids]" (json select "tuto_db_res" /data[0]/id);
#6. dq generate writes the same rule code for one field, to paste in a job;
-> "[gen]" (dq generate "tuto_db_h2" "orders" "created" "[\"is_date\"]" "SELECT * FROM orders");
#7. Clean up the bad rows, the rule and the JSON keys;
sql dml "tuto_db_s1" "DELETE FROM orders WHERE id IN (98, 99)";
sql disconnect "tuto_db_s1";
dq algorithm remove "tuto_db_product_name";
json unload "tuto_db_res";
json unload "tuto_db_f";
json unload "tuto_db_dq";
json unload "tuto_db_rules";
concat "Algorithm saved: " [algo_ok] ", algorithms in the catalogue: " [nb_algos] [_n_] [out] "Order with an empty customer: " [empty_ids] [_n_] "dq generate for created: " (string count [gen] [_n_]) " lines, ends with: " (string regexp_extract [gen] "(dq analyse \"[^\"]+\")" 1) [_n_] "Algorithm still there: " (dq algorithm exist "tuto_db_product_name");
Result
mentdb
Algorithm saved: 1, algorithms in the catalogue: 25
product: tuto_db_product_name 90% (9/10)
created: is_date 90% (9/10)
qty: is_small_int 90% (9/10)
customer: is_null_or_empty 10% (1/10) is_varchar 90% (9/10)
Order with an empty customer: 98
dq generate for created: 10 lines, ends with: dq analyse "tuto_db_h2"
Algorithm still there: 0
Go further
  • Let dq generate write the rule code for one field.
  • Stop an import job when a rule is under 100%.
executed on MentDB

Declare the connections of each environment

The API runs on H2 in dev; test is PostgreSQL, prod is MySQL, the ERP is SQL Server, a partner drops files by SFTP and exports go to a local folder. The recipe declares them, picks the one of the current environment, makes an inventory and removes them.

Pipeline
01 · declarePostgreSQL, MySQL, SQL Servercm set postgresql→02 · declareSFTP and local foldercm set sftp→03 · pickTarget of the environmentcm get→04 · inventoryPer typecm show→05 · cleanRemove themcm remove
Script
admin
#1. The API runs on H2 in dev, the other environments are declared under the same naming rule;
cm set "tuto_db_env_test" {execute "db.postgresql.config.get"
	"[hostname]" "pg-test.internal"
	"[port]" "5432"
	"[database]" "shop"
	"[user]" "shop_api"
	"[password]" "change_me"
;};
cm set "tuto_db_env_prod" {execute "db.mysql.config.get"
	"[hostname]" "mysql-prod.internal"
	"[port]" "3306"
	"[database]" "shop"
	"[user]" "shop_api"
	"[password]" "change_me"
;};
cm set "tuto_db_env_erp" {execute "db.sqlserver.config.get"
	"[hostname]" "erp.internal"
	"[port]" "1433"
	"[database]" "erp"
	"[user]" "erp_reader"
	"[password]" "change_me"
;};
cm set "tuto_db_env_drop" {execute "file.remote.sftp.config.get"
	"[hostname]" "sftp.partner.example"
	"[port]" "22"
	"[user]" "shop"
	"[password]" "change_me"
	"[connectTimeout]" 10000
	"[dataTimeout]" 30000
;};
cm set "tuto_db_env_out" {execute "file.local.config.get"
	"[dir]" "tmp/tuto_db_out"
;};
#2. Which connection does the API use, depending on the environment;
-> "[env]" "prod";
-> "[cm_id]" (concat "tuto_db_env_" [env]);
json load "tuto_db_cfg" (cm get [cm_id]);
-> "[target]" (concat [cm_id] " -> " (json select "tuto_db_cfg" /subType) " " (json select "tuto_db_cfg" /jdbc));
json unload "tuto_db_cfg";
#3. Inventory by type, then remove what was declared;
-> "[inv]" (concat "db: " (string regexp_count (cm show "db") "tuto_db_env_") ", sftp: " (string regexp_count (cm show "sftp") "tuto_db_env_") ", file: " (string regexp_count (cm show "file") "tuto_db_env_"));
-> "[removed]" 0;
json load "tuto_db_all" (cm show_obj);
json parse_obj "tuto_db_all" "/" "[k]" "[v]" {
	if (string starts_with [k] "tuto_db_env_") {cm remove [k]; ++ "[removed]";};
};
json unload "tuto_db_all";
concat "Declared tuto_db_env_* by type: " [inv] [_n_] "API target for " [env] ": " [target] [_n_] "Removed: " [removed] ", prod still declared: " (cm exist "tuto_db_env_prod");
Result
mentdb
Declared tuto_db_env_* by type: db: 3, sftp: 1, file: 1
API target for prod: tuto_db_env_prod -> MySQL jdbc:mysql://mysql-prod.internal:3306/shop
Removed: 5, prod still declared: 0
Go further
  • Keep the passwords out of the code: read them from a private parameter.
  • Use cm show_scrud to list the connections the SCRUD generator accepts.
executed on MentDB

Tear down the tutorial database

Last step of the tutorial: check that no script failed today, close a session left open, remove the H2 connection and delete the database files, then verify that nothing is left.

Pipeline
01 · activityErrors of the daysql show activity→02 · sessionsOpen sessionssql show→03 · removeRemove the connectioncm remove→04 · filesDelete the databasefile delete
Script
admin
#1. Last look before the teardown: errors of the scripts today;
-> "[raw]" (sql show activity DAY (concat (date sysdate) " 00:00:00") (concat (date sysdate) " 23:59:59"));
json load "tuto_db_act" (string substr [raw] (string instr [raw] "{"));
-> "[errors]" (json count "tuto_db_act" /error);
json unload "tuto_db_act";
#2. Close a session that a failed recipe could have left open;
-> "[open_before]" (sql show);
if (string contains [open_before] "\"tuto_db_s1\"") {sql disconnect "tuto_db_s1";};
#3. Remove the connection and the database files;
-> "[kb]" (/ (file size "tmp/tuto_db_h2") 1024);
cm remove "tuto_db_h2";
file delete "tmp/tuto_db_h2";
#4. Final checks;
concat "Script errors today: " [errors] [_n_] "Database size before delete: " (math round [kb] 0) " KB" [_n_] "Connection left: " (cm exist "tuto_db_h2") ", files left: " (file exist "tmp/tuto_db_h2") ", open sessions: " (sql show);
Result
mentdb
Script errors today: 0
Database size before delete: 324 KB
Connection left: 0, files left: 0, open sessions: []
Go further
  • Run it from a job at the end of each integration test.
  • Use sql disconnect in every catch block so that no session stays open.

Also in Programming

MB
MQL beginner
Variables, operators, if, loops and your first try/exception: learn MQL step by step.
10 recipes
MD
MQL developer
Atom lists, data types, constants, translations, session variables and error patterns.
10 recipes
BW
Backend & web service developer
Typed scripts, execute / call / include, generated calls and URLs, parameters and logs.
10 recipes
DT
Date & time developer
Calendars, business days, time zones, ISO and epoch, durations, quarters and week numbers.
10 recipes
SE
String & encoding developer
Cleaning, case styles, regex, URL/HTML/Base64 encodings, number bases and encryption.
10 recipes
DV
Data validation developer
IBAN, BIC, SIRET, Luhn, email, IP, UUID, JSON and date checks gathered in a validation report.
10 recipes
JD
JSON & document store developer
In-memory JSON documents, persistent nodes and sequences: build, walk, sort, clean up.
10 recipes
SD
Script developer
Full script lifecycle in the MQL editor: typed parameters, read back, generate, patch, deploy, copy, grant, delete.
10 recipes
EM
Execution modes
execute, call, include, sub_include, parallel, stack: which environment, which checks, which logs.
11 recipes
WS
Web service developer
A small REST resource with get, post, put and delete scripts: validation, JSON answers, codes, grants, URLs and API docs.
10 recipes
AD
Async developer
Asynchronous processing with the stack: push runs now or later, priorities and parallel limits, errors and replays, flows and cleanup.
10 recipes
SD
Scheduler developer
Cron jobs and delayed runs: add, resume, pause, update and remove jobs, prove they ran, and postpone stack runs with a delay condition.
10 recipes
ED
Event developer
Event-driven scripts: file watchers that push each file event to the stack, validation, archiving, errors and safe restarts.
10 recipes
CC
Configuration & connections
Parameters (private, described, locked, deployed), environment variables, sessions and connections used by your scripts.
12 recipes
VD
Versioning & deployment
Group your scripts, commit, compare, roll back, export, import and package a release.
10 recipes
DE
Debugging & error handling
Coded errors with try/exception, logs with correlation ids, timing, metrics and defensive checks.
10 recipes
SD
SQL developer in MQL
Connections, DDL and DML with sql encode, transactions, cursors, parse, and exports to JSON, CSV, Excel and PDF on a real local database. Run the recipes in order: the first one creates the tuto_db_h2 database.
10 recipes
WA
Web application developer
Create a web app, its host name, pages, ajax forms, menu and grants, then remove it all.
10 recipes
AI
Algorithms in MQL
Sorting, searching, recursion, data structures and number algorithms: MQL is a real programming language.
10 recipes
CF
Control flow
if, if force, case, switch, mode, for, while, repeat, break, continue, parallel and synchronized on real cases.
10 recipes
TM
The MQL language in depth
Data types and null, booleans, comments, variables and scope, eval and mql blocks, coded errors and try.
10 recipes
SU
Screen & UI developer (sajax)
Build the screens of MentDB web apps in MQL: Bootstrap grids, forms and every control, ajax buttons, alerts, refreshes and charts.
10 recipes