DB SQL Schema

The suite uses SQLModel's inpsect to run tests on the schema. One can also use SQLAlchemy's inspect too.

It is also possible, and maybe preferable, to use direct Python->DB SQL queries to test the schema.

It can be very useful to ensure than no unwanted changes to DB Schema take place during development and these tests ensure DB integrity.

SQL Queries

We can execute SQL queries natively to test SQL Schemas with regegular Python, however we have included SQLModel's inspect that already has a connection to SQLite for previous tests.

For more on SQL Schema queries for PostgresSQL, see Postgress Structural Testing.

SQLite does not support stored procesures but we can run tests as follows:

def test_SQL_014_execute_sql():
    """We can run SQL queries natively. SQLite does not support stored procedures

    If we have appropriate SQL Schema queries, we can use them to test DB Schema.
    """

    DB = "db.sqlite3"
    try:
        sqliteConnection = sqlite3.connect(DB)
        cursor = sqliteConnection.cursor()
        console.print(f"{DB} created and Successfully Connected to SQLite")

        sql = "SELECT * FROM catalog_author"
        cursor.execute(sql)
        records = cursor.fetchall()
        console.print(list(records))

        cursor.close()

    except sqlite3.Error as error:
        console.print("[red]Error while connecting to sqlite[/red]", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

This will yield for example: