Re:Things I wished more developers knew about databases
by wenc on Wednesday April 22, 2020 @03:05PM
(#22949026)
> Instead of REST endpoints (or gRPC or whatever), create
> stored procedures. These define a strong contract with your DB
Exactly. It took me years to grasp this, but when I did my code became much simpler.
A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost).
Whereas a SQL interface returns data in its native tabular format with all the correct types.
> stored procedures. These define a strong contract with your DB
Exactly. It took me years to grasp this, but when I did my code became much simpler.
A REST API (that returns JSON) has to contort a tabular data structure into a loosely-typed hierarchical data structure (JSON) which has to be read back, reconstructed and in many instances type-checked (e.g. DATETIMEs are not native to JSON, nuanced datatypes like DECIMAL(18,3) are lost).
Whereas a SQL interface returns data in its native tabular format with all the correct types.