For this, we use one trick : derive HTML widget for presentation, database access, REST endpoints from ONE SOURCE of truth and we call it MODEL.
A tradition, and I insist it's a conservative tradition, is to use a declarative model where we mad the truth of the model from python classes.
By declaring a class we will implicitly declare it's SQL structure, the HTML input form for human readable interaction and the REST endpoint to access a graph of objects which are all mapped on the database.
Since the arrival of pydantic it makes all the more sense when it comes to empower a strongly type approach in python.
But is it the only one worthy ?
I speak here as a veteran of the trenchline which job is to read a list of entries of customer in an xls file from a project manager and change the faulty value based on the retro-engineering of an HTML formular into whatever the freak the right value is supposed to be.
In this case your job is in fact to short circuit the web framework to which you don't have access to change values directly into the database.
More often than never is these real life case you don't have access to the team who built the framework (to much bureaucracy to even get a question answered before the situation gets critical) ... So you look at the form.
And you guess the name of the table that is impacted by looking at the « network tab » in the developper GUI when you hit the submit button.
And you guess the name of the field impacted in the table to guess the name of the columns.
And then you use your only magical tool which is a write access to the database to reflect the expected object with an automapper and change values.
You could do it raw SQL I agree, but sometimes you need to do a web query in the middle to change the value because you have to ask a REST service what is the new ID of the client.
And you see the more this experience of having to tweak into real life frameworks that often surprise users for the sake of the limitation of the source of truth, the more I want the HTML to be the source of truth.
The most stoïcian approach to full stack framework approach : to derive Everything from an HTML page.
The views, the controllers, the route, the model in such a true way that if you modify the HTML you modify in real time the database model, the routes, the displayed form.
What are the advantages of HTML as a declarative language ?
Here, one of the tradition is to prefere the human readable languages such as YAML and JSON, or machine readable as XML over HTML.
However, JSON and YAML are more limited in expressiveness of data structure than HTML (you can have a dict as a key in a dict in json ? Me I can.)
And on the other hand XML is quite a pain to read and write without mistakes.
HTML is just XML
HTML is a lax and lenient grammarless XML. No parsers will raise an exception because you wrote "<br>" instead of "<br/>" (or the opposite). You can add non existent attributes to tags and the parser will understand this easily without you having to redefine a full fledge grammar.
HTML is an XML YOU CAN SEE.
There are some tags that are related to a grammar of visual widget to which non computer people are familiar with.
If you use a FORM as a mapping to a database table, and all input inside has A column name you have already input drawn on your screen.
Modern « remote procedure call » are web based
Call it RPC, call it soap, call it REST, nowadays the web technologies trust 99% of how computer systems exchange data between each others.
You buy something on the internet, at the end you interact with a web formular or a web call. Hence, we can assert with strong convictions that 100% of web technologies can serve web pages. Thus, if you use your html as a model and present it, therefore you can deduce the data model from the form without needing a new pivoting language.
Proof of concept
For the convenience of « fun » we are gonna imagine a backend for « agile by micro blogging » (à la former twitter).
We are gonna assume the platform is structured micro blogging around where agile shines the most : not when things are done, but to move things on.
Things that are done will be called statements. Like : « software is delivered. Here is a factoid (a git url for instance) ». We will call this nodes in a graph and are they will be supposed to immutable states that can't be contested.
Each statement answers another statement's factoid like a delivery statement tends to follow a story point (at least should lead by the mean of a transition.
Hence in this application we will mirco-blog about the transition ... like on a social network with members of concerned group.
The idea of the application is to replace scrum meetings with micro blogging.
Are you blocked ? Do you need anything ? Can be answered on the mirco blogging platform, and every threads that are presented archived, used for machine learning (about what you want to hear as a good news) in a data form that is convenient for large language model.
As such we want to harvest a text long enough to express emotions, constricted to a laughingly small amount of characters so that finesse and ambiguity are tough to raise. That's the heart of the application : harvesting comments tagged with associated emotions to ease the work of tagging for Artificial Intelligence.
Hear me out, this is just a stupid idea of mine to illustrate a graph like structure described with HTML, not a real life idea. Me I just love to represent State Machine Diagram with everything that fall under my hands.
Here is the entity relationship diagram I have in mind :
Let's see what a table declaration might look like in HTML, let's say transition :
<form action=/transition > <input type=number name=id /> <input type=number name=user_group_id nullable=false reference=user_group.id /> <textarea name=message rows=10 cols=50 nullable=false ></textarea> <input type=url name=factoid /> <select name="emotion_for_group_triggered" value=neutral > <option value="">please select a value</option> <option value=positive >Positive</option> <option value=neutral >Neutral</option> <option value=negative >Negative</option> </select> <input type=number name=expected_fun_for_group /> <input type=number name=previous_statement_id reference=statement.id nullable=false /> <input type=number name=next_statement_id reference=statement.id /> <unique_constraint col=next_statement_id,previous_statement_id name=unique_transition ></unique_constraint> <input type=checkbox name=is_exception /> </form>Through the use of additionnal tags of html and attributes we can convey a lot of informations usable for database construction/querying that are gonna be silent at the presentation (like unique_constraint). And with a little bit of javascript and CSS this html generate the following rendering (indicating the webservices endpoint as input type=submit :
Meaning that you can now serve a landing page that serve the purpose of human interaction, describing a « curl way » of automating interaction and a full model of your database.
Most startup think data model should be obfuscated to prevent being copied, most free software project thinks that sharing the non valuable assets helps adopt the technology.
And thanks to this, I can now create my own test suite that is using the HTML form to work on a doppleganger of the real database by parsing the HTML served by the application service (pdca.py) and launch a perfectly functioning service out of it:
from requests import post from html.parser import HTMLParser import requests import os from dateutil import parser from passlib.hash import scrypt as crypto_hash # we can change the hash easily from urllib.parse import parse_qsl, urlparse # heaviweight from requests import get from sqlalchemy import * from sqlalchemy.ext.automap import automap_base from sqlalchemy.orm import Session DB=os.environ.get('DB','test.db') DB_DRIVER=os.environ.get('DB_DRIVER','sqlite') DSN=f"{DB_DRIVER}://{DB_DRIVER == 'sqlite' and not DB.startswith('/') and '/' or ''}{DB}" ENDPOINT="http://127.0.0.1:5000" os.chdir("..") os.system(f"rm {DB}") os.system(f"DB={DB} DB_DRIVER={DB_DRIVER} python pdca.py & sleep 2") url = lambda table : ENDPOINT + "/" + table os.system(f"curl {url('group')}?_action=search") form_to_db = transtype_input = lambda attrs : { k: ( # handling of input having date/time in the name "date" in k or "time" in k and v and type(k) == str ) and parser.parse(v) or # handling of boolean mapping which input begins with "is_" k.startswith("is_") and [False, True][v == "on"] or # password ? "password" in k and crypto_hash.hash(v) or v for k,v in attrs.items() if v and not k.startswith("_") } post(url("user"), params = dict(id=1, secret_password="toto", name="jul2", email="j@j.com", _action="create"), files=dict(pic_file=open("./assets/diag.png", "rb").read())).status_code #os.system(f"curl {ENDPOINT}/user?_action=search") #os.system(f"sqlite3 {DB} .dump") engine = create_engine(DSN) metadata = MetaData() transtype_true = lambda p : (p[0],[False,True][p[1]=="true"]) def dispatch(p): return dict( nullable=transtype_true, unique=transtype_true, default=lambda p:("server_default",eval(p[1])), ).get(p[0], lambda *a:None)(p) transtype_input = lambda attrs : dict(filter(lambda x :x, map(dispatch, attrs.items()))) class HTMLtoData(HTMLParser): def __init__(self): global engine, tables, metadata self.cols = [] self.table = "" self.tables= [] self.enum =[] self.engine= engine self.meta = metadata super().__init__() def handle_starttag(self, tag, attrs): global tables attrs = dict(attrs) simple_mapping = { "email" : UnicodeText, "url" : UnicodeText, "phone" : UnicodeText, "text" : UnicodeText, "checkbox" : Boolean, "date" : Date, "time" : Time, "datetime-local" : DateTime, "file" : Text, "password" : Text, "uuid" : Text, #UUID is postgres specific } if tag in {"select", "textarea"}: self.enum=[] self.current_col = attrs["name"] self.attrs= attrs if tag == "option": self.enum.append( attrs["value"] ) if tag == "unique_constraint": self.cols.append( UniqueConstraint(*attrs["col"].split(','), name=attrs["name"]) ) if tag in { "input" }: if attrs.get("name") == "id": self.cols.append( Column('id', Integer, **( dict(primary_key = True) | transtype_input(attrs )))) return try: if attrs.get("name").endswith("_id"): table=attrs.get("name").split("_") self.cols.append( Column(attrs["name"], Integer, ForeignKey(attrs["reference"])) ) return except Exception as e: log(e, ln=line()) if attrs.get("type") in simple_mapping.keys() or tag in {"select",}: self.cols.append( Column( attrs["name"], simple_mapping[attrs["type"]], **transtype_input(attrs) ) ) if attrs["type"] == "number": if attrs.get("step","") == "any": self.cols.append( Columns(attrs["name"], Float) ) else: self.cols.append( Column(attrs["name"], Integer) ) if tag== "form": self.table = urlparse(attrs["action"]).path[1:] def handle_endtag(self, tag): global tables if tag == "select": # self.cols.append( Column(self.current_col,Enum(*[(k,k) for k in self.enum]), **transtype_input(self.attrs)) ) self.cols.append( Column(self.current_col, Text, **transtype_input(self.attrs)) ) if tag == "textarea": self.cols.append( Column( self.current_col, String(int(self.attrs["cols"])*int(self.attrs["rows"])), **transtype_input(self.attrs)) ) if tag=="form": self.tables.append( Table(self.table, self.meta, *self.cols), ) #tables[self.table] = self.tables[-1] self.cols = [] with engine.connect() as cnx: self.meta.create_all(engine) cnx.commit() HTMLtoData().feed(get("http://127.0.0.1:5000/").text) os.system("pkill -f pdca.py") #metadata.reflect(bind=engine) Base = automap_base(metadata=metadata) Base.prepare() with Session(engine) as session: for table,values in tuple([ ("user", form_to_db(dict( name="him", email="j2@j.com", secret_password="toto"))), ("group", dict(id=1, name="trolol") ), ("group", dict(id=2, name="serious") ), ("user_group", dict(id=1,user_id=1, group_id=1, secret_token="secret")), ("user_group", dict(id=2,user_id=1, group_id=2, secret_token="")), ("user_group", dict(id=3,user_id=2, group_id=1, secret_token="")), ("statement", dict(id=1,user_group_id=1, message="usable agile workflow", category="story" )), ("statement", dict(id=2,user_group_id=1, message="How do we code?", category="story_item" )), ("statement", dict(id=3,user_group_id=1, message="which database?", category="question")), ("statement", dict(id=4,user_group_id=1, message="which web framework?", category="question")), ("statement", dict(id=5,user_group_id=1, message="preferably less", category="answer")), ("statement", dict(id=6,user_group_id=1, message="How do we test?", category="story_item" )), ("statement", dict(id=7,user_group_id=1, message="QA framework here", category="delivery" )), ("statement", dict(id=8,user_group_id=1, message="test plan", category="test" )), ("statement", dict(id=9,user_group_id=1, message="OK", category="finish" )), ("statement", dict(id=10, user_group_id=1, message="PoC delivered",category="delivery")), ("transition", dict( user_group_id=1, previous_statement_id=1, next_statement_id=2, message="something bugs me",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=2, next_statement_id=4, message="standup meeting feedback",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=2, next_statement_id=3, message="standup meeting feedback",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=2, next_statement_id=6, message="change accepted",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=4, next_statement_id=5, message="arbitration",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=3, next_statement_id=5, message="arbitration",is_exception=True, )), ("transition", dict( user_group_id=1, previous_statement_id=6, next_statement_id=7, message="R&D", )), ("transition", dict( user_group_id=1, previous_statement_id=7, next_statement_id=8, message="Q&A", )), ("transition", dict( user_group_id=1, previous_statement_id=8, next_statement_id=9, message="CI action", )), ("transition", dict( user_group_id=1, previous_statement_id=2, next_statement_id=10, message="situation unblocked", )), ("transition", dict( user_group_id=1, previous_statement_id=9, next_statement_id=10, message="situation unblocked", )), ]): session.add(getattr(Base.classes,table)(**values)) session.commit() os.system("python ./generate_state_diagram.py sqlite:///test.db > out.dot ;dot -Tpng out.dot > diag2.png; xdot out.dot") s = requests.session() os.system(f"DB={DB} DB_DRIVER={DB_DRIVER} python pdca.py & sleep 1") print(s.post(url("group"), params=dict(_action="delete", id=3,name=1)).status_code) print(s.post(url("grant"), params = dict(secret_password="toto", email="j@j.com",group_id=1, )).status_code) print(s.post(url("grant"), params = dict(_redirect="/group",secret_password="toto", email="j@j.com",group_id=2, )).status_code) print(s.cookies["Token"]) print(s.post(url("user_group"), params=dict(_action="search", user_id=1)).text) print(s.post(url("group"), params=dict(_action="create", id=3,name=2)).text) print(s.post(url("group"), params=dict(_action="delete", id=3)).status_code) print(s.post(url("group"), params=dict(_action="search", )).text) os.system("pkill -f pdca.py")Which give me a nice set of data to play with while I experiment on how to handle the business logic where the core of the value is.