Designing your DB Schema - GraphQL table design - Serverless SaaS Build Series 2.1

in this video we're going to be looking at the schema for the databases that i'm going to be using we're also going to look at the graphql schema as well as the dynamodb configuration that i'm setting up inside the serverless framework hi guys my name is sam with complete coding where our aim is to make you into the best aws and serverless developers that you can be in this video we'll be looking at the setup of the repo as well as the first stages of building this serverless linkedin automation tool so one thing to bear in mind is when i first started this project i was planning to use amplify for everything i spent quite a lot of time getting this project up and running and i found out that because of some of the complexities in the way i'm going to be working with graphql going with amplify was going to make even more work this is sam from the future and as i was editing this i realized that i didn't explain this bit very well so when i'm developing a system like this for a youtube video i actually write the code about three weeks in advance usually and that just means that the video that's coming out now is going to work with what i've planned for the future videos when i did this with amplify i found that creating the custom and more unique queries and mutations was a lot more complicated using amplify than if i went with just appsync so what i've chosen to do for this project is use appsync and the serverless framework as the api and cognito as the user identification and not using amplify to configure those so i'll get back to the video now and we can actually look at the schemas to visualize how each of the different entities interact i've created this little diagram and in here i've just included the fields that are needed for the relationships between each of the entities obviously there's going to be a lot more details on user on company etc etc so with the user we need to say a user has a list of companies now the easiest way would just be saying that a company has to be owned by a single user and all of the companies are the companies that you own but in the future we may want there to be multiple users associated with the same company and to that we need to have a relationships table the way that that works is that if you have a user and the companies you can say you can query this relationships table and say get me all of the relationships for this user id that will then give you a list of company ids which you can then use to query the companies table so that you now have this list containing all of the companies that this user have a relationship with as well as that we do actually still have an owner of a company and that is just going to be an id which is going to connect directly to a user the next thing that we have is we have posts on a company so in here there will be a post and that will be connected to the post entity or database and to get all of the posts we're going to query on the company id as well as that we have the same thing for the published posts and i've decided to separate these out as they're going to have slightly different functionality and i don't want to have a mess where there is one table with different kinds of data in it it's in amplify it's often simpler just to separate it we also have groups so this is a topic or a group of posts along a certain theme and in the same way we can get the group by company id the last thing is schedules so in here the company will have a list of schedules and these schedules will actually be associated to a group but you may want to be able to see all of the schedules for a company as well as that the last thing is how groups are associated so groups have a list of posts which if you come up here you can search on group id this time instead of on company id and that will return you a list of posts and in the same way you can also get a list of schedules on the group again searching by the group id so that is all of the relationships summed up in a simple way so then if we move over to our code this is the repo that i've created for this project and the way i've done it is if i minimize everything i have an apps folder and i have an api folder that just means that everything is going to be in one place and nice and easy to organize if i go into the api section which is the only section i've started so far it is a serverless repo and open here i've got the serverless.ts file that defines basically the whole of the serverless project i went with serverless.ts instead of using yaml because there are certain benefits in terms of being able to import and concatenate and those kind of things plus i kind of like the typescript way of working so i've installed a couple of plugins including like dynamo local serverless offline as well as the serverless appsync plugin in terms of controlling some other things if we go down to our functions this is one of the advantages of being able to use typescript is that you can use just normal javascript variables so here we've got functions we've also got cognito resources and dynamo resources and the resources i've just spread into a single object what this allows us to do if we scroll to the very top is just import those from other fair other functions and other files so for example if you look into source and functions at the bottom up here i have an object with confirm user signup and hello and if you go into one of these and index you can see the config that you'd normally see inside the root of the serverless yaml it's the handler environment variables and an i am role statement if we were doing this in yaml having these separated out to such fine-grained control would be a lot more difficult and it's a process that i quite like and it actually came directly from the surface framework templates and this is the aws node.js typescript template and this will have all of this import system set up for you in terms of the stuff i've added it's we go back to the serverless.ts the cognito resources and dynamodb resources can be found inside a folder i've created called serverless here we can see that we now have an object creating all of our dynamo resources which i'll talk through a little bit later as well as our cognito resources being able to split everything out into nice small folders makes everything a little bit easier to manage and doing it with typescript instead of yaml in my opinion is a little bit easier there's no incorrectly getting the indentation or something like that so it's the way i've gone with this project in terms of the schema i've left this model and key thing in from when it was an amplify setup but this isn't needed for appsync but for me it helps me know what the indexes are going to be on the data and as we can see we can have company's field which is associated to an array of company in company we have the ability to get it by company id but also we can get it by user id if we're looking for an owner so if we go down to the bottom of company we can see that we have both posts published posts groups and schedules but instead of these just being an array of posts or an array of published posts we've actually put them as post page and if we look down a little bit further we can see that post page and what this is is this is going to be an array of posts but only a subset of the total posts and then a next token what this does is it means that if we have 500 posts queued up we don't have to query database and return all 500 of the posts because that would be really really expensive in terms of database querying what we can do is we can return the first 10 or the first 20 and then return a next token so if we need more than that we can just query on the post page again passing in this next token and what that will do is return the next set of posts that way you can have a list that grows when you need it but only queries a subset when you only need part of that data so we've done the same thing for posts published posts groups and schedules as in theory all of these four entities could become quite a large list if you have a large or extravagant set of posts and topics that you're writing about if we go down to the posts what we have is we have a post id the company id a date and then a content field at the moment content is just text but in the future that may contain images or urls for videos or a lot of other things so this is actually just an interface and that's because both posts and published posts are very very similar so in terms of posts we have post company id group id date but we also have a ttl which is an optional parameter and can be used for our scheduled posts in terms of published posts it's very very similar but just misses that ttl object both of these can be queried on just the post id or if you can query it on the company id the group id and the date so that will allow you to do something like get all posts for this company and this group and then order it based on the dates the post was created this is how we're going to do our queuing system for posting again we have our groups and actually here this should be an array of schedules and we also have an array of posts and as well as that we have our schedule so in here we have the details we need for referencing it by either a company or by a group we have the ttl and the cron schedule and these are the parts that are going to allow us to time out using dynamo ttls and then reprogram the next post for the next time so now we've looked through the schema we also need to look at the dynamodb tables that we're going to be creating this is one extra step we do need to do because we're using app sync instead of using aws amplify as aws amplifier would have created the tables based on these models but this isn't a massively complicated process so here we have the user table and it's dynamodb table i'm going with paper request and the reason i'm going with that is at the start there's not going to be any real users so the amount of queries is going to be minimal and paying for reserved usage or even with auto scaling and all of that kind of stuff is going to cost more and with this i can actually work out exactly how much each query is going to cost i can then work out approximately how much it's going to cost for a user to do any of the actions such as planning a post or anything like that therefore i can work out how much it's actually going to cost for me to allow a user to use my services and i can use that to make sure that well this project is actually profitable so as well as having the billing mode we have the key schema and this just needs to be the schema of the attributes used in the index and because i only have one index on the user table of user id then that is the only one i need and it's a hash and we're saying that user id is a type of string we don't have any global secondary indexes but if we go down to the companies table the start looks pretty similar up to here but the difference is i also have an owner id attribute which is a string and that is because we do have a secondary index and this secondary index is indexed on the owner id this means i can do get all companies where owner id equals sam williams and if i want i can use the company id to sort that if i need to i then go on and i have the relationships table again this is very similar with the user id and company id based on having a secondary index as well as the relationships table have the posts published posts groups table and schedules table these all line up with the diagram that i did and talked through earlier and this will create all of these tables when they are deployed in this video we've looked at the schema that we're going to be using for the graphql endpoint we've also set up the repo created the dynamo tables and the graphql schema this is a really good start and in the next video or two we're going to be creating our custom queries as well as our mutations so that we can update and get data from our graphql api through appsync we'll also be setting up cognito so that we can authenticate and authorize our users if you're looking forward to one of those videos then make sure to subscribe and turn on the bell notification so you get notified when those videos are uploaded and of course if you have any questions about why i've done things the way i have just leave that in the comments below as i answer every comment on all of my videos so thank you and i'll see you in that next video

Channel: Complete Coding

Views: 886

Keywords: AWS, serverless, Serverless Framework, NodeJS

Id: ub_sUbmJxYA

Length: 18min 52sec (1132 seconds)

Published: Fri May 07 2021