Applications & Databases

Video Transcription

Welcome back everyone. In this video, we’re going to be talking about how we might use a database and programming application to use it. And first off as well, I do apologize for my voice. I’m kind of losing it today. But please bear with me, as we get through this things that we’ve covered. So far, we’ve covered quite a few things, running from single table and multi table queries, updates, deletes, and a lot of things in between. The last big topic that we talked about were routines. And we’ve seen quite a few different types of routines as well. So we’ve seen user defined functions, stored procedures, triggers, but stored procedures are really going to be our primary focus, when we’re working with databases, applications, primarily because stored procedures do encapsulate some of the logic behind some of the queries. So if you happen to be a developer or software engineer, and not a database designer, or database engineer, you’re going to be able to actually work with the database without having to actually write the queries. And so that provides a lot of flexibility. So you have your database administrators writing the sequel and the stored procedures. And then you have your software engineers who are developing the application logic that connect with that stored procedure. And a lot of that provides a little bit of extra security as well, because we can actually lock down stored procedures to certain people. And it allows us to do some error handling inside of our stored procedure. But remember, we’re not going to want to do a lot of error handling on the database side, a lot of that is going to be handled by our application. And in some scenarios, stored procedures do provide a little bit of performance benefit, when compared to things like user defined functions, and views, things that we can actually access from an application. And our database, includes tables views, SQL commands, stored procedures, and overall stored procedures, again, are recommended in most scenarios here. Views are useful in certain situations, but again, they’re just encapsulating some of the logic and abstracting some of that away, and you don’t actually get much performance benefit.

As a result, we can access our tables directly and modify them work with them run queries against them. But again, most of the time, we’re going to try to utilize stored procedures, instead of interacting with those tables directly. Now, again, we can throw errors in in SQL Server, and particularly with stored procedures. And we can handle those errors as well. But for most applications, we’re going to work with that on the application side and not the database, we’ll just use the database to actually execute our queries and do all of the database logic. And then if an exception happens to be thrown, that exception will be handled in the application side. But there are some errors that we can customize on the database and or handle front on on the database side, if it is something that is very specific. But we do want to leave, let the database do what it does best, and actually query and aggregate the data. And then we can take that data. And if there is any errors that have been produced as a result of that query, the application can handle it. And this actually does provide some performance benefit. We don’t want our database server being bogged down by handling a bunch of exceptions, or error handling. And we can put that on the application, which is typically separated from our database server. A lot of times those what we do with toy applications and things like that the database and the application are living on the same computer. But in the practical and production world. The database is going to be its own entire separate server system. And then the application will be served from its other this other server system, which provides some additional security and of course, performance benefits by keeping the application logic and the database logic completely separate. Initially, when we start working with databases, the first thing that you may have the inkling to do is, you know, write up a basic user interface and then connect directly to the database and start querying it.

But for the most part, this is not going to be the recommended way of actually interacting with your day. It’s less provides a quick and dirty way to do it and maybe easy to test a few things out. But in the long term in a production environment, this does not provide us with very much flexibility and doesn’t allow us to have very reusable or well written code, either. The primary pattern that I’m going to push or teach for this class is going to be the repository pattern. There are a lot of different other development patterns or design patterns for working with applications and databases or applications that work with databases. And they share some of the same ideas between them. But for now, let’s just focus on one, the repository pattern. The repository pattern has multiple layers built into it. And the idea here is that you’re going to try to separate the application logic completely away from anything that is actually interacting with the data. And so the repository is actually going to handle and in our situation, it’s going to handle structuring our queries or preparing our queries. So processing any parameters that we may be passing, and then also handling the return as well. And then that repository layer is going to communicate with a data access layer, which then actually pass which then actually executes the query on inside of the database. So the repository layer, in general, we’re going to have one repository per, per table, typically. But this may, this may include some sub ideas as well.

But let’s say everything that has to do with the person belongs in the person repository. Everything that has to do with an address belongs in the address repository. This includes any types of behaviors or aggregation of data that may end up for a person or for an address. And this is just based off of that sample database that we’ve been using inside of our lecture videos, not the wide world importers, but the temporary one that I’ve been creating. But so any queries or things like that are stored procedures that I want to execute, there is a connection point inside of the person repository, which handles passing the data needed to execute the query along to the data access layer, and then processing the return value, or the the data that’s returned from the data access layer, creating what we call creating a model or an object that then the application layer can then consume. So when we’re actually talking about this domain model layer, right, we dealt with objects, right custom objects that we created inside object oriented programming, both in Python and in Java. And so these you can just kind of think of as traditional objects, sometimes you will see a different design pattern where this domain model layer will actually will actually be referred to as aggregates inside here instead of individual models. But for our purposes, these models are only going to be serving as a application representation of the data being stored in our database.

