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.