uge_l2_rdbms_python_proto

Branches: main 2022-03-10

UGE / L2 / Intro to relational databases / Python project prototype

Background and goals

The introductory course to relational databases ends with a project illustrating the use of a relational database together with a simple CRUD web application.

The Python language could be a better fit compared to PHP for this kind of assignment as both students and the teaching staff are already familiar with the former from preceding units.

To showcase Python as a platform suitable for this purpose, a small demo project specified in ./demo-project-specs.md will be implemented.

The present document aims to motivate the functional and technical choices which have been made.

Interpretation of the specifications

Undefined aspects of the specifications are clarified as follows, with the intent of keeping the project as simple as possible:

the platform only deals with one currency (simoleons, arbitrarily),
the platform only supports one language (English, arbitrarily),
each user only has one account/wallet/balance (all three terms synonymous),
there is no notion of financial order (transactions are immediate),
only the effective transactions are logged, the rejected ones are not,
fees are billed on top of internal transfers instead of taking a cut.

Database model

Entity-relationship model

The database model is fully described as types, tables and constraints in ./sql/tables.sql. The file is annotated with comments and remarks explaining the design choices and highlights some additional constraints which remain to be enforced at upper levels.

An entity-relationship diagram of the model can be generated from the SQL table descriptions using a tool like SQLFairy (note: SQLFairy crashes with recent PostgreSQL dialects, so it is not usable here).

Consistency

The consistency of the account balance and the financial transaction log can be ensured through multiple means such as executing modification and insertions in SQL transactions at the application level, or using triggers within the database itself. The former option is chosen over the latter to avoid implicit actions.

Additional constraints to prevent alterations of the financial transaction log such as deletions are omitted here as the web application will act as the sole gateway to interact with the database and will offer no way of doing this kind of operations. For the same reason, the use of stored procedures was not retained.

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.

However, the object-relational impedance mismatch problems 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.

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.

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.

CGI scripts vs. Services

The most simple and primitive way of making Python programs accessible from a web browser would be through the Common Gateway Interface (CGI). Through it, the user is able to execute the scripts by visiting a matching URL, to then visualise its output directly in their browser.

The other approach is to make the program a server application as a persistent service process, in charge of accepting, handling and replying to network requests by itself.

The former, identical to the way the oldest PHP scripts are run, has become less popular in favour of the latter, mainly for performance and maintainability concerns for real applications outside of trivial, independent, and self-contained scripts. For those reasons, this project will be implemented as a service process.

Comprehensive vs. Light-weight frameworks

While bare Python could be used to create such a web service, it is desirable to work at a higher abstraction level to focus on the application-specific features rather than the standard protocol implementation details.

Python frameworks such as Django offer comprehensive solutions for building web applications based on the Model-View-Controller (MVC or MTV/MVT) design pattern, ensuring separation of concerns while abstracting lower level logic. This kind of complete solutions provide routing logic to map requests to user-defined handlers, and are integrated with a Object-Relational Mapper as well as with a templating engine to generate HTML pages dynamically.

Other more light-weight frameworks such as Flask or the more recent FastAPI instead focus on the first part, taking care of unpacking and routing requests to the user-defined handlers, while leaving the rest to the application developers. This approach does not fully impose an entire environment, and allows better composability with libraries which can be freely chosen.

Because the use of an ORM is not desirable in this project for the reasons detailed in a previous section, the choices of frameworks is limited to these light-weight frameworks. Here, FastAPI is preferred over Flask due to its more modern architecture, using parameters and dependency injection over thread-local global variables. The chosen templating engine is Jinja for its simplicity.

Project structure overview

./sql/
- tables.sql: database initialisation statements
- queries.sql: annotated SQL queries (from which the DAO is derived)
./app/
- app_database.py: database connection pool and transaction helper
- app_sessions.py: (signed cookies) session data management helpers
- app_templating.py: template rendering helper
- app_{account,wallet}.py: page-specific request handlers
- app.py: FastAPI web application entry point
./templates/: Jinja HTML templates
./static/: static web resources (stylesheets)
./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.
Injections in rendered views are prevented by the automatic sanitisation of inserted variables by the Jinja templating engine.
Passwords are salted and hashed when stored in the database to ensure some minimal protection of the data at rest. The hashing is handled by the passlib library, which also covers algorithm migrations.
Cross-Site Request Forgery (CSRF) attacks are mitigated through the conjunctive use of POST requests for user actions and SameSite restrictions for session cookies. (note: this will become a sufficient protection only when support in browsers will become ubiquitous).
In its current state, the application does not implement any kind of rate limiting. Such restriction would be needed for real world applications in order to mitigate account password brute-force attacks, but also to prevent users from avoiding transfer fees by sending many small unbilled transactions.

