So now that we've wrapped up the categories schema and page and API, let's go ahead and let's finally create the transactions. So head back inside of your schema file, so database folder schema, and let's export const transactions. We're gonna use pgTable again and make sure you have a matching name inside. The ID will be exactly the same. So we can copy that like this.
And our amount is gonna be interesting. So it's not gonna be a decimal, it's not gonna be a float, it's not gonna be the money currency. It's just gonna be an integer. So we have to add that from pgCore right here. Make sure you import integer and give it the same name amount.
And make sure it's required by adding not null. So, why did I choose integer for storing the currency in our database? Well, let's look at all the ways that we can store currency in our database here. So, the first idea that comes to mind is using floats or doubles but we know that this is a no-go because they do not offer precision. It might look good and easy to store it that way but the moment you start calculating you're gonna see that they're not very precise and this will turn into a bunch of errors.
The second option we have is using the decimal or numeric fields which come in Postgres for example. That is fine but the problem is that numeric or decimal does not exist in JavaScript so it has to be parsed as a string which will create some problems with our end-to-end type safety especially when it comes to forms. It will also require us to use additional libraries to handle this like decimal.js and it's also not cross-language compatible. If we change the database or if we change the language it will require us to change the complete additional library stack and also to find a way to support that in our database. So I settled on the third option which is using the integer of the smallest unit of the currency which would mean storing dollars as cents in our database which basically means storing it as an integer right But we're gonna go ahead even further and we're going to store it as milli units.
So milli units means that we're going to multiply our value by thousand. And this way we can support up to three decimals because later when we integrate Plaid which we'll read from our bank account they have a documentation about their amount property which says that it might return three decimals at one point especially if we are working with crypto values. So because of that, I made a decision to go with milli units. Means that when storing into the database, we are going to multiply the amount by a thousand and we are going to store that integer inside of our database. And then when we bring it back to the client, the client has to know that, and then the client will divide it by a thousand to display the number to the user.
So what we ensure here is that we work with types that exist inside of JavaScript ecosystem, and we also make it cross-language compatible. So if we ever change the database, we don't have to worry about whether the new database has the decimal field, right? Because all of them have the integer. And the same thing is for, you know, the programming language. They all work with integers in pretty much the same way.
So I think that's the right choice for this application. You can of course, you know, discuss and try your own solution, but I recommend that you follow exactly as I'm doing, especially because it's going to get complicated with all the calculations and stuff. So we have the amount, we're gonna have the payee option which is a very simple text which says payee. So I didn't make payee any kind of separate entity because payee can change from ATM to ATM, from cash register to cash register, right? So it can change depending on the location and everything it just varies too much so it's safest thing to do is to just make it a required string and then we're gonna have some notes which are gonna be some personal notes which user can write and they are of course optional.
Then we're gonna have a date which will be a timestamp option. Not time, timestamp from Postgres core so make sure you've added the integer, the pgTable and the timestamp here. Let's further define this by giving this the date field, giving it a mode of date and let's make it required not null. We are not going to give it any default values because this is something user will enter themselves. They can add transactions which will happen in the future or ones which have happened in the past.
So we don't want to pre-fill this for them. And now what I want to do is I want to create relations with transactions and categories and with accounts. So first let's go ahead and let's do this. So right now you can see that this transaction doesn't belong to anyone. There is no user ID here but we need to find a way for this transaction to only be loaded for that user.
We can do that by creating a relation with the account. So this is what we're gonna do. We're gonna write account ID. And we're gonna write text account underscore ID. So why text?
Well, that's because we use text for our IDs so our account has text as the type of ID so this is correct so we're going to have account underscore ID and it's going to reference to open a function here accounts which we already have dot ID so that's what it will reference right and now we're gonna add some additional rules here for example what happens if we delete the account well if we deleted the account I want to remove all the transactions that that account had. So we can do that by defining an additional option here on delete cascade like that. If you don't want that you can set that to be null or something else but here's the problem this is required for us right so we cannot do anything besides remove this because account ID field will be required we cannot do set null here because we are doing not null here so we are not getting an error but we will get an error later when this happens so make sure this is on cascade and we will also create a category id here so again text category underscore ID and this time we are only going to add a very simple on delete my apologies I didn't add the references at all so dot references here is a function which goes to categories.id and on delete here, we'll simply set it to null, meaning it's uncategorized.
So if we delete the category that this transaction is attached to, no point in removing the transaction, right? We can just set it as uncategorized so that's why this will not have the not null because it's optional right if we really want to we can leave the transaction as uncategorized But we are not done with defining our relations here, right? So we just have the references, but we haven't defined proper relations like one to one, one to many, right? So here is the documentation where you can find it just in case you're wondering where I got it from. So inside of Drizzle, Documentation, Access your data, we have Query and in here, we have Declaring Relations right here.
So we're gonna use this Relations Import. And inside of here, we can define either a one-to-one relation or one-to-many relation or many-to-many relation. So in our case, we're gonna work with one-to-many or like many-to-one. So let's go ahead and do the following. First let's resolve the accounts.
So we have to import from Drizzle ORM itself relations. So let's go ahead and let's add relations like that. And now in here, I'm gonna add export cons accounts relations like that that's going to be relations field which works on the accounts schema and inside of here we are going to destructure many and we are going to return an immediate object down here and what relation is going to have? A relation with transactions so one account can have many transactions like that perfect, so that's it for our accounts now we have to create the same thing for the categories so we can copy this let's go ahead and find this and we're gonna rename this into categories relations it's gonna work with categories so very important for you to change this and again so a category can have many transactions inside. Great!
And now we have to create the relations for transactions so let's go ahead and export const transactions relations here so that's going to be relations transactions and inside of here we're going to the structure one and return an immediate object here so each transaction can only have one account so accounts like that and now let's define the fields so we are working with transactions dot account ID is going to reference accounts dot ID so that's how we create a one-to-many relation here. We define the many there and in here we define that this field account ID references inside of the accounts this ID right here. Great! So we have that and now we can copy and paste this and rename this into categories. So categories work with categories, categories.id and this will be category ID.
Just like this. There we go. And now let's go ahead and let's export conf insert transaction schema to be create insert schema which we already have, but this time for transactions. And we're going to slightly modify it because date is a type of date, but we can't exactly work with that in JavaScript so we are gonna add we need to import Zod first we have to slightly modify it so it doesn't create errors in our forms because we're gonna reuse it all the way there so we're gonna write z.coerce.date there we go So this way we won't have any TypeScript errors with dates and there we go. You can see the date is now a type of ZodDate which is compatible with our hook form.
Great. Perfect. So we have that and now it's ready to generate that SQL and migrate it. So inside of our drizzle, last thing we did is we added categories. So let's go ahead inside of our terminal here.
Let me just see what this error is. Alright, so this is some tsconfig here. I think it will go away if I just reload my window. All right, let's get inside of the terminal. Let's shut down the app and let's do one run database generate.
And there we go. Three table accounts, categories and transactions. Let's see what happened now. So inside of our new SQL file right here, there we go. So we created the table transactions and we added foreign keys or relations to that transactions.
And let's go ahead and go inside of the terminal now and do bun run database migrate. Let's see if we are going to have any errors. No errors at all. Perfect. In case you ever get errors with the bun migrate, you don't have to worry.
You can always just delete your drizzle folder and that will restart the entire migration process. And if you still have errors then I recommend that you create a new database in NeonDB and connect again. Alright and now let's do bun run database studio just to check it out. So I'm going to go ahead and open this and now we should see the transactions and we should also see the relation. So if I go here, there we go, we have account ID and category ID and these are our relations right here.
So inside of my accounts, I also now have the transactions relation here. Same thing here. So these are going to appear as actual relations, which you will be able to click and the studio will redirect you to that. Perfect. Great.
And now I wanna go ahead and I want to build my API endpoint. So we're going to leave the front end for another chapter because the front end is going to be well slightly more complicated. So let's go ahead and let's do the easy thing which is to copy our categories right so let's copy the categories and let's paste them and let's rename them to transactions so we are doing this inside of the app folder api route right here we have the transactions so let's go inside of route.ts right here and let's go ahead and chain slash transactions and we have to import transactions. So import transactions from dot slash transactions and add them here. There we go.
So as always if I go ahead into my localhost 3000 API transactions it's now going to load categories instead, right? Because we have not changed anything. So let's go ahead and slowly do that. So I'm gonna go inside of my transactions and this is where we are going to work. So first things first let's remove the categories and insert category schema entirely and instead let's add transactions and insert transaction schema.
There we go And now we're gonna go ahead and resolve each endpoint one by one. So the first one is this one and it's immediately going to be different in a way that the the simplest GET request will have some param options. So we add a z validator here and we are specifically going to validate the query which is going to be optional. So this is going to be z.object. Let's just ensure that we have z imported.
We do. Great. So it's going to be z.object, which will accept the property from, which will be a string, and it's going to be optional. Then we're going to have to, which will be a string again and optional as well and we're also going to have an account ID which will be a string and optional as well. So as you can see we will be able to filter our transactions by dates and by a specific account ID but we don't have to do that if we don't want to.
Great! So our authorization stays the same but let's also do one more thing in here. Let's go ahead and let's use CRequestValidQuery and let's destructure everything we need. So from to and account ID, like that. Perfect.
And now in here, we're gonna go ahead and we're gonna find const default to, to be new date. So in case we don't have any filters from and to past, we don't want to load the entire history of transactions. That can be a huge amount of data. So we're going to default. If nothing was passed, we're just going to default by the last 30 days.
That's how most of this financial managers work. They always show you the last 30 days of data. And now we're gonna write const default from and we have to find a way to deduct 30 days from the new date. So we can do that with a library which we are going to use throughout the project called datefns. So npm install or bun add datefns.
Let's ensure we have that. Great. And now we can use sub days which I'm going to add an import for in a moment. So this is going to be sub days from default to 30 like that. Let's go ahead and import sub days from date FNS.
There we go. Perfect we have default to and now let's write const start date to check if we have from in that case, we're gonna use parse from date FNS. So make sure you've added parse import here. So we're going to parse the from value in a format of lowercase y-mm-dd, like that. And new date, like this.
And let's go ahead and do it like this. So we have more space. So if we have it that if we don't have it is going to use the default from. Now, let's define the end date here. To check if we have to if we have to is going to use parse to and it will be exactly the same as this one so we can copy that otherwise it's going to use the default to like that, there we go so let's go ahead and just confirm one more time that this is correct we have default to which is used as the end date and default from as the starting date.
Perfect. So that works fine. And we are format, we are parsing it in this way because that's what our drizzle is going to accept. Great. So let's go ahead and modify this now.
So first of all, well, I think it might be easier for us to actually build everything from scratch. So .select, we are specifically going to select ID, which is transactions.id. We're going to select date, which is transactions.date. Category. Now this is the interesting thing, right?
So if we write just category ID to be transactions.categoryID, this is fine, right? But on the front end, we are just going to get the ID, right? There's, That's not something we can work with. We don't want to display the user a random ID. I want to display the name of the category.
So I can do that by adding category categories from database schema dot name. But this by itself will not just work so let's go ahead and make sure you have all the imports you need transactions insert transaction schema and categories for now so this by itself will not just work we're gonna have to join the categories later great So that's why we are doing both. We are doing category ID so that we can, well, find that category in the array of categories, right? Because category name doesn't have to be unique. So it doesn't give us too much information except presentationally wise for the user so we need both and let's go ahead and go further let's add the payee to be transactions.payee let's go ahead and add the amount.
After the amount, let's add the notes. And then we're going to have the account ID here. And the same way we did it with the category we're going to have the account itself come from accounts schema dot name so just make sure you've added the accounts alongside categories here in the schema so we are going to select all of that from transactions and now we have to perform an inner join and we have to perform a left join so for accounts we are going to use the inner join because this will ensure that the both sides of the table relations exist right so we don't want to load transactions which don't have this account id so let's go ahead and do the following so we're going to write inner join accounts we already have that import and then we need the equals so let's just confirm that we have equals we do have from drizzle ORM so accounts has to equal transactions dot account ID and it needs to match accounts dot ID like that so that's the inner join we need to ensure that every transaction has a matching account ID. But for the category we're going to use the left join.
So for this we're going to write categories and it's going to be very similar. So equals transactions.categoryid categories.id. So in case a category doesn't exist that's fine we are still going to load this transaction right we are just not going to match any category ID or category name and that's fine because category is not required but account is required so we are using an inner join which means if there is a transaction which for some weird reason somehow doesn't have an account we are not even going to find it here so at least that's I'm not a back-end developer in my career I was a front-end developer So I'm learning this the same way you are here. So if you think that I explained this in the wrong way, feel free to leave a comment and see how you would explain it. And now let's chain a where method here and now we need to use our AND operator so just ensure you have AND, there we go.
So first of all we're gonna check if we have an account ID. If we have an account ID in that case we only want to load transactions with that account ID. So we ensure that it matches that variable otherwise to skip this we need to add undefined like this so that is the optional filter and now we're gonna add some required filters for example accounts.userid needs to match out user ID. So if we didn't add this inner join for the accounts, we would not be able to perform this filter right here. So remember, inside of our schema, transactions don't belong to users, right?
Transactions belong to account ID. So that's why we need to perform an inner join on the accounts so that we can check if that matching account has the matching user ID of the currently logged in user. So that's how we ensure that only the user's transactions are being loaded. So that's the first one and then we need a greater than or equals I think that's what this is a shorthand for greater than or equals I prefer let's see greater than or equal yes so greater than or equal and we're going to look at transactions.date, so when it was created, to and compare it to our start date. And then we're gonna copy and paste this but this time we're gonna use less than or equal with the end date and we need to import less than or equal from drizzle.org there we go, perfect And now we also need to add an order by method right here.
So we are going to order by descending. So we need that. So import descending from a drizzle ORM. Specifically, we're going to work with transactions.date. So we are going to, by default, order them by date.
There we go. So that is our query. So you can see how I wanted us in this course to learn more about working with SQL itself, rather than using the queries, a Prisma-like operator, which is obviously easier to work with, but we don't really know what's going on there. This time we are forced to understand this inner joins a bit better. I think you can even hover over the inner join and in here you can read the documentation.
So executes an inner join on the operation. Calling this method retrieves rows that have corresponding entries in both joined tables. So that's exactly what we need for accounts but for left join for example calling this method associates each row of the table with the corresponding row from the join table if a match is found if no match row exists it will set all the columns of the join table to null So that's fine for us because that's exactly what suits the categories field here because it is optional. Perfect! So yes, if you're confused, looks like they have great documentation here for these joins.
You can just read them. You even have some examples it seems here. Amazing! Great! So now that we resolved that, let's go ahead and let's resolve the individual ID fetch here.
So what I'm going to do is I'm just going to copy this select because I think we're going to need it here. So the querying can stay the same, right? We have the ID, the missing ID and unauthorized. We're not gonna have any querying here. So what we're gonna do is we are going to, yeah, the problem is we no longer have those errors, right, for categories.
So we're gonna have to be a bit more careful so in our get ID for the transactions this is what we are going to select so we need the ID and we also oh I think I forgot a very important field here in my first in my get route here so let's go back inside of here so after ID make sure you add date I completely forgot that we have to return the date right we want to show the user when this was created so make sure you add date inside of here so let's copy this again this time let's go to the bottom here well not to the bottom but just to the next one so let's see we have id we have date and we don't have to return the category name this time because this field right here will be loaded inside of the drawer, inside of the form and form can simply detect the category id and it's going to display the label by the select component so we don't need the category name and same is true for the account name so we just need the id, the date, the category id, payee, amount, notes and account id.
So again we are working from this time it's going to be from transactions and let's go ahead and let's perform the same inner join here for my accounts so I'm going to copy that and add it here so inner join with the accounts so we only ensure that all these transactions have the accounts and so that we can add some filters here and that filter is well primarily going to be this whoops my apologies let's go to the id so primarily we are querying by the not categories ID but transactions ID and then the second argument is going to be accounts.userid so be careful with this right don't make sure you don't have any misspellings here so inside of the where here we are querying by the transaction id because we have the id as the value here but we are also ensuring that this data that the user is trying to load exists in this inner join of the accounts and it has a matching user ID of that account. Great, so if there is no data, we return a not found here, perfect. And now let's go ahead and let's define our post method here.
So this one is gonna be a bit simpler. So inside of here, we're gonna do the following. We're gonna add insert transaction schema, but instead of using pick, we're going to write dot omit. So we want everything except the ID field that's not something I want the user to pass right so the values are fine and this is fine and we are going to work with database.insert transactions so not categories the id will stay the same and the user id doesn't exist so we remove that there we go perfect That is fine right here and now let's go ahead and let's perform a bulk delete right here. So the z-evaluator for JSON will stay exactly the same.
And now we have to do something called, I'm not sure what's the right expression. I think it's called a chained query. So we need to find a way to add a very specific, So we can't just query inside of this where to confirm that these transactions we're trying to delete has a matching account with a matching user id right it's a bit complex but Drizzle ORM does have a solution for that. So if you go inside of the documentation for the delete option so let me show you here so access your data delete they have something called with delete clause so using with clause can help you simplify complex queries by simply by splitting them into smaller subqueries So they are called common table expressions. That's the name.
All right. So that's what we have to do. We have to add this dot width so that we can make this where query be powerful because we want to be careful what the user can bulk delete, right? This is a powerful API endpoint and we need to ensure that it's protected. So this is what we are going to do.
Let's go ahead and do the following. I'm gonna go ahead and remove this returning and for the delete, I'm just gonna ensure that we are deleting transactions here, right? Now here's the problem. We cannot query where here at the moment because what we have to do is the following. We need to write const transactions to delete and let's write that to be our database dot with and let's go ahead and define this as transactions underscore two underscore delete dot as database dot select we are only choosing the ID from the transactions.id, from transactions, we are going to do an inner join on the accounts, where transactions account ID matches the accounts.id where we are going to add an end query inside immediately there are the values, there are our IDs, which we send here, IDs, are in the array of transaction IDs.
So transaction, my apologies, this transaction ID is in the array of our IDs that we are passing. My apologies, it's a tongue twister for me. Okay, so we ensure that we are only looking for transactions which have been passed in these IDs right here, right? But then we also ensure that accounts user ID are with the currently logged in user ID. That's important for us.
So we are not going to load these transactions if the user ID is not matching. So what this will create is it will create a list of IDs for us to query here so we're gonna add before the delete we're gonna add with transactions to delete like this and then we're gonna add where in array transactions.id and then we're gonna use something called a SQL operator here from drizzle ORM so that we can write raw SQL so that is here I'm going to move this to the bottom there we go this one we're going to go ahead and open backticks open parentheses and we're gonna write select ID from and we are going to inject the transactions to delete right here so let me show you this in one line like that there we go And then we're gonna add returning here. Id transactions.id So let's go ahead and let's go over this again. So we are attempting to delete transactions. But we are only gonna remove transactions where the id matches this list of ids and what is this list of ids right here?
Well that is a query which will join all the accounts so first of all we are only going to load transactions which have accounts and then we're going to ensure that it only loads transactions which have been passed in these IDs values and then it also ensures that it's only there where it has a matching user ID. Great! So that's what we are going to do here. Perfect! And now what we need is we need to resolve our patch request.
So let's go ahead and do that. So very similar to POST, it's not going to have Insert Category Schema first, it's going to have Insert Transaction Schema. Instead of PICK, it's going to have OMIT, and it's going to omit ID, like that. There we go. So let's go ahead and do the following.
So what we're going to have to do now is we're going to have to copy these transactions to delete because we have to use a similar query now because remember transactions don't belong to the user so we always need to do this deep querying by finding the matching account with the user ID. Right? So make sure you are in your patch right here. You've changed insert transactions right here. You validated the ID, the values, everything stays the same, but we don't need this at the moment.
Or let's keep it simple, just do it like this for now. Let's go ahead and paste the transactions to delete from our bulk delete above and we're gonna call this transactions to update here and we're going to change this to be transactions to update as well. So this can be the same so select transaction ID from transactions inner join of the accounts where in array and this is the cool thing So we're not gonna do in array here because this is not bulk update. This is just individual update. So we're just gonna do equals values.id.
Actually, it's not values.id, it's directly id because we destructure it right here from the param there we go so that's the transactions we can update so only the ones with the matching ID and where our user ID has a matching account user ID. Great. And now inside of here we can go ahead and chain .with transactions to update .update transactions because that's what we are updating set the values which are going to match perfectly because of our Zod validation here and we're going to do where in array transactions.id match the SQL open parenthesis select id from transactions to update again this is how it looks like in one line there we go and returning like that there we go So this now works fine and we are safely updating our transaction. So now we can go ahead and make this easier for us. We can copy this again, transactions to update here.
And this is the last one, to delete by ID. So the error handling here is fine. Let's immediately add this instead of transactions to update, this will be transactions to delete. So transactions to delete here, change it as well. And this is actually exactly the same, right?
Because this is just ensuring that the array of IDs that's going to be generated for our query is only of those transactions which the user should have access to based on their account user ID. So we have that, transactions to delete here. And now inside of here, let's go ahead and let's remove this entire query. So first of all with transactions to delete we are deleting transactions not categories as it was previously where in array we have transactions.id and we have a SQL open parenthesis transactions, Select ID from transactions to delete. There we go.
And let's also chain returning inside, but we are only going to return ID transactions.id. There we go. So if that was not found it means we probably didn't have access because we could not find it in this array which only loaded by that id and by our accounts. Perfect! That is it.
So here's what I want to do. I just want to ensure that we don't have any crucial bugs for example. So I'm going to write .from here and I just want to check that we are not accidentally loading something from accounts or from categories right because we copied this from categories so that's fine let's do dot update transactions that's fine let's do dot delete transactions transactions perfect I think our API endpoint is ready. Great, great job and see you in the next chapter.