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, welcome back. In this video, we’re going to be going through a coded example of the repository pattern in Python, and how we might write an application to communicate back and forth with our database. So if you remember this repository layer pattern or repository pattern, where we have our application up here, this is, this could be our unit tests in this case, or if you have a user interface, or application, any any form of application here, and then that application can then communicate to our database through the repository, which then has all of the behavior logic that is needed to query the person table or the address table or whichever tables that you actually contained here in the database through our access layer, which is the only class or the only layer that actually has a direct connection to our database, the repository layer it once a query has been executed, and when returned from the data access layer, the data being returned is then then transformed into the domain model, representing either a person object or address object in this case, and then that those objects then can be passed back to the application layer and be consumed.

Because remember, our application by itself has no, no way to map the database information to an object in order to utilize that data and its and the application in a meaningful way. And that is, the big reason why we have the separation here is that this, the repository pattern contains the logic that is needed to communicate to the database, and then transform the results that you retrieved from the database into a into a some form that is then consumable by the application. Alright, so without further ado, I’m just going to switch to a full screen, pi charm year. All right. So if you, if you are more familiar with Java, there is relatively easy mapping between the two, I have tried to write my Python solution so that it can be translated fairly easily enough to another object oriented language. But the IDE that I’m using here as well is pi charm, which is one that I would recommend for working with this adds a lot of extra neat and convenient features. On the left here, you can kind of see my, my layout of my project. And I will be giving this out to you all. So you’ll have access to this. But we have all of the source code, and then our test project. And then we have a few other things in here, the conflict test file for PI test constants, which is pretty useful for keeping track of common items. So like, which server I’m working with which database, I’m working with the driver that I want to use any username and password if you have that. And I will have some recommendations for username password sort of thing. So please don’t commit your password to get or anything like that. I’ll talk about in general, better ways to handling that as part of your application. But as part of this as well, I have a couple of PowerShell scripts. So these are scripts that can be that are going to be used in initializing our database.

So in our in writing applications, there’s a lot of times where we’re going to be talking back and forth to the database very, very often. And our code isn’t always going to be correct. And the code may not may not always be correct, even if our sequel is correct, the code behind it may not be correct. And so you want a way to have a test database that is structured exactly like what we would have on production. But something that we can just throw away and restart. And one reason why I use this script here is that anytime I run a test case, then I actually delete everything in my database and then re make it every time. That way. We start fresh just in case we had some error that happened in our test case, that is wiped clean. So in order to run this PowerShell script, there’s a few extra few things that you’ll need to do. Instructions on how to, to get everything installed is right here in the script. So if you don’t have it, if this command right here doesn’t work, then you’ll need to install the SQL server module for PowerShell. Up here at the top, these are the parameters that can be passed to the PowerShell command. So you can specify the server, the database, and the the directory, as well where the SQL files are located. The SQL files as part of the project are located under the source person, and then SQL. And inside SQL, we have some some data, some procedures, and some that so these are stored procedures, here’s our database schema, and then all of our tables as well. I’m not going to go through all of the dot SQL files here. For this video, I’m going to be focusing on the Python code. So if you are interested in what each of those do, just pop open all of the code and all the SQL code in these files, here are things that we’ve covered so far in class. But this PowerShell script is essentially running those dot SQL files on the database server for you. So we first drop all the tables, create the schema, create the tables, again, because we’ve dropped them all. And then we create all stored procedures, insert any test data. And then we have finished.

There’s two PowerShell scripts, one for building your local database, which is this one right here, you may need to change this to something different if you did not install SQL Express, um, you need to change this to whatever you’ve been connecting to using Azure Data Studio on this script here, this one is going to actually allow you to run this script and then connect to the CS departments, my SQL Server, MS SQL Server. So just change this to your E ID here. So your user and database that should be your e ID. In order for this to work properly, you will need to be connected to the VM. And then once you’re connected to the VM, then you can use this, I wouldn’t recommend doing this unless you have to. Because there are some extra steps that we have to take inside of our Python in order to connect to the SQL, MS SQL Server on the CS department. Because it involves a username and password. And that can not be as secure. Because we have to store that password as part of our program, or at least in some in some way, shape or form. So I would encourage you to try this locally. If you can install SQL server locally. If you cannot, please reach out. And we’ll be happy to help get you connected in your Python code to the SQL Server instance in the department. So that’s just the out of norm things here. And then, of course, there’s a requirements dot txt file here that you can that you will need to run to install all the dependencies for this Python project. And then the meat of this are these two things here. So here’s the, I need to delete my Hello World folder here. Delete that we have the data access folder, or the Data Access Project and the person project. So the data access I may switch to this year is this here. So we just have the inside here, we mostly just have this SQL command executor, which is going to allow us to communicate directly with our database. And then up here we have our repository layer, which is underneath person. So person is our repository later. This also contains our domain model layer, this project folder here.

So models, I just concluded that inside of person here, but in general, like I could rename this actually repository, and then models could be separated out here. So your folder structure doesn’t have to be identical to this, but you kind of get the idea. So my models are typical, just regular objects. So inside here that has everything Do with an address. So we have the address type enum, this is actually a helper table inside of our database, which is fixed data. So I just store those locally here. And then over here, we have our objects that were our class that represents a an address. And it just has some getters. So this is a very basic class that’s just used to represent a record in our address table. And so same idea goes here for the person as well. So let’s, I’m going to shrink this down a bit here real quick. Okay. So let’s talk a little bit about things going on in between, because a lot of the meat of everything is going to start with our data access layer. This here is just a custom exception that we made for handling some things from the database. But we’ll talk about that here in a minute. All right. So I’m going to talk for a little bit now about the data access layer. And in this case, it’s our class here. That’s called the SQL command executor. So the constructor here is going to contain all of the information that we need to connect to our database. And in particular, the library that I’m using to connect to the database here is pi ODBC. This is a Python package that Microsoft recommends for connecting to use SQL Server. At least as far as Python is concerned, there are different different libraries for Java, different libraries, for C sharp, and so on. But here is just creating the connection string that will need to connect to the database. Depending on your needs, and your installation, you may have to modify this lightly. But if you are working locally, then this should work for you as well. If you are connecting to the remote instance of SQL Server on, let’s say, CS, Linux, and the CS department, then this may not work for you.

So if that is the case, please do reach out. And we can help you fix this for that situation. But this class is relatively straightforward. I have a couple of big a couple methods here. And these two methods here are just helper methods that help create the SQL that I need for a stored procedure. And then I have a method here that executes a stored procedure, and a method here that executes a SQL query. So the stored procedure one is just for your for ease of use here takes the name of the procedure, the input, the parameters that you’re passing it, the names of them, the values, and then also the output parameters as well, if you have any. And particular, the output parameters are a little bit more complicated, because I need to have the, what they are locally, what the what types, they are in the database language, and then what they’re going to be returned as. So that’s what this helper method does is helps create the SQL for that. And I’ll actually showcase that here in just a few minutes. Then if we go up a layer, so this is the data access layer. Actually, first, I’ll talk about this get all rows here real quick as well. Pi ODBC is going to allow you to when when you execute a query like this, we have a connection object. That connection. Once you connect to the database, you have a cursor object, this cursor object is what you’re going to use to actually execute queries on the database. Once you execute them, then you can commit your results. And we’ll talk about transactions in another video. And then also be able to make sure you close the connection. Pi ODBC is pretty good that once once these once the connection. Once the scope leaves, then pi ODBC usually automatically closes the connection. But it’s very good habit to actually manually close your connections, primarily because you don’t want any extra stray connections open to your database server. But when you execute a query on the database, those results are actually stored as part of the inside of the cursor object. To retrieve those, I wrote a helper method here that retrieves all rows that are returned from executing that query. Now each query that you run as part of an ad execute command is returned as a result set. So, if you ran like, you know, if you ran three different separate queries in Azure Data Studio, you see, three rows affected five rows affected, or three rows affected. And so each one of those is a separate line, because that’s each one of those is a separate result set. So the result of a query. And inside of our result set, you have rows. And these are row objects that we’re actually fetching here. And so I’m actually just smashing all those together into a list, and then returning that. Cool.

So that is the gist here of the data access layer, I am going to be providing this code, all the exit all this code will be provided for you, and the emus this class and general and verbatim and your final project if you’d like, of course, you’re welcome to write your own, modify this reuse it as you need to. But this should provide you a very, it should provide you a very good way to get jump started into programming your final project. But let’s jump up a layer now into the repository later. So I’m gonna start out by saying that I did do two interfaces here, for one for address one for person. And again, we’re doing one per database, one per database table. But the big thing here that I’m trying to relate to is that you’ll want to use an interface here, because this allows you to make this makes everything a little bit more testable, because I can mock this repository object and substitute my own if I wanted to test something in isolation. So an interface is a very good practice here to implement. But let’s take a look at, let’s say, the person repository here. So this class, this class is going to have all of the behavior and logic for a for the interacting with the database objects in the database that are related to a person. So the constructor takes all of the server, the database server connection information, because it needs that in order to create a SQL command executor. But then each of these methods here correspond to one stored procedure. Um, except these last two, these are how I translate these last two methods or how I translate a row and the table or a list of rows. So a row from our database, or a row from multiple queries to a person. So this is how I translate the database results into an actual person object, then that person object. So this is the domain model layer, right? So repository layer, translates this into a domain model. And then that domain model then gets passed into the application. As we go through here, each one of these is talk is executing a stored procedure.