Going further

Technical platform improvements

Form validation could be refactored to make constraints more easily composable and to improve error reporting to users with a per-field granularity.
Error handling could be improved by the use of exceptions to be turned into user-friendly error messages in a generic way, either through decorators or with a middleware.
Request handlers could be made fully asynchronous after migrating to psycopg3 to improve performance under a large amount of requests.
Database schema migration will need to be implemented to ease the deployment of subsequent versions of the application.

Functional project improvements

A minimum transaction amount needs to be introduced to prevent users from dodging the transaction fees by sending small unbillable amounts.
The financial transaction log on the wallet page gets longer over time and needs to be properly paginated.
Some stronger form of authentication could be used, either by delegating the authentication to some other provider, or by adding multiple-factor authentication.
Users should be offered the possibility to modify their account data, delete their account, and to download all the data concerning them.
The creation of new user accounts could require some email validation. Users could be notified via email of incoming money transfers.
The user interface could be translated into multiple languages with the help of an internationalisation/localisation library such as Babel.

Development environment

The development and execution environment is fully described using the Nix language and package manager as a Nix Flake in ./flake.nix. This allows creating reproducible environments containing all the software dependencies of the program itself, as well as optional development tools.

(Note: this projects requires Nix version 2.4 or higher).

This environment can be used on NixOS, MacOS, or any Linux system having Nix installed. In principle, Nix should also be usable on Windows through the Windows Subsystem for Linux (WSL) compatibility layer. Full virtual machines and containers can also be derived from the same description file.

All the commands in this section have to be run within the provided development shell, which can be entered by running the following command at the root directory of the project:

nix develop

Local database

The Nix Flake development shell provides its own self-contained PostgreSQL server, configured to operate independently of any other instances running on the same system.

All data are written to the ./development_database/pgadata directory. The database server can be initialised by running the following command:

initdb --no-locale --encoding UTF8 --auth-host reject --auth-local peer

The local development PostgreSQL server can then be started by running the following command, with the $PGHOST environment variable automatically set by the development shell:

postgres -h "" -k "$PGHOST" -d 2

This server listens to local requests through a UNIX domain socket located at ./development_database/.s.PGSQL.5432, to which programs run in the development shell will implicitly automatically connect to.

The development shell ships with both the psql and pgcli tools to interact directly with the database. The latter provides additional features such as syntax highlighting and better auto-completion.

A new local database for the application can be created and its table can be initialised with:

createdb app
psql app < ./sql/tables.sql

Should the need arise, this database can be deleted with the following command before being created and initialised again:

dropdb app

Local application server

The server application can be started in development mode with:

uvicorn \
  --reload-dir app \
  --reload-dir templates \
  --reload \
  --app-dir app \
  app:main

This server will listen to incoming requests to a locally bound port. It will automatically reload itself when its files are edited, and display logs about type checking and runtime errors.

Production deployment

While the deployment phase is way out of the scope of a database introductory course, asking students to deploy their application on the Internet could be a motivational factor. It would also facilitate evaluating and grading projects, avoiding deployment and testing hassles to the teaching staff.

Standard daemon

The Nix Flake provides a Nix package which can be used to run the web application in a production context as a daemon, managed by a standard init system.

Docker container

A Docker container can also be derived from this package to be deployed on popular cloud hosting services.

Example of container deployment

An example of deployment procedure using the free tier provided by the Heroku hosting service is given below. This makes use of the skopeo tool to upload the Docker container to the service.

# Log in to an Heroku account.
heroku login

# Create a new project with a random name, hosted somewhere in Europe.
heroku create --region eu

# Set a local environment variable with the assigned name of the project so
# that the next commands operate on it.
export HEROKU_APP='name of the created app'

# Set a randomly-generated signed cookie secret key for our application.
heroku config:set COOKIE_SECRET_KEY=$(pwgen --secure 128 1)

# Attach a PostgreSQL database to the newly created app.
# This sets a connection URL in "DATABASE_URL" in the server's environment,
# containing the confidential database username and password.
heroku addons:create heroku-postgresql:hobby-dev --version=13

# Create the tables in the database.
heroku psql < ./sql/tables.sql

# Prepare a Docker container.
# This creates a Docker archive streaming script as `./result`.
nix build .#docker

