From ae3fc354f3b15dfefb8ce6f2294768e063e53d19 Mon Sep 17 00:00:00 2001
From: pacien
Date: Sun, 25 Jul 2021 15:38:37 +0200
Subject: docs: explain database interface library choice

---
 readme.md | 53 ++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 52 insertions(+), 1 deletion(-)

diff --git a/readme.md b/readme.md
index 4b6e222..7500744 100644
--- a/readme.md
+++ b/readme.md
@@ -64,13 +64,64 @@ of operations. For the same reason, the use of stored procedures was not
 retained.
 
 
-## Project structure overview
+## Web application
+
+### Platform
+
+#### Interface with the database
+
+Object-Relational Mappers ([ORM]) such as [SQLAlchemy] are sometimes used to
+partially or fully abstract the database and its entities behind objects in the
+Object-Oriented Programming ([OOP]) sense. With those tools, it is possible to
+generate tables, indexes, and constraints from annotated classes. They also
+allow automatic querying and implicit joins to perform information retrieval,
+and inserting and updating records in the database directly by setting fields
+on linked objects.
+
+[ORM]: https://en.wikipedia.org/wiki/Object-relational_mapping
+[SQLAlchemy]: https://www.sqlalchemy.org/
+[OOP]: https://en.wikipedia.org/wiki/Object-oriented_programming
+
+However, the [object-relational impedance mismatch problems][ORIM] often render
+the use of ORMs impractical especially for complex queries, more often than not
+resulting in the developers bypassing the ORM altogether. The tools also tend
+to introduce performance issues due to the implicit queries they generate in
+situations in which developers do not always expect them to.
+
+[ORIM]: https://en.wikipedia.org/wiki/Object-relational_impedance_mismatch
+
+In the context of an database course aiming to teach SQL, such level of
+abstraction is also a hindrance by itself. Therefore, the use of an ORM has
+been excluded for this project.
+
+More direct and less intricate forms of mappings, such as transparent result
+tuples to Python immutable named tuples conversion, are preferred instead.
+Features of this kind, which are directly provided by database drivers like
+[psycopg], are often sufficient by themselves.
+
+[psycopg]: https://www.psycopg.org/
+
+It remains desirable to segregate SQL queries into their own modules in order
+to follow the Single-Responsibility Principle ([SRP]). Fortunately, libraries
+such as [embrace] allow easily deriving Data Access Objects ([DAO]) from sets
+of annotated raw SQL queries, exposing them as callable Python functions.
+
+[SRP]: https://en.wikipedia.org/wiki/Single_responsibility_principle
+[embrace]: https://pypi.org/project/embrace/
+[DAO]: https://en.wikipedia.org/wiki/Data_access_object
+
+### Project structure overview
 
 * `./sql/`
   * `tables.sql`: database initialisation statements
   * `queries.sql`: annotated SQL queries (from which the DAO is derived)
 * `./flake.nix`: project runtime and development environment description
 
+### Security considerations
+
+* SQL injections are prevented by using proper query parameters substitution,
+  automatically handled by the embrace and psycopg libraries.
+
 
 ## Development environment
 
-- 
cgit v1.2.3