So here, all I’m doing initially here is that, we want to make sure first make sure that the data being passed to me, so the data that came from the application is clean. So we want to make sure that we clean up any of the make sure that it’s not empty or anything like that. And then we’ll want to then formulate that into the parameters for the store procedure, and then check the results. And that’s what we’re doing here. And pretty much each of these other methods here are roughly the same revelation, same general pattern here, we have crate person is probably the biggest one that we want to check out here because it has input parameters and output parameters. But these three down here are just retrieving, returning retrieving data from the database. So output parameters here, this is the exact structure that you need, and each one is separated by a comma here. Do note that and a very in a production environment, I would want to pass I want to I would want to filter my data that’s being passed to me. And make sure that it’s not anything that is malicious. Because a person that is entering data into a database or through an application, they could try to hack the server in order to gain access to information that they would otherwise not supposed to have access to. First For big, biggest weakness, there is usually SQL injection attacks where we could, the person could actually pass in SQL commands for the data parameters, and get those SQL commands being executed on the server. So just be careful about that. I’m not going to really cover that in detail for this class. It’s a little bit beyond the scope, current scope. But just be aware that that is something that you should be concerned about in a production environment. There are a lot of libraries out there, but I can help filter and cleanse your data before it’s being interrupted, or being passed to the database. So otherwise, here, I think most of this is straightforward. Once you start looking into and reading through, we have the stored procedure that gets executed, and the results are so the rows from that stored procedure are then translated into the model, and then passed back up into the application layer, the address, the address class is roughly the same, a similar similar pattern here. So I’m not going to really cover this in too too big of a detail. But I do want to show highlight here, the tests. So this is a you can treat the unit tests that I’ve created here as kind of our application our interface.

And so what I’m doing here, in my test cases, you can replace with your interface logic, my pattern here, I have just some Forgive me, my son is crazy about blue. So my fake people here are blue characters. But inside here, we just have some simple test cases for you to see how I interact with the, the repositories, and how I handled the return values of those repositories. But what the return values of that repository are just normal objects. And so those are relatively easy to actually interact with. So once you actually get the data back from the repository, that is very easily handled in application. So I could, in theory, display this to the screen inside of a UI, text, file, command line, whatever it may be. So you can kind of go through here and check how I’m actually interacting with the repository, passing parameters and things like that. Down here, if you want to utilize some of my my example, my example code, I actually have a method here that generates a random string. And so this is utilized as part of this method here, that just creates a random person out of the blue. So this can be very useful for testing your database out, if you want to utilize that. That’s actually is utilized really well inside of where I create an address here. So I can actually specify the size of the string. So for example, the states code, I can say two, and I can say that I only want ASCII, uppercase letters. This may not be actual an actual valid state, mind you. So there is some general logic flaws here, but you kind of get the gist. So let’s go ahead and execute one here. And I’m going to let’s see here. Let’s go ahead and run, create person. And I’ll put a breakpoint here. But I also want to show you conflict tests. So these pipes, I’ve made some fixtures here for PI test to help help out here. So these two fixtures down here, address repo and person repo. These are executed every time a fork for a function. So every time I actually do a test case. So every time I do a test case, the repository, the these repositories are created for that test case if they are needed. And then after the test case finishes, I reset the database and resetting the database is just executing that PowerShell script again. So anything that I actually saved in the database as part of the test case gets removed completely. So this is really useful for when you’re unit testing and database but do make sure that you only run this on your test database and not your production one, right. So just be careful about that.

