Instant DB Web Apps

Here's how to create a basic web app for your database in 10 minutes.  Let's explore the created app, and then the creation process.

Created Web App

As shown below, the apps are

1. Multi-page: apps include 1 page per table.  The screen show below shows a List Customer screen (with query), a Show (one) Customer page, and a Show Order page.  Navigation controls enable you to transition between these pages.
2. Multi-table: pages include related_views for each related child table, and join in parent data
3. Favorite field first: first-displayed field is "name", or contains "name" (configurable)
4. Predictive joins: favorite field of each parent is shown (product name - not the foreign key product_id_)
5. Ids last: such boring fields are not shown on lists, and at the end on other pages

The Challenge: Conventional Approaches Not Agile

Conventional approaches where you create pages a field at a time with a screen painter, or use Wizards to create pages, will not create such an app in 10 minutes.  These also often require not only complicated coding, but also installing and configuring an IDE, frameworks, Web Servers, etc.  Not agile.

Instead, we need an agile approach that introspects the data model, and creates not only all the pages, but also the transitions.

Creation Process

Let's see how to create this app.

Background

As shown below, Flask AppBuilder (FAB) is a Python-based open source tool that uses sqlalchemy (an ORM for database access), and Flask (a micro web framework):

To drive FAB, you need to provide 2 model files:

1. models.py: describes your database tables
2. views.py: describes your pages

Each of these consists of a snippet of code you repeat for each table and page:

models.py

xxxxxxxxxx

class OrderDetail(BaseMixin, Model):

    __tablename__ = 'OrderDetail'

    Id = Column(String(8000), primary_key=True)

    OrderId = Column(Integer, ForeignKey("Order.Id"), nullable=False)

    Order = relationship("Order")

    ProductId = Column(Integer, ForeignKey("Product.Id"), nullable=False)

    Product = relationship("Product")

    UnitPrice = Column(DECIMAL, nullable=False)

    Quantity = Column(Integer, nullable=False)

    Discount = Column(Float, nullable=False)

views.py 

xxxxxxxxxx

class OrderModelView(ModelView):

   datamodel = SQLAInterface(Order)

   list_columns = ["ShipName", "Customer.CompanyName", ... "EmployeeId", "CustomerId"]

   show_columns = ["ShipName", "Customer.CompanyName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]

   edit_columns = ["ShipName", "OrderDate",... "ShipCountry", "Id", "EmployeeId", "CustomerId"]

   add_columns = ["ShipName", "OrderDate", ... "ShipCountry", "Id", "EmployeeId", "CustomerId"]

   related_views = [OrderDetailModelView]

appbuilder.add_view(

      OrderModelView, "Order List", icon="fa-folder-open-o", category="Menu")

While straightforward, these take time to learn, and are tedious to construct.  Happily, the 2 generators shown build these with a single command line.  Let's see how.

Process

So, the process is as follows.  First, you need Python3, along with virtualenv (a Best Practice for managing and separating project dependencies), and pip (Python installation utility).  You can install these in a minute or two as described here here.

We'll be using a sqllite version of Northwind (Customers, Orders, OrderDetails, Products, etc).

1. Create an environment

This creates a folder for our project, and an environment to manage dependencies:

xxxxxxxxxx

mkdir nw

cd nw

virtualenv venv

# windows .env\Scripts\activate

source venv/bin/activate

2. Create an empty FAB Project

Install FAB (and dependencies, such as Flask/SqlAlchemy), and create a default empty FAB app:

xxxxxxxxxx

pip install flask-appbuilder

flask fab create-app

You will then be prompted for the project name and your db engine type. When prompted:

• Use the default engine
• Name the project nw-app

You should see a structure as shown in the screen shot in the next section.

We now have a well-formed empty project. We now need to acquire and configure a database, set up SQLAlchemy ORM models.py, and describe our pages with views.py.

3, Configure Database

To get the database:

1. Download this file, a sqlite version of Northwind
2. Copy it to your nw-app folder
3. Update your nw-app/config.py file to denote this database name (illustrated below): Northwind_small.sqlite

Your project will look something like this (from VSCode, an IDE - optional, install it like this):

4. Create models.py

You must next provide model classes for SQLAlchemy. That's a bit of work (13 classes in this small example), but we can automate this with sqlacodegen:

xxxxxxxxxx

cd nw-app

pip install sqlacodegen

sqlacodegen sqlite:///Northwind_small.sqlite --noviews > app/models.py

This overwrites your nw/nw-app/app/models.py module. For more information, see the sqlacodegen docs.

5. Define views.py

Finally, we need to define some pages. That's also a bit of work to do that by hand, so let's use fab-quick-start to create the views.py file:

xxxxxxxxxx

pip install fab-quick-start

fab-quick-start run --favorites="name description" --non_favorites="id" > app/views.py

This overwrites your nw/nw-app/app/views.py file. For more information, see the FAB Quick Start Utility docs.

6. Create Admin

The FAB system can create tables in your database for authenticating and authorizing users (tables such as ab_user, ab_user_role, etc). You create these as follows (Username: admin, Password: p):

xxxxxxxxxx

(venv)$ export FLASK_APP=app

(venv)$ flask fab create-admin

Username [admin]:

User first name [admin]:

User last name [user]:

Email [admin@fab.org]:

Password:

Repeat for confirmation:

Ignore the error "user already exists", since the admin data was pre-loaded in the sample database.

7. Run `nw' App

You've now created a app with a dozen pages or so; run it like this:

xxxxxxxxxx

(venv)$ # still cd'd to nw-app

(venv)$ export FLASK_APP=app

(venv)$ flask run

Start your browser here.

Business Value

So, 10 minutes for a 13 table app.  It is a testament to the Python infrastructure that the process was not only fast, but also that it required no programming knowledge, nor often-formidable install / configuration skills.

There are several ways such automatic web apps can contribute to your project.

Agile: Collaboration With Working Software NOW

Agile is based on Collaboration based on Working Software, and there's no surer way to communicate than with running screens.

Approaches like FAB provide Working Software NOW, to kickstart your agile process.  Even if the ultimate User Interface is much different, collaboration over such early running prototypes can provide early clarification of the data model, and provoke discussions about the underlying business logic and process flow.

Data Exploration and Maintenance

In encountering a new database, developers typically require a database diagram.  But in my own experience, I need to explore the data - and their relationships - to really understand the database.   With automated navigation and joins, this is infinitely simpler than writing dozens of sql scripts.

Back Office Production Use

Most systems have a number of admin aka maintenance screens that are not strategic, yet are expensive.  FAB can make these basic apps free, leaving you more time to focus on the custom apps.  In fact, approaches analogous to FAB are used in many large-scale CRM/ERP systems.

Acknowledgments

Key software illustrated here include the following open source projects and authors:

• Flask App Builder, by Daniel Vaz Gaspar
• Flask, by Armin Ronacher
• SqlAlchemy, by Mike Bayer
• sqlacodegen, by Alex Grönholm
• fab-quick-start, the latest step in pursuing this vision

Next Steps

Follow this link for a Quick Start to Python, FAB, SqlAlchemy, and fab-quick-start.