# Log in to the Heroku container registry to upload our container.
skopeo login --username _ --password $(heroku auth:token) registry.heroku.com

# Upload the Docker image to Heroku (uploading about ~200MB).
./result \
| gzip --fast \
| skopeo --insecure-policy copy \
    docker-archive:/dev/stdin \
    docker://registry.heroku.com/$HEROKU_APP/web

# Deploy and launch the uploaded container.
heroku container:release web

# If all went well, the app should now be deployed and accessible on
# https://$HEROKU_APP.herokuapp.com
heroku open

# If not, logs can be remotely inspected for debugging.
heroku logs --tail

Conclusion

It took approximatively three days of work to the author to architecture and implement the demo project, with prior basic knowledge of SQL and Python, but with no prior familiarity with the used libraries (FastAPI, Jinja, psycopg, nor embrace). The extensive documentation of those dependencies has provided examples to quickly obtain a working application. The tooling and development environment allowed a quick, friction-less setup phase.

The level of abstraction provided by the framework and libraries allowed focusing on the database schema and queries aspects, without needing to develop a deep understanding of the intricate protocols and standards linked to web systems. Their APIs did not impose, but did encourage the developer to follow some best-practices regarding the structure (separation of concerns) and the security (proper query and template parameterisation, session handling) aspects of the application.

Copyright and licensing

This project is distributed under the terms of European Union Public Licence version 1.2, a copy of which is provided in ./licence.txt.

Other documents

demo-project-specs.md

Project description

The goal is to create a platform on which users can send money to each others.

Accounts

Unauthenticated visitors can to either create a new account or log in into an existing one using their username and the matching password.

Balance

Each account has a balance, which must remain non-negative at all times.

Transaction log

Users can see a log of all of the financial transactions which involved them.

Operations

Deposit and withdrawal

Users can deposit and withdraw an arbitrary amount of money to and from their account. Both operations are free.

Money transfer

Users can send money between each others, referred to by their respective usernames.

Each transaction between users has a fee of 10% of the transferred amount.

licence.txt

                      EUROPEAN UNION PUBLIC LICENCE v. 1.2
                      EUPL © the European Union 2007, 2016

This European Union Public Licence (the ‘EUPL’) applies to the Work (as defined
below) which is provided under the terms of this Licence. Any use of the Work,
other than as authorised under this Licence is prohibited (to the extent such
use is covered by a right of the copyright holder of the Work).

The Work is provided under the terms of this Licence when the Licensor (as
defined below) has placed the following notice immediately following the
copyright notice for the Work:

        Licensed under the EUPL

or has expressed by any other means his willingness to license under the EUPL.

1. Definitions

In this Licence, the following terms have the following meaning:

- ‘The Licence’: this Licence.

- ‘The Original Work’: the work or software distributed or communicated by the
  Licensor under this Licence, available as Source Code and also as Executable
  Code as the case may be.

- ‘Derivative Works’: the works or software that could be created by the
  Licensee, based upon the Original Work or modifications thereof. This Licence
  does not define the extent of modification or dependence on the Original Work
  required in order to classify a work as a Derivative Work; this extent is
  determined by copyright law applicable in the country mentioned in Article 15.

- ‘The Work’: the Original Work or its Derivative Works.

- ‘The Source Code’: the human-readable form of the Work which is the most
  convenient for people to study and modify.

- ‘The Executable Code’: any code which has generally been compiled and which is
  meant to be interpreted by a computer as a program.

- ‘The Licensor’: the natural or legal person that distributes or communicates
  the Work under the Licence.

- ‘Contributor(s)’: any natural or legal person who modifies the Work under the
  Licence, or otherwise contributes to the creation of a Derivative Work.

- ‘The Licensee’ or ‘You’: any natural or legal person who makes any usage of
  the Work under the terms of the Licence.

- ‘Distribution’ or ‘Communication’: any act of selling, giving, lending,
  renting, distributing, communicating, transmitting, or otherwise making
  available, online or offline, copies of the Work or providing access to its
  essential functionalities at the disposal of any other natural or legal
  person.

2. Scope of the rights granted by the Licence

The Licensor hereby grants You a worldwide, royalty-free, non-exclusive,
sublicensable licence to do the following, for the duration of copyright vested
in the Original Work:

- use the Work in any circumstance and for all usage,
- reproduce the Work,
- modify the Work, and make Derivative Works based upon the Work,
- communicate to the public, including the right to make available or display
  the Work or copies thereof to the public and perform publicly, as the case may
  be, the Work,