Okay, and then up here at the top, I just also have one that runs a reset on the database that runs when you first start the test cases, just in case if you have any latent latent data that was in the test database in the meantime, before you run your test cases, okay, so now we know. So this address repo, or person repo that gets mapped to these parameters here. So if you have personal repo that’s person repo, any address, you have address, repo or and you can also have any, so these parameters map to the fixtures that are in contest, and but let’s go ahead and run this test case. And I’m going to actually debug it. Let’s see here. There we go. I’ll run it through here. Because let’s see here, test case personal repository. area. Okay, so let’s debug this one. Alright, so now we can actually track our way through and I’m gonna make this a little bit bigger here. So right now, we are just in the application layer, this right here. And as we step through here, I’m wanting to step into. So now I’m in the sequel, person repository, repository layer. And so inside my repository layer, I’m going to create all of my check all of my parameters, formulate those, so I can pass them to my stored procedure. So I’m going to step into this method call now. Let’s go there we go. Alright, so now I am in my data access layer, I went from my application to my person repository here, right? To my SQL, command executor. So inside of my SQL command, execute, executor, step over here, connect to my database, that’s first thing, then I’m going to I have some just if chain here that because the the SQL that you run, if you have parameters or no parameters is different. So here’s one with no parameters, this one is just input parameters, this one is out just output parameters. And this is both. And so if we step over, so now we have both.

So if I actually see continue here, now if I actually highlight over this, and see here, you can actually see, right, what my SQL command is, so I have declare Person ID, exec person that create person, and these are all my parameters, the question mark, so the question marks, which are injected up here, right? those question marks are replaced by the input parameter values list here. So chili, healer, chili h@test.com. So those are that’s, that gets replaced in those three question marks. And then here is my assignment to the output parameter, the parameter. And then this last one is something that I have to do in PI ODBC. So I have to actually select the output parameter in order to get it as a result back. So that’s what that last little slept does, right there. So that select is only needed if you have an output parameter. But then let’s go ahead and continue here. So if I, if I hover over these results here, you can see that my list is just one row because I just created a person. This one is that person ID. And so if I go back, here we are. So my length of results is one so I actually had a person made. And so then the repository so we went from application repository to DATA Act. says, now my repository is mapping the data through the domain model. Okay, so the domain model error is getting triggered here. So the domain model is getting wrapped up. And then that that model is then returned to my test case, you so now I’m back. But that is going to be the similar pattern for everything that you run here, you’re going to go from the applications at the test cases or your interface depending on which one you’re doing, you’re going to interact with the repository layer, posits, our layer is going to talk talk back and forth to the data access layer, that data is then going to be transformed into a model. And then that model is then passed back to the application. All right, so that is most of the code tour. As I mentioned, the code is going to be shared. So you can take please do take some time to walk through, debug a couple of test cases, run them, make sure you can actually make sure they run they should all pass, but trace the test cases. So you can actually trace see where you jumped through the code from the application layer.

So the test case, to the repository and the data access layer, and back and forth. But I do just have some general recommendations here. Please make sure you always clean up. So mainly, close any open connections to the database, that is the biggest thing that you need to actually accomplish there. Make sure you also commit. So if you’re working with transactions, of course, make sure you always commit or rollback pi ODBC and other libraries handle some of that for you with the auto commit, the data access layer that I provide for you also does that for you. Um, so you don’t necessarily have to worry about that as much. But nonetheless, that is there. As we’ve shown here, I do recommend the repository pattern with interface classes. So those interface classes provide the ability for dependency injection, if you need to. So that dependency injection allows you to substitute code in and out makes the code a lot more testable and a lot more flexible. Also one model per domain slash table. So although with that you may have some sub models, so like an order has order lines may involve more than one table, right, because like an address, right, an address involves address, address type, and so on, and so forth. So there may be things attached to that model. But all that is contained in the address domain. Likewise, you should only have one repository per model or one repository per domain.

All of that typically maps to one table in the database, typically. But there are a lot of libraries out there for working with applications and databases. I’ve covered one pi ODBC for Python, but there are lots out there. A lot of the ones that you’ll find include o RMS or object relational mappers these are going to abstract away a lot of the database work I try not I don’t want to push RMS in this class because I want you to get used to working with SQL but oh RMS are definitely play a big part in writing code for databases. So O RMS are very useful in that they will help automatically map the results from the database to a objects in code. So that transformation that we do in the repository pattern to the model that is handled by the Object Relational Mapper, the ORM can also write queries for you. So that it can be useful in some regard, although I will, I will say with with big Asterix there, RMS, their query ability is usually fairly weak when it comes to very complex queries. So especially when you get to joins and more complex relationships ORM start to fall apart a little bit but they can be useful for mapping results back to the original object model, but that is going to conclude the our discussion or our my presentation here on programming and application with a database. I know it’s short So please bear with me my voice is is almost gone here. But please take a look at the code. And if you do have questions, please reach out and we’ll be happy to help you make sense of the code and help translate and transfer that to your team project for the semester.