I will tell you how to make a simple relay service for the frontend (an example will be in php), as well as how easy it is to design an API analogue in the base and register it for relaying to the frontend.
PS: an example of backend and frontend will be in PHP, firebird database. But this is not necessary, you can use any programming language and any database.
PS: I ask you not to throw your hats. I know that now it is not popular, for certain social reasons, there are more PHP or Python specialists on the market than SQL programmers, they are cheaper, everyone loves ORM. It is believed that when laying down the logic in the database, it is more difficult to organize a microservice architecture. Harder to manage version control. Go compiles and should run faster than a database. It is popular to lay business logic in php frameworks, etc.
There are many reasons and everyone has arguments on one side and on the other, and each one can be deeply argued.
But this article is for those who want to set business logic in the database . There is no purpose here to promote such a method. Please be correct in the comments.
In general, the method is very simple and annoying in terms of speed. If you write a competent microservice relay and actively use service tables in the database, then registering new APIs requires only one service table of the form:
CREATE TABLE DFM_PROC (
ID INTEGER NOT NULL,
NAME VARCHAR(70) NOT NULL,
DISCRIPTION VARCHAR(1000)
);
For example:
The scheme is as follows:
Here we will consider how to organize the work of the "Service for interface" microservice. I will give an
example of a frontend in php
function ser1($proc_id,$json)
{
//
$post='hash='.$_SESSION['sess_id'].'&user_id='.$_SESSION['id_user'].'&proc_id='.$proc_id.'&json='.base64_encode($json);
//
$url = 'http://192.168.128.1/ser.php';
$ch = curl_init();
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST , true);
curl_setopt($ch, CURLOPT_POSTFIELDS ,$post);
$result = curl_exec($ch);
$arr_res=json_decode($result,true);
curl_close($ch);
return $arr_res;
}
//
//
// 4 -
if (isset($_POST['go_new_prof']))
{
unset($arr);
$arr['0']=$_SESSION['id_user'];
$arr['1']=(int)$_GET['tp'];
$arr['2']=(int)$_POST['lang_list'];
$arr_prof=ser1(4,json_encode($arr));
}
An example of a called procedure in SQL
create or alter procedure DFM_PROF_ADD (
USER_ID smallint,
TYPE_ varchar(10),
LANG smallint)
as
begin
INSERT INTO DFM_PROFILES (USER_ID, TYPE_, LANG)
VALUES (:USER_ID, :TYPE_, :LANG);
suspend;
end
Now we understand how a microservice-relay will understand that it is necessary to perform this particular procedure, how it will understand what parameters it has, how it will pull it. I will give an example code, details in the comments in the code
<?php
$proc_id=(int)$_POST['proc_id'];
$json= base64_decode($_POST['json']);
$arr_json = json_decode($json,true);
// JSON
switch (json_last_error()) {
case JSON_ERROR_NONE:
$err = null;
break;
case JSON_ERROR_DEPTH:
$err = ' ';
break;
case JSON_ERROR_STATE_MISMATCH:
$err = ' ';
break;
case JSON_ERROR_CTRL_CHAR:
$err = ' ';
break;
case JSON_ERROR_SYNTAX:
$err = ' , JSON';
break;
case JSON_ERROR_UTF8:
$err = ' UTF-8, ';
break;
default:
$err = ' ';
break;
}
// JSON
if ($err==null)
{
// ID
$user_id=1;
// ID
$sql_proc = "select p.name from DFM_PROC p where p.id=".$proc_id;
$res_proc = ibase_query($dbh, $sql_proc);
$prom_proc = ibase_fetch_row($res_proc);
//
$sql_in='select ';
// ,
// RDB$PROCEDURE_PARAMETERS
$sql = 'select pp.rdb$parameter_number, pp.rdb$parameter_name from DFM_PROC p
left join RDB$PROCEDURE_PARAMETERS pp on pp.rdb$procedure_name=UPPER(p.name)
where p.id='.$proc_id.' and pp.rdb$parameter_type=1 --
order by pp.rdb$parameter_number';
$res = ibase_query($dbh, $sql);
$i_cou_out=0;
while($prom = ibase_fetch_row($res))
{
//p. -
$i_cou_out++;
if ($prom[0]>0) $sql_in.=',';
$sql_in.='p.'.$prom[1];
$name_out[$prom[0]]=$prom[1];
}
// -
$sql_in.=' from '.$prom_proc[0];
// , , execute procedure
if ($i_cou_out==0) $sql_in='execute procedure '.$prom_proc[0];
//
$sql = 'select
pp.rdb$parameter_number,
pp.rdb$parameter_name,
case
when f.rdb$field_type=7 then 1 --smalint
when f.rdb$field_type=8 then 2 --integer
when f.rdb$field_type=16 and f.rdb$field_scale=0 then 3 --bigint
when f.rdb$field_type=16 and f.rdb$field_scale<0 then 4 --frloat
when f.rdb$field_type=12 then 5 --date
when f.rdb$field_type=13 then 6 --time
when f.rdb$field_type=35 then 7 --timestamp
when f.rdb$field_type=37 then 8 --varcahr
end,
f.rdb$field_type, --
f.rdb$character_length, --
f.rdb$field_precision, --
f.rdb$field_scale --
from DFM_PROC p
left join RDB$PROCEDURE_PARAMETERS pp on pp.rdb$procedure_name=UPPER(p.name)
left join RDB$FIELDS f on f.rdb$field_name=pp.rdb$field_source
where p.id='.$proc_id.' and pp.rdb$parameter_type=0 --
order by pp.rdb$parameter_number';
$res = ibase_query($dbh, $sql);
$i_cou=0;
while($prom = ibase_fetch_row($res))
{
$i_cou++;
if ($prom[0]>0) $sql_in.=','; else $sql_in.='(';
if (($prom[2]==5)or($prom[2]==6)or($prom[2]==7)or($prom[2]==8))
//
// null
if ($arr_json[$prom[0]]=='')
$sql_in.="null";
else
$sql_in.="'".($arr_json[$prom[0]])."'";
else
//
if ($arr_json[$prom[0]]=='')
$sql_in.="null";
else
$sql_in.=($arr_json[$prom[0]]);
}
//
if ($i_cou_out==0)
{if ($i_cou>0) $sql_in.=')';}
else
{if ($i_cou>0) $sql_in.=') p'; else $sql_in.=' p';}
// p.
//
$res_in = ibase_query($dbh, $sql_in);
if ($i_cou_out==0)
{
// execute procedure
$json_out='{
"error_json":"",
"error_code_json":"",
"result":" execute procedure",
"result_code":0
}';
}
else
{
// json
$i_json=0;
$json_out='{';
while($prom_in = ibase_fetch_row($res_in))
{
if ($i_json>0) $json_out.=',';
$json_out.='"'.$i_json.'":{';
foreach($prom_in as $k => $v)
{
if ($k>0) $json_out.=',
';
$json_out.='"'.trim($name_out[$k]).'":"'.$v.'"';
}
$json_out.='}';
$i_json++;
}
$json_out.='}';
}
// - , ,
if (ibase_errmsg()=='')
{
//
echo $json_out;
}
else
{
//
$err_json='{
"error_json":"'.$err.'",
"error_code_json":'.json_last_error().',
"result":"'.str_replace('"','\'',ibase_errmsg()).'",
"result_code":2,
"sql_err":"'.$sql_in.'"}';
echo $err_json;
}
}
else
{
// JSON
$err_json='{
"error_json":"'.$err.'",
"error_code_json":'.json_last_error().',
"result":" json",
"result_code":3
}';
echo $err_json;
}
?>
What we get in the end.
1) We write a microservice relay 1 time. The rest of the backend architecture design takes place in the database. In PHP (or Go, Python, what the microservice will be) we no longer climb.
For example, a new feature was needed on the site - a frontend is being done, a procedure is being done. The procedure is registered in the plate and the frontend by its registration number calls the microservice API. EVERYTHING.
I recommend writing a microservice relay in Go as compiled and much faster. The advantage is that it only needs to be written once and the program is not complicated. Moreover, it will be a highly loaded element, to which intensive calls occur.
Library for connecting firebird to goland: https://github.com/nakagami/firebirdsql .
2) All processing goes into procedures - we get compiled elements that process everything at the database level and produce a RESULT. Those. if the operation consists of several select, insert, update, etc. we do not send data over the network to the backend, but immediately process it in the database.
Plus a procedure is a compiled element with a generated query plan. No resources are spent on this either.
3) Microservice-relay, to form the procedure, refers to the database 4 times.
1. Get its name
2. Get its outgoing parameters
3. Get its incoming parameters
4. Executes the procedure
4 times, because we considered a universal method. This can be reduced to once.
How:
1. It can be stored locally in a text editor on a web server for example. Periodically just pulling this table and updating when a new one is added.
2.3. Similarly, you can store it locally, pulling it all when something is updated.
4) Not all backend can be done on the database! This is an essential part of everything, but one must be guided by considerations of expediency .
For example, some chats, mailings, etc. aspects of the life of a web service, of course, must be done as separate microservices in the programming language most convenient for this. It is not necessary to transfer all the logic of the web service to the base at any cost! You need to transfer only the part that is convenient for this!