Skip to main content

Demo: application of Flask SQLAlchemy Lite

Check the following link to review this demo of a complete Flask application.

app_fsqla_lite.py

This demo provides the following features:

  1. A log-in system powered by Flask Login.
  2. Two admin users and two regular users.
  3. Only the admin users can access the list of users.
  4. Each user has several entries.
  5. All users can access the entries belonging to them. Furthermore, the admin users can access the entries of any user.
  6. Accessing the user list or the entry details needs the log-in credentials.

Database structure​

The entity-relationship diagram (ERD) of the database is:

ERD of the Lite application demoERD of the Lite application demo

The database has the following features:

  • The default database backend is Flask SQLAlchemy Lite. If the Lite version is not available, fall back to Flask SQLAlchemy.
  • A one-to-many relationship between users and entries.
  • A many-to-many relationship between users and roles.
  • The role object-relationship mapping (ORM) contains a hybrid property is_admin that allows the fast query of the admin roles. The property is implemented by checking the level column.
  • The user ORM also contains a hybrid property is_admin that is implemented by the joined query. Query that property will search the users who has at least one role with the admin level.
  • The password of a user is saved as the hashed version. This form can prevent the passowrd leakage if the database file is leaked. To work with the hashed version, the user ORM provides two methods set_password(...) and validate_passowrd(...).

Access the demo​

Suppose that the examples folder is in the current folder, where the folder structure should be like this:

.examples
|---__init__.py
|---...
|---app_fsqla_lite.py
`---models_fsqla_lite.py

In the current folder (where examples folder is), run the following command:

python -m examples.app_fsqla_lite

The demo serves as an independent Flask application. The application can be accessed by a browser. However, here we would not do that, because we need to submit POST requests to login, and the login credentials need to be stored in the session.

Test the login status​

To test the application, we can use the requests package. Install it by:

python -m pip install requests

The first step is to test the status of the current user. Run the following script:

demo-login.py
import requests


# Recall that the address shown in the `app_fsqla_lite` is
# http://172.17.0.2:8080
addr = "http://172.17.0.2:8080/{0}"


def show_data(res: requests.Response):
    try:
        print(res.json())
    except requests.exceptions.JSONDecodeError:
        print(res.content)


if __name__ == "__main__":
    with requests.session() as sess:
        res = sess.get(addr.format("login"))
        show_data(res)

        res = sess.post(
            addr.format("login"), json={"user": "admin01", "password": "imadmin"}
        )
        show_data(res)

        res = sess.post(addr.format("logout"))
        show_data(res)

        res = sess.post(
            addr.format("login"), json={"user": "reader01", "password": "regular"}
        )
        show_data(res)

The script will login as an admin user first, logout, then login as another regular user. It can be found that the welcome messages to the admin user and the regular user are different.

Test the admin user​

Start another session by the following script, we will test the results when the current user is an admin.

demo-admin.py
import requests
import pprint


# Recall that the address shown in the `app_fsqla_lite` is
# http://172.17.0.2:8080
addr = "http://172.17.0.2:8080/{0}"


def show_data(res: requests.Response):
    try:
        pprint.pprint(res.json())
    except requests.exceptions.JSONDecodeError:
        pprint.pprint(res.content)


if __name__ == "__main__":
    with requests.session() as sess:
        res = sess.get(addr.format("login"))
        show_data(res)

        res = sess.post(
            addr.format("login"), json={"user": "admin01", "password": "imadmin"}
        )
        show_data(res)

        res = sess.get(addr.format("user"), params={})
        show_data(res)

        res = sess.get(addr.format("entry"), params={})
        show_data(res)

        res = sess.get(addr.format("entry"), params={"user": 2})
        show_data(res)

        res = sess.get(addr.format("entry"), params={"id": 1})
        show_data(res)

        res = sess.get(addr.format("entry"), params={"id": 3, "user": 2})
        show_data(res)

It can be verified that:

  • The admin can access the list of all users.
  • The admin can access the entry list of any user, not limited by the current user.
  • The admin can access the entry detail of any user, not limited by the current user.

Test a regular user​

Change the above example by a little bit, and use a regular user rather than the admin, then we will get different results:

The changes are taken for the following lines:

...
if __name__ == "__main__":
    with requests.session() as sess:
        ...
        res = sess.post(
            addr.format("login"), json={"user": "admin01", "password": "imadmin"}
            addr.format("login"), json={"user": "reader01", "password": "regular"}
        )
        ...
        res = sess.get(addr.format("entry"), params={"id": 1})
        show_data(res)

        res = sess.get(addr.format("entry"), params={"id": 1, "user": 1})
        show_data(res)
        ...

It can be verified that:

  • The regular user cannot access the user list.
  • The regular user is only able to access the list of entries belonging to the current user. Even if the user ID is specified as a different user, the specified user ID will be ignored.
  • The regular user can not access the entry of a different user. Even if the user ID is explicitly specified, the ID will still get ignored.

Test the anonymous status​

The only change is to remove the login request from the session:

...
if __name__ == "__main__":
    with requests.session() as sess:
        ...

        res = sess.post(
            addr.format("login"), json={"user": "admin01", "password": "imadmin"}
        )
        ...

Certainly, since the database is protected by Flask Login, any request without the login credentials will not be accepted.