SIDE: Creating a User Interface

Building a User Interface to our database

We are going to use mainly two different approaches to interacting and using our database:

• Command line tools (CLI)

• A Web-based user interface, without thinking about web technologies…

While not related at all to database development, we will be discussing our interfaces as we go along.

The CLI based ones will be fairly simple and we will seldom discuss them as they are mostly obvious. You can run a simple example on the basic branch by doing

uv run example.py

Have a look at the code there.

The web interface will be done with Streamlit. We will not be doing anything complex with front-end technologies - Just the fundamental to have an intuitive user interface.

Our Web interface

Here is how our Web interface will look for now:

Results on the bottom and entry at the top.

You can run this by doing:

uv run streemlit ui.py

Remember that the parser is flimsy….

Explaining the implementation

The code is quite simple:

Here is the main function:

def some_app() -> None:
    st.set_page_config(layout=”wide”)
    st.title(”Some Database”)
    with st.sidebar:
        st.header(”Navigation”)
    st.write(”Welcome to Some Database”)
    if “text” not in st.session_state:
        st.session_state[”text”] = “”
    user_input = st.text_input(”SQL input”, “”)
    if user_input:
        try:
            result = execute_statement(user_input)

            match type(result):
                case _ if isinstance(result, SomeNone):
                    st.session_state[”text”] = “Done”
                case _ if isinstance(result, SomeSelectResult):
                    st.session_state[”text”] = result
                case _:
                    st.session_state[”text”] = f”Unknown result type: {type(result)}”
        except ValueError as e:
            st.session_state[”text”] = f”Error: {e}”
    st.markdown(”### Result”)
    st.html(rich_to_html(st.session_state[”text”]))

This is a simple standard streamlit application. If you need details check the streamlit web site.
The only line that is less common is the very last one. We will be using rich, a Python library to generating visually appealing text output - normally using in console applications, not web. This will allow us to generate nice looking web output without knowing much about web presentation. For example, it is able to add colors to our Pydantic output above.

Here is the code:

def rich_to_html(renderable) -> str:
    console = Console(record=True)
    console.print(renderable)
    return console.export_html(inline_styles=True)

This is quite basic and with time I might improve it. But it is hardly the main topic of this Substack, so I will only discuss it if there is something that I find really interesting…