So the person table gets translated into a person object, or a record or row from the person table in our database gets translated to a person object inside of the report repository layer and then fed back into the application layer, the data access layer is just as it sounds, it’s going to be the primary connection point to our database. And so the repository layer itself won’t actually have any connections to the databases won’t actually store the connection or have an active connection to the database, only the data access layer will maintain a connection. Now inside of the data access layer, for our examples, it’s just going to be responsible for executing stored procedures or any raw SQL queries that you may actually have, it is also responsible for connecting to your database when needed. So inside of this access layer, sometimes this is also referred to as the unit of work. data access layer is a little bit of an older design pattern. unit of work is becoming a little bit more modern approach. And when I when I say unit of work, the difference between the data access and unit of work It is typically with the data access layer, we’re actually passing on one, one command at a time to the database. But with the unit of work, let’s say we’re trying to do some data aggregation that it requires the execution of multiple stored procedures for multiple queries all at once. Right? That is one job, right. And so from the application layer, they just press one button to do one thing. And so that that is the unit of work, and so that that unit of work is passed along to the database as one unit, instead of being a whole bunch of different separate commands.

So we won’t refer to the data access layer as unit of work for our example, the data access layer for your first time learning of interactions with the database is a little bit easier to understand and work with overall. And the examples that I’m going to be talking about here in a little bit is the class equivalent here that I’ve created is called the SQL command executor. And that is essentially just one class that represents the data access layer. But that will conclude this initial video on the repository pattern. And next, we’re going to actually start taking a look at some code examples.

Applications & DB Example

Video Transcription

Hello everyone, and welcome back to another video for CC520 Database Essentials. This is a casual walkthrough of an example using Flask and Microsoft SQL Server. Much of what we’ve covered in this class focuses on writing SQL queries, running them against a database, and viewing the results. But in practice, databases are rarely used on their own. They are almost always paired with an application - often a web-based one.

For this example I’m using Flask, a lightweight Python web framework, but you could adapt this to Django, React, Vue, or other frameworks. My goal is to keep things simple and highlight the essential steps needed to get a database running and display its data in a web app.

Project Structure Overview

Let’s start by looking at the project structure. Much of it comes from the core template used in the class. The dev container includes everything needed to get your Flask server running, along with all database connection support.

For the Python example, I’m using the CC520 database. The WideWorldImporters database is also included if you want to experiment with larger queries. You can ignore the SQL Server admin database unless you need deeper configuration work.

Source Folder Components

data_access

The data_access folder contains the core functionality for running SQL queries. The main class is SQLCommandExecutor, which uses the msql Python library and pulls connection information from an environment file unless overridden.

It includes:

• Connection-building helpers
• Transaction support
• String conversion of parameters
• Methods to execute stored procedures
• Methods to execute raw SQL queries
• Methods for retrieving all rows from a cursor

This class should work as-is for your project.

db Folder (Database Seeding)

The db folder contains an example of how to seed your database. During development, schemas and data often change, so it’s useful to reset your database quickly.

The script runs:

• Schema SQL files
• Table creation SQL files
• Stored procedures
• Seed data files

To rebuild, run:

poetry run python -m source rebuild_db

There is also a “seed only” option to reload data without recreating tables.

The sample data lives in garden/sql/data. This data was generated using AI, and I encourage you to generate your own fake data this way as well - just cite the AI model used.

models Folder

The models folder contains classes that represent each table. Each class corresponds to a row in the table and lists all fields as properties. You’ll duplicate and adapt these models for your own project tables.

Repository Pattern Structure

Repository and Interface Layers

Inside the garden folder, you’ll see repository classes:

• sql_gardener_repository
• sql_plot_assignment_repository

These follow the repository pattern discussed in lecture. The project contains three layers:

1. Web Layer (Flask)
2. Repository Layer (business logic)
3. Data Access Layer (SQL execution)

Repositories inherit from interfaces that define required methods. This isn’t strictly required for the course, but it encourages good object-oriented design and improves code reusability.

You’ll implement the actual SQL calls inside these repository classes.

Web Application Layer

Flask App

The web folder contains a simple Flask application. Some HTML and CSS - like index.html and gardeners.html - were generated using AI to help with styling. You will write the logic that interacts with the database.

The web layer includes:

• API endpoints returning JSON
• Template-rendering endpoints
• Helper methods to access repositories

Test Cases

At the bottom of the project, there are test cases. Some were generated using AI. AI can quickly produce useful test cases, though you may need to correct or supplement them.

Running the Web Application

To run the app:

poetry run python -m source

You’ll see:

Running on 127.0.0.1

This means Flask is active.

Front-End Behavior

The web application uses:

• HTML/CSS (partially AI-generated)
• The DataTables library for interactive tables

DataTables provides pagination, searching, and sorting. For example:

• The gardeners page shows 30 gardeners across three pages.
• Searching “Tim” returns “Tim Berners-Lee.”
• Clicking a row triggers an API call to fetch plot assignments (only the first time; subsequent clicks use cached data).

Editing, creation, and update features are not included yet - you will add these in the assignment.

Exploring the Database in the SQL Server Extension

The SQL Server extension includes tools to view:

• Tables and their contents
• The table designer
• A schema visualization preview (still in preview, may have bugs)

The schema view shows tables like:

• Garden plots
• Plot assignments
• Gardeners
• Plot status

It also shows foreign keys, though not every constraint (like unique constraints).

Conclusion

That wraps up this video. If you have questions or run into issues with setup or running the web app, please contact me. Everything should run inside a dev container, whether in GitHub Codespaces or locally using Docker.