Vanilla SQL Series

ToDo App Intro

This series of articles will demonstrate a pure PostgreSQL approach to software development.

A pure PostgreSQL approach means that entire backend is implemented in PostgreSQL. Specifically, SQL and PL/pgSQL (PostgreSQL SQL Procedural Language) functions and procedures are used only. Those functions and procedures will be used to encapsulate the entire application logic, which will be described later.

To be able to do this, we will use an automatic REST API Client for PostgreSQL called NpgsqlRest. This PostgreSQL automatic REST API Client is also a code generator (low-code platform), so we will also be demonstrating this feature, which boosts software development to another level.

Demo TODO application will not be just simple TODO used for tech demos - we will add a certain level of complexity (such as user roles with different logic, for example), and finally, we will add test decent test coverage. Should you or should you not start with tests first is a matter of choice, this is just a tech demo.

So let's start with the requirements first:

Requirements

• Users can use email as username and password to log in.
• Users can be either admin or "normal" users.

Note: we will leave the user registration process out of the scope of this tech demo to keep the size of these articles manageable.

Item consists of:

• Description: alphanumeric characters, minimal two words or 6 characters.
• User assigned for doing this item.
• Status: TODO, DOING, DONE.
• Priority: HIGH, NORMAL, LOW.
• Category: Optional attribute that can be used for filtering. The list of categories will be pre-defined and later editable.

Users can see a list of items ordered by:

• Status: TODO, DOING, DONE.
• Priority: HIGH, NORMAL, LOW.
• Description ascending.

Users can filter the list by:

• Status: TODO, DOING, DONE.
• Priority: HIGH, NORMAL, LOW.
• Category (if any).

Users must be able to:

• Users must be able to see all items.
• Admin users can see a list of all users (items assigned to users other than currently signed users).
• Normal users can see only items that they are assigned (currently signed users).
• Admin users can change everything on every item (no restrictions).
• Admin users can delete and create all items.
• Admin users can change the assignment of an item to another user.
• Normal users can create items, and the system will automatically assign them to new items.

Project Setup