- distribute the Work or copies thereof,
- lend and rent the Work or copies thereof,
- sublicense rights in the Work or copies thereof.

Those rights can be exercised on any media, supports and formats, whether now
known or later invented, as far as the applicable law permits so.

In the countries where moral rights apply, the Licensor waives his right to
exercise his moral right to the extent allowed by law in order to make effective
the licence of the economic rights here above listed.

The Licensor grants to the Licensee royalty-free, non-exclusive usage rights to
any patents held by the Licensor, to the extent necessary to make use of the
rights granted on the Work under this Licence.

3. Communication of the Source Code

The Licensor may provide the Work either in its Source Code form, or as
Executable Code. If the Work is provided as Executable Code, the Licensor
provides in addition a machine-readable copy of the Source Code of the Work
along with each copy of the Work that the Licensor distributes or indicates, in
a notice following the copyright notice attached to the Work, a repository where
the Source Code is easily and freely accessible for as long as the Licensor
continues to distribute or communicate the Work.

4. Limitations on copyright

Nothing in this Licence is intended to deprive the Licensee of the benefits from
any exception or limitation to the exclusive rights of the rights owners in the
Work, of the exhaustion of those rights or of other applicable limitations
thereto.

5. Obligations of the Licensee

The grant of the rights mentioned above is subject to some restrictions and
obligations imposed on the Licensee. Those obligations are the following:

Attribution right: The Licensee shall keep intact all copyright, patent or
trademarks notices and all notices that refer to the Licence and to the
disclaimer of warranties. The Licensee must include a copy of such notices and a
copy of the Licence with every copy of the Work he/she distributes or
communicates. The Licensee must cause any Derivative Work to carry prominent
notices stating that the Work has been modified and the date of modification.

Copyleft clause: If the Licensee distributes or communicates copies of the
Original Works or Derivative Works, this Distribution or Communication will be
done under the terms of this Licence or of a later version of this Licence
unless the Original Work is expressly distributed only under this version of the
Licence — for example by communicating ‘EUPL v. 1.2 only’. The Licensee
(becoming Licensor) cannot offer or impose any additional terms or conditions on
the Work or Derivative Work that alter or restrict the terms of the Licence.

Compatibility clause: If the Licensee Distributes or Communicates Derivative
Works or copies thereof based upon both the Work and another work licensed under
a Compatible Licence, this Distribution or Communication can be done under the
terms of this Compatible Licence. For the sake of this clause, ‘Compatible
Licence’ refers to the licences listed in the appendix attached to this Licence.
Should the Licensee's obligations under the Compatible Licence conflict with
his/her obligations under this Licence, the obligations of the Compatible
Licence shall prevail.

Provision of Source Code: When distributing or communicating copies of the Work,
the Licensee will provide a machine-readable copy of the Source Code or indicate
a repository where this Source will be easily and freely available for as long
as the Licensee continues to distribute or communicate the Work.

Legal Protection: This Licence does not grant permission to use the trade names,
trademarks, service marks, or names of the Licensor, except as required for
reasonable and customary use in describing the origin of the Work and
reproducing the content of the copyright notice.

6. Chain of Authorship

The original Licensor warrants that the copyright in the Original Work granted
hereunder is owned by him/her or licensed to him/her and that he/she has the
power and authority to grant the Licence.

Each Contributor warrants that the copyright in the modifications he/she brings
to the Work are owned by him/her or licensed to him/her and that he/she has the
power and authority to grant the Licence.

Each time You accept the Licence, the original Licensor and subsequent
Contributors grant You a licence to their contributions to the Work, under the
terms of this Licence.

7. Disclaimer of Warranty

The Work is a work in progress, which is continuously improved by numerous
Contributors. It is not a finished work and may therefore contain defects or
‘bugs’ inherent to this type of development.

For the above reason, the Work is provided under the Licence on an ‘as is’ basis
and without warranties of any kind concerning the Work, including without
limitation merchantability, fitness for a particular purpose, absence of defects
or errors, accuracy, non-infringement of intellectual property rights other than
copyright as stated in Article 6 of this Licence.

This disclaimer of warranty is an essential part of the Licence and a condition
for the grant of any rights to the Work.

8. Disclaimer of Liability

Except in the cases of wilful misconduct or damages directly caused to natural
persons, the Licensor will in no event be liable for any direct or indirect,
material or moral, damages of any kind, arising out of the Licence or of the use
of the Work, including without limitation, damages for loss of goodwill, work
stoppage, computer failure or malfunction, loss of data or any commercial
damage, even if the Licensor has been advised of the possibility of such damage.
However, the Licensor will be liable under statutory product liability laws as
far such laws apply to the Work.

