Instant DB Web Apps
Instant DB Web Apps
Create a multi-page, multi-table web app in 10 minutes.
Join the DZone community and get the full member experience.Join For Free
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
- 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.
- Multi-table: pages include
related_viewsfor each related child table, and join in parent data
- Favorite field first: first-displayed field is "name", or contains "name" (configurable)
- Predictive joins: favorite field of each parent is shown (product name - not the foreign key
- 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.
Let's see how to create this app.
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:
models.py: describes your database tables
views.py: describes your pages
Each of these consists of a snippet of code you repeat for each table and page:
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.
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:
2. Create an empty FAB Project
Install FAB (and dependencies, such as Flask/SqlAlchemy), and create a default empty FAB app:
You will then be prompted for the project name and your db engine type. When prompted:
- Use the default engine
- Name the project
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
3, Configure Database
To get the database:
- Download this file, a sqlite version of Northwind
- Copy it to your
- Update your
nw-app/config.pyfile to denote this database name (illustrated below):
Your project will look something like this (from VSCode, an IDE - optional, install it like this):
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:
This overwrites your
nw/nw-app/app/models.py module. For more information, see the sqlacodegen docs.
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
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_role, etc). You create these as follows (Username:
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:
Start your browser here.
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.
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
Follow this link for a Quick Start to Python, FAB, SqlAlchemy, and fab-quick-start.
Published at DZone with permission of Val Huber , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.