9. Additional agreements

While distributing the Work, You may choose to conclude an additional agreement,
defining obligations or services consistent with this Licence. However, if
accepting obligations, You may act only on your own behalf and on your sole
responsibility, not on behalf of the original Licensor or any other Contributor,
and only if You agree to indemnify, defend, and hold each Contributor harmless
for any liability incurred by, or claims asserted against such Contributor by
the fact You have accepted any warranty or additional liability.

10. Acceptance of the Licence

The provisions of this Licence can be accepted by clicking on an icon ‘I agree’
placed under the bottom of a window displaying the text of this Licence or by
affirming consent in any other similar way, in accordance with the rules of
applicable law. Clicking on that icon indicates your clear and irrevocable
acceptance of this Licence and all of its terms and conditions.

Similarly, you irrevocably accept this Licence and all of its terms and
conditions by exercising any rights granted to You by Article 2 of this Licence,
such as the use of the Work, the creation by You of a Derivative Work or the
Distribution or Communication by You of the Work or copies thereof.

11. Information to the public

In case of any Distribution or Communication of the Work by means of electronic
communication by You (for example, by offering to download the Work from a
remote location) the distribution channel or media (for example, a website) must
at least provide to the public the information requested by the applicable law
regarding the Licensor, the Licence and the way it may be accessible, concluded,
stored and reproduced by the Licensee.

12. Termination of the Licence

The Licence and the rights granted hereunder will terminate automatically upon
any breach by the Licensee of the terms of the Licence.

Such a termination will not terminate the licences of any person who has
received the Work from the Licensee under the Licence, provided such persons
remain in full compliance with the Licence.

13. Miscellaneous

Without prejudice of Article 9 above, the Licence represents the complete
agreement between the Parties as to the Work.

If any provision of the Licence is invalid or unenforceable under applicable
law, this will not affect the validity or enforceability of the Licence as a
whole. Such provision will be construed or reformed so as necessary to make it
valid and enforceable.

The European Commission may publish other linguistic versions or new versions of
this Licence or updated versions of the Appendix, so far this is required and
reasonable, without reducing the scope of the rights granted by the Licence. New
versions of the Licence will be published with a unique version number.

All linguistic versions of this Licence, approved by the European Commission,
have identical value. Parties can take advantage of the linguistic version of
their choice.

14. Jurisdiction

Without prejudice to specific agreement between parties,

- any litigation resulting from the interpretation of this License, arising
  between the European Union institutions, bodies, offices or agencies, as a
  Licensor, and any Licensee, will be subject to the jurisdiction of the Court
  of Justice of the European Union, as laid down in article 272 of the Treaty on
  the Functioning of the European Union,

- any litigation arising between other parties and resulting from the
  interpretation of this License, will be subject to the exclusive jurisdiction
  of the competent court where the Licensor resides or conducts its primary
  business.

15. Applicable Law

Without prejudice to specific agreement between parties,

- this Licence shall be governed by the law of the European Union Member State
  where the Licensor has his seat, resides or has his registered office,

- this licence shall be governed by Belgian law if the Licensor has no seat,
  residence or registered office inside a European Union Member State.

Appendix

‘Compatible Licences’ according to Article 5 EUPL are:

- GNU General Public License (GPL) v. 2, v. 3
- GNU Affero General Public License (AGPL) v. 3
- Open Software License (OSL) v. 2.1, v. 3.0
- Eclipse Public License (EPL) v. 1.0
- CeCILL v. 2.0, v. 2.1
- Mozilla Public Licence (MPL) v. 2
- GNU Lesser General Public Licence (LGPL) v. 2.1, v. 3
- Creative Commons Attribution-ShareAlike v. 3.0 Unported (CC BY-SA 3.0) for
  works other than software
- European Union Public Licence (EUPL) v. 1.1, v. 1.2
- Québec Free and Open-Source Licence — Reciprocity (LiLiQ-R) or Strong
  Reciprocity (LiLiQ-R+).

The European Commission may update this Appendix to later versions of the above
licences without producing a new version of the EUPL, as long as they provide
the rights granted in Article 2 of this Licence and protect the covered Source
Code from exclusive appropriation.

All other changes or additions to this Appendix require the production of a new
EUPL version.

src.euxane.eu

/uge_l2_rdbms_python_proto