GraphQL Workshop Medley to build cloud native apps with Open Source Distributed SQL database

Welcome everybody. My name is Eric Pratt. Today we're going to be going over our graphql workshop medley. We're getting started with distributed SQL, and some popular graphql servers. My name is, as I said, Eric Pratt. I'm a senior solutions engineer here at Eagabyte on the cloud team. Previously, I was a premium support engineer over at Datastats, two others with me, and I will let them introduce themselves. Hey, everyone. My name is Nikhil. I'm one of the engineers on the ecosystem team. We build integrations with popular developer tools like graphql, Spring Framework, and host of other cloud-native projects. If you have any questions on the integrations with distributed SQL and graphql, you can reach us out on our Slack. That's what we do daily in our engineering efforts. Prior to this, I was at Ubuntu. I was a senior data architect there building cloud-native solutions. Thanks, Brad. Hi, everyone. My name is Marco Rejcic. I'm also a solutions engineer here at Eagabyte. Previously, solutions engineering at Oracle focused on both cloud and on-premise technologies. So excited to speak to you today. As Nikhil mentioned, we're very active on our community Slack channel, so if you're able to download Eagabyte locally or using it out or whatever, have any questions, feel free to find us there. All right. So this is our workshop agenda. We're just going to give you a brief overview of getting started with distributed SQL databases and graphql. We'll kind of have a brief overview of our open source database, our open source offering for Eagabyte. And then we're going to do some hands-on sessions for implementing these. So you're going to do a real-time poll app. That'll be with Marco and Asura. For me, we have a social post app with prisma and Eagabyte. And then finally, Nikhil will be doing a space explorer app, which I think is pretty cool with apollo platform and Eagabyte. At the end of those, if we have some time, we will be going over tuning performance for our graphql queries with distributed SQL and some do's and don'ts that we've kind of come across as we've worked with customers over the last year or so. So getting started with graphql, we have your app, we have your graphql server, our database. We have abstract layers that kind of go over our database. So you can query and mutate data via the graphql constructs, build your schema and involve the domain models. We can involve the api without versioning, which I think is kind of nice. And then you have out-of-the-box pagination filtering collate disparate data sources and then friendly inventing support. So the graphql is just query language for your api. You build your queries, you can get exactly what you need. You can combine a few different resources in a single request. It's pretty robust. And like we said previously, you can involve your api without versioning and it supports an event-based system. So we kind of have an example here of a query. If you guys are familiar with graphql, this should look pretty familiar. And then it has your generic REST APIs. So your gets, posts, it supports all of those and there's kind of some examples that we have. We'll have those work with a particular response. And then for graphql, we have our ways for retrieving data, which is our posts. You can see the request there. Here's the query that we would run on this side. So let's say we want to get the author name and articles from a particular thing, table, and then we have that response here that you'll get. As we work through a lot of these examples, or a lot of these different workshops, we'll be running through quite a few of these. So you'll get kind of familiar with those for the different graphql, like Asura, prisma, and then apollo. So you get to kind of see what each of those has and how they kind of interact with Nougabyte. It's pretty cool. I think you guys are going to like it. So I'm going to turn it over to Marco here because we will be going over the open source distributed SQL database and how kind of the fundamentals of the Nougabyte. So before we get into the fun part of the live demo, we wanted to just catch everybody up what we're doing here at Nougabyte DB, why we feel this is important for cloud-native applications, and really what Nougabyte DB is for those that are unfamiliar. So Nougabyte DB is an 100% open source distributed SQL database. Our goal is to make it the go-to cloud-native database for cloud-native applications. We're really trying to bridge the two worlds between traditional SQL and the strengths that those types of systems provide with the strengths of typical NoSQL systems. Our founders have vast experience with both Oracle as well as building out Cassandra and HPace at Facebook in the mid-2000s. If you guys know anything of Facebook in the mid-2000s, that's kind of where they had this crazy user growth and really had to start moving towards NoSQL systems because the traditional database systems that they were using just could not scale to what they wanted. And obviously this was very new for everybody running on the internet. Once this type of user growth really started showing, SQL systems could not scale to the speed at which you needed them to. So by being able to continue to provide strong SQL features with our Postgres SQL compatibility, feature compatibility, and asset transactions, security, all major factors of why users like SQL systems in the first place, whether it's Oracle, SQL Server, MySQL, Postgres, these are always the really important things. You want strong SQL features, you want good performance, you want strong security, and you want to have asset compliance and data integrity. The downsides of those types of systems is that although there's some resilience and high availability, most of it's bolt-ons, you have the ability to scale horizontally, but you have to use manual sharding. It's not a core feature of the database, and it wasn't created with the database as something that was going to be important. And then geographical distributions, typically you're going to have to have separate instances of your database in order to be able to accomplish that. So being able to take on the benefits of the NoSQL world where these three things really are something that those systems are good at. You have a cluster running multiple nodes where high availability can be taken care of. If one of those nodes goes down, you have other nodes that are able to take on that traffic. You're able to scale horizontally. That's the key piece for those systems and why they were built in the first place. Scaling reads and writes, and then geographical distribution. So the downside of NoSQL systems, obviously, is that phrase NoSQL, where although some of these systems continue to add SQL features as time went on, they don't have full SQL compatibility. They're still missing a lot of features that are used in applications today. They don't have data integrity or the ability to run multi-node transactions and make sure they're asset-compliant. So really bridging those two worlds together, making sure that you have strong SQL features, you have data integrity, but you also at the same time have built in HA, horizontal scalability, and the ability to run your database on any cloud or your own private data center along with different regions across the entire world as you kind of grow as a company. The first real fork in the road that we came into was, all right, if we're going to design the perfect distributed SQL database, what route do we want to go down? What database do we want to focus on and use that kind of as inspiration for our query layer? What we decided to do is go with Postgres SQL. We saw a lot of popularity. We loved the community. As this visual on the left-hand side here shows, from 2013 to where we're at today, this visual cuts off at 2019, 2020, but even to the end of 2021, we see Postgres popularity really growing and growing more so than some of the other NoSQL systems out there that have been extremely popular in the last five, ten years too. So we really wanted to be able to continue to build off of that. What we did is we reused the Postgres query layer similar to the approach that Aurora took. Aurora allows you to reuse your MySQL and Postgres applications without a lot of changes, similar to you by DB if you're coming from the Postgres end. That's a little bit different than what Spanner did. Google Spanner was one of the first distributed SQL databases, but their approach was, hey, we're going to build a new syntax. The issue that they ran into is that a lot of RDBMS features, a lot of SQL features, users are still looking for. We really saw Amazon Aurora benefit of that as time went on, and Aurora today is much more popular than Google Spanner. We saw Google Spanner actually in the last month come out with more Postgres SQL feature functionality, because I think they've noticed that that's going to be something important that they're going to have to introduce too. So you go by DB blends these two things together with the Aurora side and the query layer. We took their approach where we were like, hey, we want users, we want the least amount of friction as possible for new users coming to you by DB. We want to make sure we have all of the SQL features that Postgres provides. We want to have the ability for users to continue to use the same drivers, et cetera, in order to make the least amount of changes necessary in order to run on you by DB instead of developing a new SQL syntax, instead of rewriting Postgres or any other open source database in a different language. The second piece with you by DB is the storage layer. The query layer, we took the Aurora approach. The storage layer, we really took the Google Spanner approach because we wanted both scalability and HA. If you know anything about Amazon Aurora, you know that although it scales reads really well, scaling isn't nearly as up to par. So using Google Spanner, using RAF consensus, we're able to slowly add more nodes to your cluster until you need it. And just to kind of double click on the RDBMS feature set, this is what's supported today on Yougabyte from version 2.9. You can see a full list of this on our docs as well. But things like stored procedures, triggers, role level security, user defined types, et cetera, you'll see that. Not many NoSQL databases, if any. New SQL, as well as other distributed SQL systems aren't going to have that because it's almost impossible to do without reusing the Postgres SQL code itself. So if you're using any of these today, rest assured you can use them with Yougabyte DB as well if you decide to make that transition. Now to double click on the scaling portion. Over here to the left is what you have seen for years now. Your typical, hey, we have a single server database and we have the clients coming in, all our data is on a single node. If we want to scale to be able to handle more client traffic, we're kind of bottlenecked. Depending on whether you're about storage or if it's CPU or if it's RAM, really all you can do is increase the size of your instance, scaling vertically, and eventually you get to this crossover point where it just becomes too expensive. Or maybe the physical limitations are there. You're kind of relying on manufacturers of these servers that are choosing your CPU and RAM relationships. And sometimes that's kind of what makes you think, okay, now I've got to start sharding. Well, up to this point, you would have had to shard manually if you're using MySQL or Oracle. They have the ability to shard, but they don't do automatically sharding for you like Yuga by DB does. You have to manually shard your data across a cluster and you have to manage all that. Anytime there are changes, you're going to have to go ahead and make those changes to the shards themselves and manually shard it to the application. This is very time consuming. If you're somebody who is doing this today or has done this in the past or has even just heard about it, it's very time consuming and it takes a lot of experience as well. So finding the people that know how to do it and how to do it right is also extremely challenging. So Yuga by DB's goal was to take this away from the user and make sure that the database itself can automatically do this. So every time you create a database in Yuga by DB, if you take a look over here on the right side, we split that data into chunks or shards, and then we distribute those shards across all of the nodes in your cluster. The cool part about this is that now every single one of these nodes is going to be acting as the quote unquote master node, accepting client traffic, having its own CPU and RAM that is able to serve that traffic. Anytime that you need to scale, you can still vertically scale if you'd like. Go from two cores to four cores, there's eight, 16, 32, etc. Or you can just add more nodes to the cluster. And these nodes can be in the same region, it can be in different regions. We're very flexible when it comes to the type of topology that you're having. So the key part is all of these nodes are identical. Every node has its own Postgres query layer, it has its own DocumentDB storage layer running on the Yuga by DB node within a single cluster. So if we take a look at a user on their cell phone, if you have a mobile application or it's a web application using graphql connected to something like a Hussura graphql engine, and then Hussura communicates to the back end Yuga by DB engine, it communicates with that query layer that's Postgres compatible. As you'll see when I run through the Hussura demo, we'll be using the Postgres connector itself. There's no special Yuga by DB connector, we're using the exact same one. And then that communicates with the data. So connecting to any node, Yuga by DB will cache the different shard locations and be able to direct the traffic to where it needs to go. And then you can add or remove nodes anytime without any downtime. So that's different than how you'd have to do with traditional systems. Let's say you had a monolithic application and a single server database, you'd have one app connected to graphql, connected to the graphql server, hitting that single node Postgres instance in this example. Now if we take a look at what it's going to look like with Yuga by DB and graphql, as you kind of see today, you're broken up into microservices, each one using graphql APIs, connecting to the same cluster as you build out more microservices and you build out more APIs, you can hit the same cluster and add nodes to that cluster. So you're not stuck on a single node. If you start with a three node cluster, you can grow that to a six node cluster or a nine node cluster, et cetera. So even if you're running a lot of times in our conversations, users will create microservices for their application code and they'll break their massive application code into different sections. And sometimes they'll still be running it on a single node database, whether that's Postgres, MySQL, Oracle. It doesn't really matter because at the same time, all of them are kind of, it's the same type of system, right? It doesn't scale as easily. So a key part to any type of modernization effort for your applications is going to be the database because really what we're doing is if you continue to run it on a single node instance, you're eventually going to run into another bottleneck, right? Because that's a benefit that YugaByDB provides. And there are different topologies with any YugaByDB cluster. Every YugaByDB cluster has a minimum of three nodes. So if we assume every single one of these little visuals is a single node, you can run across availability zones, right? So availability zone one, availability zone two, availability zone three, your data is being distributed across these three nodes running in three different availability zones and is being synchronously replicated and using graph consensus to make sure that there's data integrity between them, right? So you have consistency across zones, but obviously you don't have the luxury of region-level filters. That's where the regional topology comes in. If you want region-level redundancy, you're going to want to have your nodes in three different regions rather than three different availability zones. Technically, they can be three different availability zones as well, but they're going to be in three different regions, right? That's kind of the key there. We're kind of expanding the distance that we want to be protected for. And then the last one is multi-cloud. If you want to run on aws and GCP and azure, and you want to have X amount of nodes in each, you can do that as well. And that obviously takes it to another level, right? Where if something were to happen to a cloud provider in a particular region, you have other cloud providers that, or other nodes and other cloud providers that can, will make sure that your cluster is still open. And these clouds can actually be a private data center as well. So this doesn't necessarily have to be a public cloud like aws. If you have your own data center, you can have a node in your own data center and then other nodes that are maybe in public clouds. One of the key areas of conversation that we're having with users is as they expand globally, how do we reach our users with lower latency in these new regions that we're pushing on our app? And with, for that, that is something that, you know, NuVidDB is really good at, where we allow you to scale and be able to add nodes to new regions as needed. And you can accomplish this in two different ways. One of them is using synchronous replication, an idea of geo-level partitioning, where you pin specific rows to specific regions. We partition off a particular column that allows you to select different variables that allow you to kind of choose, hey, if we have a row come in and the geo-column is, you know, US, it'll automatically pin that data to the United States. If it's EU, it'll go to EU. If it's Asia, it'll go to Asia, right? So that's something that's, when it comes up a lot when we talk about data compliance, especially with the EU and GDPR compliance, we almost always need to make sure that, hey, EU data stays in the EU, right? But at the same time, if you wanted to, you could query across the different regions, but the data is stored in that particular region. YugoByDB also has the concept of asynchronous replication. So if you want to have two separate clusters synced up with asynchronous rather than synchronous replication, that's also something that we can do. And we've seen users accomplish that for low latency in different regions as well. So using YugoByCloud and other different, let's say, for example, Hasura cloud, we've really seen developers start to embrace this type of architecture where they don't have to deal with the infrastructure piece. They don't have to go to the command line and run different scripts and different types of commands in order to stand up these different technologies. These technologies are fully managed for them by the people that built them in the first place. And by using them together, you really get a fully managed graphql stack. And that's what we're going to show you today on the Hasura piece. It removes the need to have to install and manage things yourself. You click a couple of buttons through a UI, and it does that work for you. And then with the YugoBy piece added in, you have the luxury of scaling linearly in order as your traffic grows, right? You can just add nodes to your cluster, and you can add those nodes. Again, we kind of talked about it, but different cloud providers, different regions, et cetera. So that's just a quick hello from the YugoBy team, kind of giving you guys an idea of what YugoByDB is at a high level. Obviously, we'd love to continue this conversation for you guys. If you have any questions, feel free to continue asking questions in the chat. Whichever one of us is not presenting, we'll take a stab at answering them for you. And we'll go from there. So now, getting to the fun part, right? Starting off with Hasura. And what we're going to do is really quickly, we're going to show you how to use the graphql console and the backend, the graphql engine, to interact with YugoByDB and its Postgres compatibility. So the steps that we're going to run through, we're going to create a Hasura cloud instance, we're going to create a YugoByDB cloud instance, and then we're going to configure Hasura to communicate to that instance, prove it out, make necessary changes to schema and the application in order to use that Hasura cloud instance. And then we're going to run the application and kind of show you what you can do with it. So the application today that we're going to be running, it's called Real-Time Polling App. And this is built using react. It's going to be powered by Hasura cloud for the graphql engine piece. And then under the backend is going to be YugoByDB. We're going to show you kind of how you can cast votes on the poll and look at the results in real time. We'll create a database and kind of show you how the data exists in there as well. So that'll be fun. First thing that we're going to have to do, and I'll put this in the chat here. So YugoBy, Hasura cloud workshop is going to be the repo that we're using today. Calls out some prerequisites, some technical requirements. We're going to go through all this with you, reiterates the application that we're going to be working on. What we'll be using today is for the hands-on session is going to be this document right here. And we're slowly just going to go through these steps together. So the first thing, create a YugoByDB cloud instance, create a Hasura cloud instance, make sure that we have network access. So that's step one. So let's first go to Hasura. So if you go to hasura.io backslash cloud, we'll be able to go ahead and get started. So I've already went ahead and created my Hasura account. It's very quick. You can actually just sign in with your Google cloud credentials, which is what I did, and you can go ahead and create a project. Free tier. Okay. And of course, if you want to use a paid one, you can. I'm using free tier because I want to show you guys how you can use free tier on Hasura and YugoByDB to not have to pay anything and play around with some of these different applications. Change the code to maybe look a little bit more like what you guys are looking to do. I'm going to change this to YugoByDB demo. Let's go ahead and save that. Okay. And then one of the things that we're going to need here, we're going to go ahead and we're going to save a copy of this because we're going to need it later. So I'm going to save, I already have my cluster created. So I'm going to add that. And then last thing we're going to need here is this admin secret. So let's go ahead and copy that over here as well. Okay. So that's everything on the Hasura side. Really quick to create an account and create a project. Now, really quickly, let's go ahead and do it on the YugoByDB side. If you go to cloud.yugoby.com, you'll be able to log into your account if you already have one. If you do not, you can do a backslash sign up and it'll take you to this page. Marcello, can you just increase the size of your screen a bit? Yep. How does that look? Is that better? Yeah, that's good. So typical, just personal information in order to sign up. We don't require you to put in any type of credit card to go in. So you can go ahead and create accounts, start using YugoByCloud, and then you'll be introduced to our getting started section here. I've already went ahead and created a cluster just because it takes a little bit of time. So that's already ready for me. Obviously, you guys will not have any clusters, but you'll be able to go ahead and click on getting started, create a feature cluster, and you'll be able to create your free YugoByCloud cluster. We have a limit of one per account. So you'll see here that I cannot create one, but I'm going to go ahead and just walk you guys through creating a paid cluster. But obviously, you don't have to do that. It's the same process. So what you'll see here is I'm going to go ahead and this one's created in aws, US West One. So I'll just keep it in the same region. I'll do the same. So here I'll call it Asura demo. It's not letting me because I already have that name, but then I'm going to go ahead and do this one, US West One. I wanted availability zone tolerance. If I wanted node level, I can do that as well. Soon we'll have regional level as well. And then go ahead and click next. The first thing you'll have to do is download your credentials. If you want to keep the default credentials, go ahead and do that. If you want to add your own, you can do that as well. You'll see here that calls out two different APIs. YSQL is the Postgres compatible api that we spoke about a little bit during the presentation portion. YSQL is our Cassandra wire compatible api. If you have any NoSQL workloads that you want to test out, you'll see here you can use the same credentials for each one. You can do it differently. So it really just depends on the level of control you want, but you'll have to download your credentials either way before you can create your cluster. Once you do download those credentials, go ahead and get them copied over so that you can have them head-to-head available and make sure all that is secure and nobody else has access to your passwords. And then go ahead and click on create a cluster. I, like I mentioned, already have my cluster set up. So I'm going to go ahead and kind of take you through what that looks like once it is up and running. You'll see an overview section. We have an idea of what our fault tolerance is. If you're running a free node cluster, you'll see that there's free tier cluster. You'll see that there's only one node. So if you're looking to test things like high availability and horizontal scalability, you will not be able to do that with the free tier cluster. But if you have a particular use case in mind and you want to try something like a POC, you can reach out to me or Pratt or anybody on the sales engineering team or the sales team, and we can kind of get you stood up for a free trial as well in order to test out an actual full You Can Buy DB cluster. But you'll see some more information. You'll see the level of encryption when it's created, key metrics. I don't have anything running right now, so you don't really see anything there. Node tables. We have a single node, different things you can do on the performance side, looking at the activities that are happening and then other settings too. So you'll see one of the things that are called out here is in order to connect this cluster, you must have at least one IP in the allow list. Right now I don't have any. So let's go ahead and create our IPs. So I'm going to create a new one. This one's just going to be my home network, meaning that if I for my local laptop want to go ahead and use this IP address arrange, I don't know my IP, so I'm going to go ahead and just click on this button to detect it itself and add it in there. Go ahead and click save. So it's going to go ahead and apply that here. I do want to add another one to you for my Hasura cluster, so let me really quickly go to network access and let me add another IP address. So this one's going to be called Hasura in order to connect from my Hasura. So IP range. I'm going to go back to my Hasura project and I'm going to go ahead and copy this over here too and I will add that here. You can see here it can be either a specific IP address or addresses or an entire range. So I've done both. My home network was arranged for this was a particular IP address because I knew what it was. So you'll see here green is denoted that hey this is an active network allow list. So you'll see here that it's being used by this particular cluster and it'll show all the other clusters that it might be using by. You can't delete it if it's being actively used. So if I wanted to delete this particular allow list, I would have to go into the Hasura cluster and first take it off of that and then be able to terminate. Hasura one, I'm not using it yet so I'm allowed to delete it here right if I need to. So let me go back here. Let me go to settings. I can also go to quick links, edit IP allow list and just add this one. So you can see here the different allow lists that you create. It's as simple as clicking on and off here to get them added. So it's applying that one as well. Soon we'll have both available to us. All right awesome. So that takes care of the first step. Let me take a quick sip of water and we'll continue. All right so next step. What we want to do is we want to go ahead and configure the Hasura cloud Instance to use the Yugabyte DB cloud Instance. So go back to our Hasura project and we're going to launch the console. Right now we're on the api tab. We want to go ahead and maneuver over to the data tab and then we're going to use the connect existing database tab in order to get our connection working. So our database name, we'll see here that we already have one that you need to use called yugabyte-cloud-instance and I'll show you why. We're going to use that in a little bit but let's just go ahead and copy and paste that over. We're going to continue to use the Postgres SQL driver. Yugabyte DB is Postgres compatible so we'll be able to use the exact same driver in order to connect. database URL is the way to go. You'll see here they have a little connection string for us. If you go back to Yugabyte cloud Instance you'll actually have a connection string readily available for your connection to Hasura. If you click on connect there's going to be three different connection methods. One is with the cloud shell and we'll use that a little bit to verify once we get the data migrated over. You can do a client shell where you download the Yugabyte DB clients and you use those from your remote server or you can connect it straight through here. You have a little tab here option to optimize for our cloud so let's go ahead and do that. I'm just going to copy this. Make sure that you're using the right api and then I'm going to go over here and I'm going to make the necessary changes I need in order to do this. You'll see that SSL is authenticated if you do not want to use SSL. Well the SSL mode defaults to require anyway so whether you use this or not it's going to be using the SSL mode of require. We're going to go here. This is Yugabyte. One thing I want to make sure is I'm not actually using the Yugabyte database. Just as a best practices I want to go in and I want to create a new database specifically for my application or specifically for this demo that I'm running. I'm going to go ahead and launch the cloud shell and it'll just open up a new tab that allows you to access your database directly. Once this pops up I'll be able to put in my password and I'll be able to take a look and prove to you guys that there's no tables in there and that we're actually starting from scratch. We're at the Yugabyte DB database. Let's go ahead and check. There's nothing here. Let's take a list of all of our databases. We have a number of system databases that are created to us by default. So what I'll go ahead and I'm going to go ahead and create database Hasura. That way when we start our migration over using the graphql migration feature we will be able to go ahead and connect to the Hasura one and that's where it's going to create all of our tables. So when we come back in here we'll be able to do that. As you could see, all the Postgres related shorthands and all the Postgres SQL compliant queries you can run on Yugabyte. So you don't have to learn a new SQL language. All the existing Postgres knowledge you can use with Yugabyte as well. I'd like to also point out that the default database that is created is the Yugabyte but that's owned by Postgres. So when you for security reasons we give you an admin user and that admin user has limited functionality for the default Yugabyte database. So you'll want to create your own database for your applications. So that way you can create schemas and other things like that. Yeah, or specifically it creates its own schema for certain things. So you want to make sure, and my demo will do the same thing, but I just wanted to point that out while we're looking at this here. Yep, thanks Brad. And that is specific to the cloud as well. If you're running open source Yugabyte DB you won't have any issues logging in as Yugabyte or as Postgres if you wanted to. Again, I think from best practices it's typically better to just create your own database anyway. But just kind of give you guys a heads up that if you're using the cloud and you log in as a Yugabyte user you're not going to be able to do certain actions like create a schema. For example, on the Yugabyte database you're going to have to create a new database and then create a new schema. Okay, so I think for that right now we have good. So now we're going to go back to here. We're going to put a sir right here because that's the database we want to connect to. 5433, this is the port that you want to go to for Yugabyte DB. Here we have the hostname. You can also find the hostname by going to settings and then hostname right here. And Eric, I saw somebody raise their hand. I don't know if they've posted in the chat or in Discord, but if you guys want to take a look. It was Mary. Any questions? Go ahead and ask. Oh, okay. Never mind, it was a mistake. False alarm. Alrighty, so now we're back here. Let's go ahead and we have saved our database user and our database password. So let's go ahead and update that. I just used the, I didn't create my own password. As you can tell, I used the Yugabyte provided one. So this is really what it looks like. It's your typical Postgres to SQL connection string. The difference here is that we're going to have our Yugabyte cloud hostname as well as the Yugabyte specific port of 5433. We're calling out our database and the rest. So let's go ahead and copy all of that over. Let's go to the connection pool. Not the connection pool, but the connect database in Hasura. And then let's go ahead and connect our database. And then shortly we should have this. There we go. In the blink of an eye. So now we have this here. Let's go ahead and view our database and you'll see we don't have any tables. We don't have any types of other relationships. So that takes us to the next step where we're going to want to clone the workshop repo. So it's already available for you right here, the entire thing, right? If you want it to actually go through the repo as well, you can do that. You can just click code, copy it right here. So I will go ahead and clone that. You'll see here that I don't have anything really going on. Let me go ahead and clone this. Now it is here. Let me go into the one. And then you'll see all of our different, this is actually what we're reading from on the browser. And then all the different pieces of that repo that we were looking at earlier. Okay. So we're going to go ahead and do that. If you're not already, please install the Hasura CLI. If you're using something like Homebrew to typically do that, you can just do brew install Hasura dash CLI and it'll download it for you. So for now, what we're going to do is we're going to go to Hasura directory. We're going to have to edit our config file. So when we are running the application, we want to make sure that the migration can actually connect to our database. So let's go ahead and take a look at that. We're going to go under Hasura and config that YAML. And there's two main areas that we'll have to edit. We'll have to edit the endpoint for our application. And then we're going to have to edit the admin secret to make sure that we have the right authenticity, right? So we already saved that here under Hasura. I'll increase the font size of the ID a little bit. Yeah. Yeah, that's good. Okay. Is this good? Yeah. Okay. So let me go ahead and copy this. I'll move this over to here. We probably don't need this much space. Okay. So I will go ahead and change this. You'll see instead of Hasura YB demo, it's not going to be you like DB dash demo or whatever your naming convention was. We'll go ahead and we will change this up as well to my secret. And then we're going to go ahead and save this. We're going to go ahead and exit out of that. Okay. So the next part here is going to be actually applying the migration itself. So we went ahead and we updated the endpoint and the admin secret. Now we're going to go and run the migration. So let's go ahead and do that here. So let's go ahead and run this. Awesome. So migration applied. Now we'll be able to go here and see that there are some untracked tables. Awesome. So here we go. Here are the tables for that particular application that we're going to need. So what does it mean for us to have an untracked table? It just means that the tables reviews are not exposed over the graphql api. So let's go ahead and expose them. We'll go ahead and we'll track those tables first. And then once that is completed, we'll go ahead and we will track anything else that comes along with it. So if you take a look at here, you'll see that we want to track all the different tables and the relationships for it. And then we'll have to actually add another relationship ourselves to show you guys what that looks like if you need to manually do this and you don't have the ability to do it how we did today. So it looks like all of our tables and views have been tracked. Now we want to track the different foreign key relationships. Let's go ahead and do that as well. We'll give it a minute. Awesome. Relationships have been created. Let's go ahead and refresh one more time, and let's make sure that there's actually some relationships that are existing in here. So I'll go ahead. Let's take a look at the options table, relationships, and it looks like there are some relationships already there. Let's go ahead and create our own relationship now on top of some of the ones that have already been created for us. So let's go ahead and go to poll results. Following this, we want to create a relationship, array relationship for poll results, relationship name option on poll results table, referencing poll ID to the options table, dot poll ID. So I'll go to poll results. I'll go to relationships, configure manually. We'll do an array relationship. We're going to call it option, singular, not plural. Public schema, reference. We actually want to do the options table referencing that, but poll ID for each. Let's go ahead and save that. Awesome. Another step done. Step six, verify the setup. Navigate to the Sera graphql console, run the graphql mutation and queries present in the graphql.js file. So let me go ahead and open this. There's a number of different queries that we can run. We'll go ahead and just run this one to prove it out. And as you scroll through this, we're using subscriptions in order to run this application. A couple of different things depending on what somebody votes for in the polling app and then obviously making those changes for you. So let's go ahead and that's been added. Let's go ahead and go to api here. So go back to the api tab and let's go ahead and let's run that sample query just to make sure that we're getting what we need. And there we go. So we're running the query here and you see the results here, the ID of the poll itself, what the question is, what's your favorite front-end framework, and then different IDs for the options that you have, react, vue, vanilla.js, angular. So let's really quickly take a look on the Ugabyte side too of what that looks like. So let me go ahead and reopen this cloud shell. I'm doing it to the Hasura database this time. We'll go ahead and confirm that and then let me go ahead and get my password. And then really what we're going to do there is I'm just going to kind of use SQL in order to kind of show you that the different tables that are in there are going to have the same type of results. Okay. So we go in here. We're connected to Hasura. If we take backslash D, we'll see all of the list of relations that we now have. So all of these different ones here, you're able to see if you take a look at the data tab. So just confirming on the Hasura side and the UBDB side that we do see what we want to see. If I do select star from an option, for example, you'll see that our options map to the different options that you see if I run this again. All these different ones. If I take a look at what the actual poll is, you'll see that it's the same question. If we wanted to add more polls, we could. Right. If we wanted to take a look at what users, there's only been one user so far. And then we have a number of different votes already cast. When it's created, the idea of the user, we just use the admin user for all those. So you'll kind of see that there's already been some votes that have taken place. If I actually want to see the poll results and you'll be able to see is once we show the visual, right, you'll see that we have one, two, four, seven, eight votes already cast. So let's go ahead. And now that we've done that, we're going to the next step, which is the last step before actually running the application. We'll want to edit another file in order to make sure that it's able to connect to the application. This time is able to connect to our Hasura instance. So let's go ahead and let us open up that particular file. So it'll be under source, apollo.js. And then the two main areas that we're going to have to change it as the host name. So right here and then the Hasura secret again. So for the host name, please make sure you're doing it, that you're not including HTTPS. I made that mistake myself. And it's going to cause some UI issues. So let's just grab everything, but the HTTPS portion. Let's go ahead to host name. That's going to be right here. So ours is going to look like this, go back, grab the secret. There we go. Okay. Let's go ahead and save that. We're done with that one. Now we will go ahead and see what's next. Okay. We already did all these. And now we just go ahead and save that. We just go to the root of the repo and we run these commands. So let's go ahead and do that. Root of the repo. Okay. npm install. Let's run this real quick. I should be down here in a second. Okay. Then just make sure it's going to open it up on here. Cool. I'm going to go ahead and hit npm start. Okay. So let's go ahead. Actually, I'm just going to open it up. So if I go to my local host, it'll show the application. So we see here the same options that we saw when we were kind of doing a verifying our testing about the Ubyte side, as well as the server side. We'll see if there's already some votes that we've casted, right? 23111. So this adds up to eight, just like we have within our UbyteDB demo. And then you'll see the different cast load options. So if I go ahead and I click on react, let's go ahead and vote for react. We'll see that that's now up to three as well. If I didn't want to take a look at any of these graph 12 pieces look like, we could do that through the Hasura interface as well. So if I go back over here and let's say, now, let's see, I select star from poll results. It will show. So now you'll see here that for react, it is now up to three. So as we make changes here live, we'll be able to not just update the UI, but it's also going to update the database immediately all going through the syrup. Real quick. Now it's up to four. Behind the scene, it's actually using the Hasura subscriptions. So the third query in the bottom just showed that. Yeah, that was correct. Like whatever the performance you might be seeing with say Postgres, you will see the same kind of performance with Ubyte. Later on, we're going to see how we were able to benchmark graphical subscriptions to scale it like a million records. So, and like easily scale out the database, you can start out small as in when you have more users start using your api, then you can scale out the database and scale up the subscriptions as well. Ultimately, that's what we wanted to show. Yeah. And we will go a little bit. Make sure to stay on the zoom because we will be going through that in a little bit after. So we just finished up the Hasura portion. Now we're going to play around with prisma and then apollo as well. So, Eric, I'm going to turn it over to you. Nikhil, Eric, is there anything else that you guys think would be good to show while I'm already here or anything you guys want to comment on? Yeah, this was awesome. I think we have covered all the things we want to cover. Let's just ask the attendees if they have any questions. So folks, if you have any questions regarding how to get started with Hasura and Yuga Byte, please feel free to ask us any questions. If you are following along, if you have any troubles following, getting the app working, you can let us know as well. Yeah. And if you instead want to ask us over Slack, there's a lot of users, YugaByDB users, as well as admins like myself, Nikhil, Eric, if you guys have any questions, we'll be happy to have those conversations over here as well. So in this part, we're going to be working with Hasura. So I'm just going to get out of here. We just ran through the Hasura part, but actually we're going to be working with prisma, excuse me. So you can kind of see we're going to get started with prisma. We're going to create a local prisma instance, create the YugaBy cloud instance. I hope you already have it up. Maybe if you didn't quite get it all the way up during the Hasura, hopefully it's up and working now. And then this will be the stack that we're going to be using. So we'll have an apollo server, you have the graphql Nexus, our prisma Client, and prisma Migrate. So we're going to actually run through creating our prisma app, and we're going to do a live migration of the data, and we'll create a new database table in real time, get that started, and kind of work through the apollo server. You'll kind of see the UI. It leads to the it leads nicely into what Nick Hill is going to be doing for his portion. So I definitely, definitely, if you guys can get this kind of going and started, that'd be great. And so let's see, I'm going to post the repo in the chat here. This is what we'll be working on. And you can go here. I've kind of created it out, gotten it already. So here it is. Can everybody see everything okay? Yes. Is it big enough? Do I need to make anything bigger? Can we see? Good. Marco? Making a little bit bigger wouldn't hurt. Yeah, I think that's good. Okay. So we'll do that. All right. Let's get a terminal session open. This is where we're going to be working. So if you haven't already, maybe go create a particular folder. I created this Yugo Byte prisma workshop because we're going to be downloading the example and installing all the dependencies really quickly. So you can see here, I also have a Yugo Byte cloud cluster set up. This is actually a paid one. I have three nodes. You can kind of see here, I have it in the US West. I don't have any tables. I do have some tables currently. Okay. But we'll go quickly and actually we'll just go delete these real quick. But as that's kind of getting in there, we can kind of roll through. So we're going to install it all. I wanted to put this here. So we're going to kind of like we did when we did with the server, we're going to switch to the Postgres. We'll kind of work through it and I'll show you where we're going to make these changes for prisma. So you'll have the kind of the same thing. We'll create a Postgres provider. We'll have a very similar URL and we'll create and see our database, kind of run through that. So let me add over there. I can't type this one in. These are all the tables that it will create. All right. So now we have fresh, fresh, clean database. There's no tables anymore. So we'll kind of go ahead and practice. You don't hurt to just increase the font a little bit on the browser. On the browser. Okay. Yeah, that's a little better. I'll try to make sure I get going. Okay. So let's go ahead and we'll download. If you haven't, like I said, create a folder there for this if you'd like. We'll go ahead and just copy this one. And get this a little bigger too. I'm going to leave that for you guys. Okay. So we should have our graphql folder with everything for prisma already in there. So let's hop in there and you should be able to see we have everything loaded up. So now we're going to go ahead and install. So similar to our demo, we just do an npm install. And it's going to go through it and get everything created for us. So now what we're going to do is we're going to go allow it to connect to our Postgres instance or our Yugo Byte instance using the Postgres api. So if you have a particular IDE that you like or if you want BI, however you want to do it, what we'll do here is we'll open this. So I'm already, here's the graphql. In the prisma folder, we'll have our schema.prisma. So we'll go ahead and open that. And this is where we'll create everything. So you have your data source DB. And we're going to make this Postgres. And we provide our URL. So kind of similar to how we did it with the Asura, you can just go to connect, right? We can connect our application. And since the first thing that, one of the things we did early on was Asura. So that's why we have our optimized for Asura, but it's the same for prisma. We can just take this URL and insert that into our URL string. So I already have mine here. And I have a very secure password because it's fun. And so if we come over here, we can just add that in. And then we'll save that. And if we come back back to our repo and our shell, we can go ahead and create and see the database. So we can just run this command, npx prisma migrate dev. And this will go ahead and connect to our database and create some tables. So we'll run this. Now, this does take a little while. I noticed for prisma, it does connect. It definitely takes just a little bit. But as we come back, we can see those, we can already see we have that new table. One new table started. And as it kind of goes through and syncs, the rest will be created. It takes a few minutes. Well, not a few minutes, but it takes a couple of minutes to kind of sync up, get everything created. I'm not sure why prisma takes a little bit. I haven't dug into it that much yet, but it does take just a little bit longer than I think it does for Asura. But eventually, once this runs, there we go. Now it's applying the migration script. All right. It's running the SQL statements. We'll see some more tables. All right. Now we have our user profile. All right. Everything's in sync now. So as we can see, we actually have a lot of extra tables. I'm quite surprised about. Okay. Seemed I cleaned it up. That was odd. I'm not going to live run through this a few times. It seemed to want to create some extra tables that it hasn't done before. So I apologize for that. But that was interesting. Gotta love live demos. All right. So from there, we can now have another file called seed.ts. So if we go look at that real quick, we'll open it. And what we'll see is we have just some data. So you can construct the user data and we can have this file with some data. And it's going to go ahead and insert that data into the database. So we can run our DB seed and it's NPX prisma DB seed. And it's going to go ahead and load that data for us. So you can see that. All right. It's created those users. So if we come back and hop into our cloud shell, go back to the prisma table. I should really keep this open more, but just let that open up. Apologies for the delay. Sometimes it's going to take a little bit. It creates its own instance for the shell. Any questions so far? Has anybody been able to go and get this going as well? Please feel free to ask. I can answer any questions while we're waiting here. Let's give it a refresh. See for the travel. Try again and get back in there. There we go. Okay. So we look at the user table. Have it there. And we'll just select the star user. And we can see that we have three users, their IDs, and they're ready to keep moving. So now we're going to actually start the graphql server. So all we have to do is run the npm run dev. And it will start it up. That starts up nice and quick. So then we can just go to localhost port 4000. And we will have our apollo. So you just click in here. And now here we have our UI. We can interact with our database nice and easily. If you're familiar with graphql, you know, we can start building our queries here. So I have some queries already set up that we can kind of start with. So let's go ahead and copy this one. And but instead of that, Sarah, let's go ahead and create me. Create me. And we'll say vprag.com. So if we run this query, boom, goes and makes my user. We can check it out. There I am. We want to see if we click down here, there's a few more different queries we can run. So let's go ahead and create a new draft. Let's say it's a social posts, right? So go create this draft. Nice and easy. Run that. And now we've created the draft for this user Alice to join prisma Slack, right? So once that's there, we can kind of come back and we can now receive, we can go and say, okay, let's go get that draft back. Let's see, you know, if that actually was created. So nice and easy. Boom. Here's another draft. Here's the draft that was created by Alice. All right. No problem. So this all works pretty well. We can run this. All right. So here we have some. Here's all the published posts by their authors. We can see that pretty much it's only Alice. Mahmood has also created one. So as you can see, you don't have to run up through all of them, but if you want to, you're more than welcome to kind of go. There's a bunch of different examples here of different graphic queries that you can run. We have great posts. We can delete posts. We can do whatever we want. But the main part, one of the things I thought was really cool about this prisma was evolving this app. So this section here under evolving the app, we're going to migrate the database and we'll update the application code. So if we do it all live, it's quite cool. So we create a new table called profile. So it allows users for this particular application to create a profile that they can link to their user ID. And as we do this, it's all live. So we don't actually take anything down. So it's quite cool. So we can start this section here. I'll see if there's any questions so far. Anybody? Anything on Discord? If not, we'll just kind of keep moving through. So what we'll do now is I'm actually going to add another shell here. We'll go to our workshop. So we're here. And what we're going to do is reopen our schema.prisma. So we have that here. I'm actually going to get rid of that. So here's the schema. So what we're doing is we're going to add another column to our profile table. And this will be linked to our profile. The column will then be linked to our profile table. So we'll just go here and grab this. This is what we're adding. So it's nice and easy for you guys. All right. So now we have our new table, our profile with our ID into the bio that we're going to do, the user and the user ID. This will be linked back to our user table where it'll add that profile section. So what we can do is now migrate this. And it's going to go real time and create our new table. So let's run this here. Okay. So this takes a little bit. It's going to go similar to what we did before where we create our initial tables. It's going to go ahead and create that profile table. As we kind of look here. Okay. We can now see our profile kind of does this migrate part as it gets everything ready and then kind of cleans up after itself. So let's move through. Once it's done, we'll see that it'll be synced again. And you can see if it's here yet. So it's not quite there. Okay. Once this syncs up, we'll be good to go. This is the only part I found kind of interesting. It does take a little bit. Like I said, I haven't really looked into the prisma part as to why, but it does take a little bit of time. We can see we come to performance that there's a little bit of latency creating those. But we don't really, you know, we don't hit a lot of CPU while we're doing these operations. So it's not that intense. These aren't actually particularly large nodes either. Right. So there are only four CPUs. Two gigs of RAM on each. So not particularly big, but that's okay. It's going through and creating those still. It does give us time for questions if anybody has them in. The next part, we'll actually go through and update the application code. That part's a little more involved. We do have to make quite a few different changes to the code. So we'll be able to interact with our new table, be able to query and look at it. So if you'll have any questions on that part, I'll take it a little bit slower. Okay. So then it looks like we finished back up. And we should have our table. Did it not do that? All right. Well, we'll restart our, restart the, restart it. Let's see if we picked back up. Okay. Did not create our, I did not create our table. Give me one second here. There, that's there. Maybe the CLI is timed out. Yeah. Okay. Okay. That could be the problem. I was not editing the correct file. All right, well, that's on me. All right, let's do this again with the correct file. How about that? All right, let's try and run this one more time. So we'll migrate that, add the profile. That's actually, I know what I did wrong. I didn't save the file, so that's on me. Sorry, guys. So we'll run through this one again. While that's going, we'll kind of look at the next part as it runs through. So in the source file for the schema.ts, we'll kind of run through this. This is the application code. We're going to create our profile object type. This allows us to interact with our profile table. And then here, we're going to adjust the user object type, our user table, and we're going to add that profile field. We also have to include it in our make schema section. And then we will also have to create the graphical mutation. So it allows us to do mutations against those tables. And so we'll kind of walk through this one. It's not a lot of crazy code changes, but we will have to make some. And so one thing I noticed in the prisma examples, if you go to, I pulled this from prisma, their examples. In this particular part, I had to change a little bit in order to get it at least working for me. And we can talk about that later. It's going through and creating those tables. So the interesting part, I thought, was, as you saw, the seric creates it quite quick. But in prisma, it does take a little bit. So I apologize for the delay. But please ask any questions now. If anybody is trying to catch up, I probably have the time now. So we'll take a look here. Now we're applying the migration finally. It's adding that profile. And now we can see our profile table. Now we can see it's in sync. We didn't take the application down this time. It's still running. We could still interact. So that's fine. So now that that's done, now we'll move on and actually do your application code migration while everything's still running. So we can get rid of this guy. We can come back here and we'll open our schema TS. So if we come back to the You Can Buy it workshop, we go to graphql, we're gonna go to source, go find our application code. And so we want, right, our schema.ts file, typescript file. So here it is. Can everybody see that all right? I'll get rid of the chat. Okay, a little bit bigger so that we know what we're doing here. Okay, so this is all the application code. If we come down to the user type, what we'll do here is we'll just make a little room for our profile, copy this part here, and we'll add it to our code. We'll just get rid of this part. Okay. So now we've added our profile object type. This is going to allow us to interact with our profile table. And then we're going to add this particular section here to the user table so we can then interact with the new column that we created in our users table. So this one gets a little interesting. We just gotta kind of clean it up a little bit. Not quite. So I'm just kind of pulling out a little bit here. No big deal. It doesn't really matter. You don't technically have to do it, but it's all right. Not the greatest, but we won't spend too much time making it pretty. So then we have to come down to our make schema section. Right here. So we have our make schema, and let's add our profile column there. Profile, don't forget our comma. And now we're going to add our create profile to our profile. Now we're going to add our create profile graph kill notation. So let's find this one. We have to come back up, actually. This is going to be, we find our rotations section. Sorry, I'm going a little fast here, but here's our mutations. So you'll see some of the different ones in here. This was, you know, you can create your draft. Here's how you toggle a published post. So these all relate back to a lot of those mutation fields we saw earlier up where we had that long list that you could do. So let's see here. Let's just make a little space here. There we go. We'll add that. Line it up a little bit. Right there. And we will then get rid of this. Okay. So let me just check our blocks here. I just want to make sure everything's kind of okay. That looks all right. Okay, so I'm going to bring this back up. What we'll see here is our application is still running. So once we save this, it's going to go update the application so that we are able to use everything again, right? So we'll just hit Command S. So we'll save this and we'll see it over here applying everything and we're ready to go. If there was an error, you would see it over here. So I'll show you real quick kind of what that looks like. So in the prisma example that they have on their site, I'm open to GitHub issue for it, but let's say we remove this and we control save it. Now we have an error. So you can see that it'll tell you what the problem is live. So this would be an error that I hit using the sample code. And so to resolve that, what we did was we added this. So while it takes care of it, don't know how familiar everybody is with application code and stuff. So go into the details per se of it, but that allows us to get past and to keep working with our applications. So now that that's good, we can come down here and now we can test the new mutation. We can say, okay, for Mahmoud, he's going to have this bio or anybody we can, this is just the example we have here. We can add this and run it. And now this user now has a profile. So if we come back in and we used to say, select star from user, we can see, right, we have him here. He has an ID of three. Now we can look at our profile table. And there he is. You see, this is the ID of the row in our profile table, but we can see that it is linked back to our user ID of three. So those tables are linked. We did that all live without having to take down our application server. I think that's pretty cool. At least on the prisma side. And that kind of sums up this particular workshop. You can go back to the actual prisma workshop. They have, we have a lot of more mutations and things you can play around with, but you can kind of go through, check out the prisma doc. This example, like I said, that we did here, you have there in the chat, you can go mess with it. You know, by all means, please go, create your UBI cloud, kind of run through this. You know, we can see like our performance, live queries, they're not running at the moment, but we can go ahead and let's say, let's go create another one for me. And we'll have a look. This part's kind of cool. I like snowboarding, personally. I just can't wait for the season to start. I live in Denver, if you guys are interested. And I'm waiting for snow. Still don't have any snow. So let me just kind of pull this out and we'll see if we can catch that live query. As it runs, this runs pretty quick. That ran way too quick, so it didn't quite catch it in time, but it's gone. So there is a pet section for slow queries. We'll talk a little bit about this later in our query tuning, but yeah, we can go ahead and look at our profile. That's related to my user ID, right in our users table. So I think it's a pretty cool example. I will be turning it over to Nikhil here. And he's going to run through kind of a cooler example. It's like what I liked about this is that we're building upon this and you'll see he's going to work through apollo and have a really cool example. We'll give it a minute. And we'll start off with apollo graphql and Yoga by DB. This is our last of the graphql servers we wanted to showcase today with Yoga by DB. As you could see, all we were doing is existing steps that you were using with any other database, like say Postgres, mongodb, not Postgres or MySQL, that will continue to work with Yoga by DB as well. And if you are a Postgres shop and you are looking to use Yoga by DB as if you are looking to use a highly scalable system because you have hit some of the limitations of a single node Postgres, then Yoga by DB can be a good fit for such use cases where you need scalability and always on kind of setup where you cannot take outages. Some of the deployment apologies that Marco explained in the beginning, right? That will help us a lot to build like always on architecture. And this workshop we just wanted to showcase like how simple it is for anyone to get started with Yoga by, whether it is OSS or cloud, it's just few configuration changes and all the existing graphql concepts that you are aware of will continue to work with Yoga by DB as well. Okay, sounds good. Let's get started with apollo graphql. So apollo platform actually provides a few more things compared to what prisma provides. And also it's similar to how Hasura graphql engine is. But one difference is in apollo platform, you have a lot more control of how you are implementing your graphql server. These are the four different categories that apollo platform kind of touches upon. It's like it allows you to build your graph. When you, in apollo's perspective, when we say graph, it is nothing but building the graphql queries or graphql types itself. So obviously you can build your schema and everything and write all the resolvers that are required for queries and the mutations using the apollo server APIs. And obviously most of the node.js applications use apollo client as apollo client for querying the backend graphql servers. Doesn't matter if it is prisma or Hasura or apollo itself, like you will be using apollo client for querying your or fetching the information from the server. And one other thing that apollo platform provides is it provides the apollo Studio, which is nothing like a UI where you can go ahead and see, like kind of review all the types and run some graphql queries. In addition to that, apollo Studio has the capability of monitoring your apollo servers as well, like what is the throughput, what is the latency and things like that. All those things you can monitor using apollo Studio. And one important thing is even the apollo Studio is kind of, it's a free software and you just have to create an account on apollo Studio. And if you want to use the more advanced analytics features that apollo Studio provides for identifying like outage patterns and things like that, then only you'll have to go for like the more advanced like paid versions of apollo Studio. Otherwise, whatever the existing we have, it will continue to work with the free version of apollo Studio. And obviously like all the other graphql servers, you can also federate a bunch of different data sources, whether it is your different databases, your data lake or the different REST APIs, or even if you have SOAP APIs, you can all federate that using the graphql server. So similar to how we got started with the other two graphql engines, even the apollo server, you can easily connect to your web by DB using apollo server APIs. Behind the scene, what apollo server does is since it's a node.js based implementation, you can use any node.js ORMs that support. In today's session, we'll be using SQLize. SQLize is one of the popular node.js ORMs out there. So all the code that you write for instantiating like creating the credit operations will continue to remain the same across databases. Only the way you instantiate the SQLize object itself or create a new store, that kind of changes between different database implementations. So if you already have a graphql server that you have implemented with Postgres, it will like super easy to connect it to YugaByte. But if you have connected to, like if you are using any other databases like MySQL or SQL server, with just minimal code change, you can migrate your application from those databases onto YugaByteDB. We'll just show how those steps as part of this workshop, we will go over that, how to create a data store for YugaByteDB using SQLize. And obviously all the APIs that SQLize provides like create find by one, find by user ID or the column of the table itself, all those continue to work with YugaByte itself, YugaByte database. It's similar to how we were working with other demos or other workshop apps that we went to. In today's for apollo, we'll be building a space explorer app. This is like a cool app that I found for apollo. So we just have extended this application to use YugaByteDB. The workshop repo is here, you can do a bit.ly slash YugaByteDB apollo workshop and just paste that for everybody to see here. It is bit.ly, YugaByte.apollo. You can navigate here and you'll be redirected to the platform, whatever the workshop we'll be building. Obviously similar to the prerequisites, whatever we had for the previous workshops, a little bit understanding of graphql, familiarity with YugaByteDB fundamentals, like how to create tables, how we can instantiate which we have already gone through and some of the other things. This needs to change. Instead of Hasura graphql, it should be like apollo server. Okay, what we'll be building today, right? We'll be building a space explorer app. It's a futuristic app, which is able to read only APIs that SpaceX provides where it kind of has a list of all the rocket launches they are doing and you can book a seat on one of their crew dragons they have. And obviously whenever you are building a reservation, you would want to store your information in a data source, right? For that, we'll be using YugaByteDB for storing the database. It has two components to it. We will be building the graphql server part, they're using apollo, and also there is a node.js or react.js client, which actually has things like, you can see on the upcoming rocket launches and you can add a rocket launch to the cart and check out the cart for your profile. So we'll see how the schema of this looks like, what are the mutations we need and what are the queries that we are gonna be building as part of this application. If you scroll a little bit back, as I said, we'll be doing the hands-on session starting with building the schema, config the data source using SQLize for YugaByteDB, the query resolvers and the mutation resolvers. If you compare between Hasura and apollo, the one changes in Hasura, it kind of all these things are in black box. Once you track your graphql table or the database table itself, Hasura takes over building all the queries, resolvers, and the mutation resolvers behind the scene. If you want more granular control of building the queries and what should happen in your resolvers, then you can start using the apollo server APIs where you'll get more control over how to build the resolvers. And obviously we'll try to connect to apollo Studio and run some sample queries. And finally, once we have the server up and running, we'll run our client UI application which has the actual UI for this app. Okay, the hands-on session are actually linked here. If you click on this, it will navigate to the workshop.md. Obviously we have been talking about creating an instance on YugaByte cloud. Even I have pre-created a cloud instance for myself for this workshop. And if somebody joined new, if you want to start creating a new cluster, you can go here and click on Add Cluster, and you can click on the YugaByte Free Tire instance, since we have all, it's limited to one per user account. So if you have already created one, you'll not be able to create one more. And all the other options that you have for the paid version will be similar to the free tier as well. You can give your cluster name, whatever the availability zone, which cloud provider you want to run it in, all those things you can select here. For just, in order to save some time, we have already pre-created the instance. As you know, right, we will like, it's not like the cluster is pre-provisioned, we'll be creating VMs and the containers that are required for running YugaByte on the fly. That's why it takes a few minutes for us to configure the VM and the container. So we just wanted to save that time. I have created an instance already. This is the instance I'm going to be using. As we said previously, sure, there's a few things you would want to have as part of this, as part of your instance itself. Like you would want to have the credentials that are required as well as some of the host name that you have for connecting to this instance itself. Cool. Hopefully, this is the third time we are explaining that. You have now experts in how to work with YugaByte cloud Instances. Once you have created YugaByte cloud Instance, obviously, the other thing you would want to do is configure the load balancer so that you can connect to this instance. So for me, I have already set that up. Since we have already gone through this twice, I'll not go over how to add a new IP. The next thing that we would want to do is set up a account on apollo Studio. Okay, let me quickly do. Studio.apolographql.com. If you go here, you will navigate to the graphql schema and all those things. You can go ahead and preview it. We'll come back to this page once we go with, as we go along with the hands-on session. Cool. As I said, if you go to the project structure, let us go ahead and first clone the project structure itself. I'll go to GitHub clone. If you are using IDE, I'm using the Visual Studio code. We can go ahead and create a new terminal here. In this new terminal, as I can say, I'll go to the temp directory and I'll say git clone. I cloned the project. I'll navigate into the project. And if you see here, there's a few folders that we have created. There's the client folder and the server folder and the server initial folder. The server folder is actually in the completed code. If you are feeling lazy, if you don't want to go over the step-by-step of building the server itself, you can just go ahead and start the server there. But for today's walkthrough, we'll be using the server initial project. I'm going to import the server initial project as part of this repo here. Okay, let me go and say open folder. I'll go to server initial. Since I'm the author, I trust myself. So let me start the terminal again so that we can start working on this. Okay, cool. Hopefully, you have set up your IDE and you have the server initial project in your IDE. And now let's navigate back to the workshop hands-on session. Any questions so far on the file structure of the project itself? If not, I'll just continue with the hands-on session. Okay. As I explained, the repo has three folders, the server initial and the server and the client itself. So the first thing that we'll be doing as part of this rundown is building the schema for our expert app. As I explained, there's two domains that we are tackling here. Basically, those are the domains for which we want to define our schemas. So there is first domain is rocket launches itself. And the second one is the user reserving a seed in those launches. That's why in our schema, they should be able to handle these things, like fetching a list of upcoming rocket launches, fetching the specific launch by its ID, logging in a user, and for a user to book a launch. And also, you have the ability to put the launch or the reservation into a card, checking out and canceling the previous. So some of the basic thread operations that you would want to do with any domain. And the first thing, as I said, we'll be building the graphql types and queries and mutations that are required for our domain. If you navigate to source and schema.js, you'll see an empty file. And the first thing you will do when building a graphql schema is obviously, since this is a apollo server, we'll be bringing down the apollo server api so that we can build our graphql types. I'll go ahead and first copy the content for creating a graphql server. The syntax is pretty straightforward. The first thing you will be bringing on the required dependency, and we are calling it graphql, and you are going to define a bunch of type definitions and export that type definition so that it can be used later on in our index.js. And the second step is for us to build the object types. Obviously, graphql is a very rich type-based language. So we have like a bunch of type defined for launch, rocket, user, mission, and the trip data. So these are the ones that we want to create. We'll go ahead and copy the types that are required for us and place it here. And once we have the types, the next thing that we want to do is to build or define the graphql queries. For each of the graphql query that we define, we would obviously have to do the resolvers because there is no auto-generated resolvers for us in apollo. When we are building apollo server, whatever the query that we need for our specific domain or the business use case, we would want to have those resolvers as well. So for us, the first query that we would want to run is the launcher query. We will be querying the SpaceX api to fetch the bunch of launchers, and we would want to, whatever the information that we are getting from the response, we would want to transform that into a graphql way based on these queries. So each of the query, we can define what attributes I need from the rest response. That is what we are going to do with this type query here. We are going to define a query here, and then hopefully this is big enough. Let me make my editor a little bigger. Okay, cool. And after that, obviously we want to write a few mutations, right? And these are the mutations where we'll be interacting with the database to store the bunch of information. So I'm going to go and copy the mutations. It's pretty straightforward, right? If you see here, I'm saying, okay, I'm booking trips, cancel trip, and log in. And as you can see in the book trips, it is going to use the trip update response. So whenever I book this and that mutation, this is the response that's going to go back to the client. So the client will see a message saying that it was successful or not, and what are the launches that were actually booked. So this is how simple it is for you to build the graphql types itself. As you can see, there is not a lot of complicated code that you would have to write for building the types itself. It's pretty straightforward to design your own graphql types that you need as part of your application. Okay, I'm going to quickly go and save this. And we're not going to launch the server now because we have not still connected the data source. Let me go ahead and connect the data source first or talk about the data source itself. As you can see, we have a folder here called data sources. And in our data source, we have two things happening. We have a SpaceX REST api data source. As we were saying, graphql apollo server is capable of doing federation across multiple data sources, right? Whether it is different databases or REST api. So in this particular example, we are going to federate the data sources between REST api response and also like a UGA by DB database to store the information. So in our first data source is the SpaceX REST api data source. This is published by SpaceX. You can, if you go to launch.js here, we can see how the reducers are written. The reducers are nothing but kind of formatting whatever the REST response you get from the REST api and then putting it in the graphql format. So since it's a read-only, this is not very interesting for our specific database use case. You can go ahead and just review how the reducers are written for the REST response. What we are interested in is on writing reducers for UGA by DB data source itself. So for the UGA by DB data source, as you could think about, there's like few things that we want to do at the database. The first thing is to find or create a user. If the user is not there, you want to insert that user into the database. And if he is already there, we just want to fetch the information. That's it, right? And the second one is like booking the trips. This is like, again, an insert into UGA by table in UGA by database. The cancel, obviously removing or deleting the entry from the database and getting launches by user ID. This is like a query select by user ID that you can do or select by launches on the user table you can do. And another one is whether to determine whether a user is booked on a launch or not. So these are the queries that we'll be building. So before building the actual reducers for these queries, the first thing we want to do is we want to set up the connectivity between our apollo graphql server and UGA by DB database. Let's go back here. Let's go to sting.utils and copy this code. I'll explain what is happening in this pair of code one by one. Let me first copy this content here and I'm going to place it in like this. So this util is just like a cleaner way of coding so that you could have had this create store in the index itself. But for the code readability perspective, we are just creating in util.js. So what we are actually doing is the first thing, we are creating a new SQLize instance. So SQLize, as I said, it's an ORM, it supports connecting to multiple databases. So for us, we'll be using Postgres Dialect. As we were talking since the beginning, it's a UGA by DB is a Postgres compliant database. And since we are using UGA by cloud, there are like a few things we want. We want the host name and the username and password that we were using. Okay, let me now navigate back to my instance. I'll copy the host name here. I'll copy the host name. And another thing is password. I have a password somewhere. Let me get my password. And one other thing that you can see here is we are gonna connect over SSL because you could buy cloud instance only communicates over TLS or SSL. And obviously, if you want authentication, if you want your client to authenticate the server, you should be able to download the server. So that's the first thing that we are actually doing. So let me go back to my instance. Let me go back and download the root certificate and configure the root certificate in the SSL block here. That is actually implemented in the, if you go back to the actual, the completed code, I'll show you how to configure the authentication as well. If you see here, all you would need is to download the root certificate from megabyte cloud instance and specify the CA file. Once the CA file is done, so what will happen is, so the node.js client that we are building or the graphql server we are building, it will authenticate the server as well, which means that you will know you are connecting to the right server. You are kind of avoiding all the man in the middle kind of attack and all those kinds of things. So for now, since it's just a demo purpose, it's not a production-ready app. If you are building a production-ready app, you have to configure all these things. So that's how simple it is for us to configure our graphql server to use Eka by DB. And after that, what we are doing is we want to define our tables. So SQLize provides an api called db.define, where you can define all your tables and the columns and the specify and the corresponding data types for that. So we are creating two tables. We are gonna create user and trips table, and that which is going to be used in our index.js for starting this graphql server itself. So once we have created our store, the next step for us is to go and create our mutation resolvers. So if I go to user.js, so if you see in our user.js, what I'm doing first is I'm gonna first get the store. I mean, first, this is just like a data source that we are building. In our data source, the first thing this user data source will use, it takes a store, you can pass in any store. In our case, we'll be passing in an Eka by DB store. You could very well pass a SQLite or a MySQL or any of those things. And now let's copy this code, which is going to be writing our reducers. Okay, let me copy the reducers. As you can see, reducers is pretty straightforward. What I'm doing is we kind of defined all our methods that we wanted to create here. If you go into the implementation of this, we are first kind of doing a validation check, whether there is an email ID or not, whether it's a valid email. And then we are gonna use the SQLize APIs, right? So what all we are doing is we are using the SQLize like the reference and querying the user stable. And we are checking whether to find out here. These are like a common set of APIs that SQLize kind of provides. If you're already familiar with SQLize, you will know it's how simple it is to get started with that. So this find or create, it will just run, select star query on the database. If it finds some data, it will just return. If it doesn't find the data, it then goes ahead and does the insert. So all these things are kind of black box to the user. He just needs to be familiar with the APIs that SQLize provides. Okay, let's go ahead and save this for now. Once this is saved, and the rest of the things are pretty straightforward. Like if you see in the destroy, if you want to delete an entry and just use a destroy api, you can take a look on how easy it is to write reducers. Once we have written the reducer, I think it's time for us to go ahead and start the application itself. Before we go and start our server, let's see how we are using the stores and whatever the reducers we have written, right? If you see here, the first thing we are saying is, we are creating a store. Once we do this, this is going to tell this node.js application to make a connection to user by DB. And then we are creating launch api and the user api reducers, which is going to be passed into the apollo server. If you see here, we are going to pass the data sources and the resolvers that are going to be used by the apollo server for generating a graphql api or serving the graphql queries itself. That's how simple it is. It's like within three or four steps, you can design your graphql type, write the required data sources and the reducers that are required for your schema and start the graphql server. As you could see, right? Even though there's like few steps that you would have to do compared to Hasura, but there's a lot more control you get here. It depends, like convenience versus control. If you are like quickly trying to do something, a prototype or if you do not want to mess with the backend code, you can use Hasura. But if you are in like a lot more control and you are writing complex queries and you want to know how the queries get executed on your database, if you want that kind of control, you can use apollo servers. Okay, let me save this. I'm gonna do npm install. Hopefully that's about it. Okay. npm install. There was a typo. Okay, let's take a minute or two. Any questions so far, folks, on the graphql apollo server on the concept we just talked about? Okay. Okay, we have built our app or the node.js application. Let me go ahead and say npm start. This should start my server. Okay, awesome. As you could see, there's like one thing that I'm doing whenever when I'm doing my, when I configured my SQLize, since it's a ORM, right? In my SQLize, I'm seeing db sync force true, which means if there were tables already that were created, I wanted the tables to be dropped and create new tables. So automatically SQLize ORM is able to drop the tables and create the new tables for us. If I go back to here, okay, connection is closed. Go back and launch the cloud shell. We can see that there is no data. And also we can now go here and explore our apollo Studio. We can go here and see all our objects and whatever the different types we created. As you could see, we created three different mutations. We can book the trips, cancel the trip, and we can log into the user. And obviously we can query few things, launch, launch connections. So all the databases on the data sources you create in your code, the apollo server is able to send those information into the graphql Studio where you can go ahead and review everything. It is very straightforward for you to understand, like figure out what all queries and mutations you are supporting in your graphql server. And obviously there's a few objects, types we are supporting. These are the types which we created. We can easily see what each of them have, drill down into each of the things and what you would want to create in your graphql query. So once this is done, oh, it looks like my connection is also here. Go and get my password. Let me copy this. If you see here, I'll do select start from users. There shouldn't be any data since we created a new set of tables. And also I'll say trips. I also have some residual tables from another application that's a Marcos app, but that's okay. As you can see, the new tables that we created, there are, since we dropped, there are no data. Now, what I'm going to do is I'm going to go to the apollo Studio. I'm going to run a few mutations out of that. So the first thing I want to create a user and say, I'm going to do an electrical ITP. I'm going to say login user. And once we log in the user, we will get authentication token. Let's make a note of this authentication token. We'll be using that in the next mutation we'll be doing. If we go back to our code here, there are like two mutations that we are running. One is the actual logging into the user itself and booking trips for that user using graphql api, right? So I have already, so since we have created a new user, if I go into the database itself, I can say, like start from users. I should be able to see an entry there. As you can see, we created a new entry now called nickel that gigabyte TB. Let's use my login token ID for actually creating a new booking entry. Let's go back to our code, read me documentation. Let's copy that mutation here. Let's paste this. And if you go to the headers here, there is an authorization tag that we need in this authorization. Okay, perfect, it's already there. I'm going to say book trips. This should be able to book us a trip, trips are successful. And these are the three launches where I have a seat. Hopefully this thing comes through in our lifetime where we are able to like do space travel, like how we fly on flights. And maybe whatever the things that you are seeing in future, you will be the one who will be implementing like a website or a portal for booking flights on space launches. Cool, let me go ahead and see if the data is reflected here. I'm going to say from trips. Obviously, like what are the three things that we have? It's showing here, awesome. If we can do the similar thing on using a client that we are going to have, and let me create a new terminal here so that we can start a new one. I'm going to say, okay, sorry, this is a new terminal. Create a new terminal, okay, navigate into client. Let me restart the app so that data gets refreshed. Okay, I think there are no more data, okay, perfect. Now let me go to the client. So the client is actually built using the react.js. Since this is a backend-based workshop, we are not going to go as much into how the client has been implemented. It's pretty straightforward. If you see, there will be like a graphql client. Each of those graphql client that will be used for waiting against the server that we have created. If you go into the server, and if you go into the index itself, you'll see some of the pages. As you can see, we are using apollo Client here for creating the database, and apollo Client is configured to go into the default location, which is where we'll be running our server. And obviously, all the APIs that are required, all the queries that we have is already documented here. If you are interested in that client-driven implementation, please feel free to go ahead and look into this code. For now, I'm going to just do npm install and start the app. Okay, let's give it a minute for the install to complete. And in the meantime, I'll go back to the tutorial. Okay, awesome. Now I'll say npm start. This should start the client. Oh, sorry. Cool, stop. Oh, wow, I've already logged in. Cool, as you can see, I'll just log out and do the, this is the app, the UI part of the client. We can go ahead and see, yugabyte.com, say log in. This should have an entry for me in the users now. Okay, there is an entry for me. And if I go to the launches in my profile, if I don't have any books, any trips booked, I'll go to home. This is the first, Crew-1 Falcon is the first Falcon rocket that SpaceX used for launching astronauts into space. We all would love to be on that space launch, right? Or the rocket launch, I want to also be on that. And say, there is an entry, the other interesting ones. Okay, let's click on another. And say, add to cart. I go to my cart, I have two rocket launches where I want to be on. I'll say book all. Now, if I go back to my profile, I have two launches that I'm booked on. This is, you can, we can see that in the trips as well. So this is how simple it is, folks, for us to get started with CapsQL and book it with, or configure it with a distributed SQL like YugaByDB. Obviously here, we just wanted to show how easy is it for you to get started. But if your app that you have built with YugaByDB or like graphql itself, right, goes, let's just say, viral, right? How do you handle such situations? That is the next part we wanted to talk about. How do you actually scale your graphql workloads? I'm gonna take quick 10 minutes to show how you can scale out your graphql workloads using YugaByDB. Obviously, we kind of found out how easy is it for you to like get started with all the popular graphql engines. If there is any other engine that you want, it is as simple as configuring, getting the right credentials and configuring to work with YugaBy. And obviously, you built an app using graphql APIs, right? And what would happen and what is the current bottleneck we are seeing with our customers, right? So pretty much many of the folks get started with, let's say, Postgres database. And what we are seeing is, as and when the number of queries are going to the graphql, being served by the graphql APIs, the kind of the resource utilization on the database also increases, right? So some of the customer use cases, they have hit the higher ceiling of how much you can scale vertically. So there is so much that you can scale vertically, right? Like that, obviously, there will be a C2 threshold that you will hit one point at a time. And also, there is no, if this single instance of your database goes down, your entire app goes down. You shouldn't be in that situation in this cloud space, right? Or in the cloud environment currently. There can be cases where your entire cloud can go down. As recent as like two months ago, the entire US East and West of aws was down. And there's like a lot of internet scale applications. I cannot name the people, they are all down, right? So it shouldn't be in such situations. That's why you need to think about scaling as well as resiliency of your architecture. When moving to a cloud-native database like Yugo Byte, you kind of get all these things out of the box. In Yugo ByteDB, you can start off small. You can start off with like a three-node cluster. We are handling, let's say, 500 requests per minute or a second. And if you want to scale out as, like if you are getting viral and if you want to handle, like say, 5,000 requests per second in a span of a few hours, you can do that by adding new nodes to Yugo ByteDB instance. So Yugo ByteDB is able to easily kind of spread or kind of load balance the query requests as well as handle more traffic. As in when you are getting more data, it's not only handling more amount of data but also the compute. You can scale out the compute as well with Yugo ByteDB. And obviously the other thing that it can happen is there can be outages, right? Your database can go down or your graphql server itself can go down. So most of the times the graphql server, it kind of is stateless. So all the state is actually being maintained in the database. So if you lose a graphql instance, obviously you can keep spawning new graphql instances but it becomes essential for the data to be available. If you have a single node instance like Postgres which doesn't have any read replicas or it's hard to configure read replicas, you will have outages in your architecture. That's where a distributed SQL comes in. That's where Yugo ByteDB kind of provides all these things out of the box which Marco kind of explained the different policies you can have Yugo ByteDB in multi-region, multi-cloud where you can have your apps to be always on. So in order to, we wanted to like kind of figure out how much scale we can do linearly, right? So for that, we did an exercise where we wanted to scale out graphql subscriptions linearly. We started off small. It's a pretty straightforward use case that we wanted to do. Obviously like we want to like kind of simulate an e-commerce application where we'll be users and users will be placing orders. Obviously, you know situations where like Black Friday or Cyber Monday, the amount of traffic will considerably increase. So you can scale out your database for that kind of events and as and when the traffic dies down, you can scale down as well. So in this exercise, we're just gonna show the scale out part what we did. We started with a simple 25,000 subscriptions using a three node cluster. So we are able to scale that same cluster from 25,000 subscribers to 1 million subscribers without any downtime or without any latency, like without stopping the graphql servers or the graphql servers taking a user traffic. So we wanted to simulate that. We went from 25,000 subscribers to a million subscribers within the same cluster. That's how linearly scalable you are. You can start off small and as and when your application gains traction, you can scale out your packet. And this is the benchmark setup. If you are interested, please feel free to ask me. Like I'm gonna show you where the code and all are there. Everything was run on kubernetes so that it's easier for us to scale out each tier, whatever we wanted to benchmark. Please feel free to ask us in the chat if you need any details on the benchmark setup itself. Cool, that's all I had as part of the scaling. Now is the most interesting part that Prat is gonna use. Obviously, distributed SQL is like few gotchas you need to know how do you query tune, what are the do's and don'ts of working with distributed SQL, right? That part is going to be covered by Prat. Let me stop sharing my screen and give the control back to Prat. All right, so we'll go ahead and share my screen here again. This one is easier. All right, so Prat is going to show you how to scale out each tier. All right, so query tuning with Yugo Byte. As Nico mentioned, in most of the things that we're working on are RNs. And they have particular ways that they like to query your database. So even if you write your own SQL, for instance, like in Asura, they have some interesting pieces where you can write your own SQL. But when you track that, if you remember back to when we were looking at Asura, it changes how it interprets your regular SQL code to work with Asura's ORPRIME and how it decides to create that query for itself. So usually I like to start with where do we start, right? You have performance tuning versus query debugging. performance tuning, right, usually more often involves OS level metrics. So you're looking at memory, CPU, IO. How do the queries affect these statistics above? Do we have enough horsepower, right? As Nico was mentioning, at what point do we scale out versus try to tune queries? And so often, we're looking at these metrics to decide, okay, do we have some poorly performing queries or just do we not have enough nodes to satisfy our workload? And that is the great part about Yougabyte is you can just add nodes. As we've come to the realization that, you know what, we just need more CPU or more IO or we need to service more queries, we can just add nodes. It's really that simple for us. And then query debugging, right? What we'll look at is how do we identify slow queries? How do we analyze them to decide, where are the problems within said queries? We can do that in Asura, prisma is a little different and then we have like explain versus explain analyze and what they do and what they are. So Asura has a particular really nice built-in function. It allows you to run the equivalent of an explain, analyze within Postgres. So in the Asura console, we have this button called analyze, right? And it's there in the api section. What it does, it'll give you the generated SQL and the execution plan. So what we see here is one of the queries that we ran against for the polling application. And it gives you the generated SQL. This is really nice. Is this big enough for everybody? What we can see here is the owner I'm creating in this generated SQL that it runs. So you'll see it has the select from what we're looking for and then it has these left outer join laterals. That one always gets me from the different tables and stuff that we have to get all the information from. And these are not easy to look at. They're not easy to kind of decipher what they're actually doing. And then you can see kind of below, we have the execution plan for these particular queries. And again, they're usually kind of tough to look at. We can see here that we do this nested left loop join, then we run down on a function scan, you materialize that, we kind of move down these. And so for any of these, each of these have to be, each of these like function scan, oops, they're not going to do that. Each of these parts has to get the piece below it satisfied to work all the way back up. And in this one in particular, a lot of the times what we're looking for is sequential scans and maybe some of these aggregates. And I'm gonna hear in a few kind of talk about what all of this means. But the first part is how do I identify a slow query? Maybe you can bite. So for us, we have since we're Postgres, we have PG stat statements and it's built into Postgres. And so it helps us dig into problem queries. It shows the query ID, the query, number of calls, right? Total time it took, gives us the min, max and mean times, number of rows that it pulls back. This is a great, great table to use as you're trying to debug some of the problem queries that you may end up running into. So what you wanna do generally is what you start with is that the PG stat statement aggregates all the queries. So the first thing you wanna do is to reset the table. So that way, if you know this particular query is kind of, or if you don't know which query is running poorly or you're trying to figure it out, because maybe you have a bunch of different queries going on at the same time, if you run this select PG stat statement reset, it will then completely wipe out that particular table. It doesn't affect you, right? This is just a statistics table in Postgres for us to use. So it doesn't matter, it's not gonna impact your application. But then the query I like to use mostly is this one below where we select the database ID, the query, yada, yada, yada, all the different things. And then we order by total time descending. So give us the worst queries, the top 10 worst queries. And that'll allow us to see, okay, what are the top 10 ones? Let's pick them and let's start working on them. So then we also have, I'm gonna give a plug to our EagleBite platform. It's an enterprise kind of version. We have a slow queries tab in there. And then as you saw in my other example, you also have that slow queries in our cloud. So both of these, you can also look at, they are the same thing. It's just a view of the PG stat statements. So we just kind of give that to you in a nice little UI space here and we can look at how long some of these took. But this is just our shameless plug for our data by platform for our enterprise users, if you like. So, okay, we figured out which slow query it is. Now what do we do? So the first thing I like to do is run an explain plan. So this is just a really basic explain, select star from this table foo. And what we see is that we have a sequential scan on that table. Now, what that means is that, I mean, if you're familiar with it, we have to scan the entire table to find all of that data. Now, as Marco pointed out earlier in this, we shard it across multiple nodes, right? So sequential scanning tables in YugoByte is kind of rough because if you think about it, we have to then scan the entire table and that data may be spread across multiple nodes and then we have to go do that for all of them. So you can see how this can kind of be a little rough, especially in a distributed system like YugoByte. As you add more nodes and more nodes, these can take longer and longer and longer. So what we're looking at here is we see we have a sequential scan on foo, we have a cost, we have the number of rows and we have the width. So for cost, the range number dot dot number is because it shows the cost for starting the operation row and cost for getting all rows. And by all, I mean, all returned by this operation, not all on the table. So what we see here is we have a cost of zero and then 155. So the starting cost, right, is that zero. And for a sequential scan, it's going to be zero because, well, we just read the page and return the rows. We just start at the top and then go all the way down. That's pretty much it. Rows obviously is self-explanatory. It's the number of rows for the operation that it pulls back. And then you have width. So width is the PostgreSQL idea or the Postgres idea on how many bytes, right, on average, there aren't a single row returned by this operation. So what we see here is since our fake table just has a single column, it's stored as four bytes. So there you go. One important thing to note here is what's also great is this query is not actually executed. So we're not actually running it against the database. It's just kind of figuring, the operator's looking at it, figuring out what it's going to do, but we don't actually run the query. So we're okay here. So we'll go to the next slide. So let's look at, let's look at explain analyze. Right, so this is very similar to explain, but it actually shows you where our time is spent. And this is very important, right, when you're trying to understand slow queries and problem queries within databases, it's all about where you're spending your time. So we can see we have the explain analyze, select star from our table T and we limit 100. So again, you can see the limit, our cost is zero to 9.383, number of rows is 100, right? Because we're limiting that to 100 rows and our width looks to be about 608 bytes. But then you can see we have our actual time where you can see the number of rows, the number of loops and that we're doing that sequential scan on table T. So again, then if we actually look down, it runs the query, it shows this actual time and where we spent. So we looking at 100 rows and loop ones. This is estimated number of rows actually in the table, but we're limiting on 100. So we only really grab that top. But since we do a sequential scan, we have to look at the whole table. This is why you can see sequential scans are pretty rough within your library. So let's look at something a little more complex, right? So once we have a table breweries, we have the brew ID, the name, city, state, primary key, which is going to be our brew ID. And then we have beers, right? So name, beer ID, the ABV. If you're familiar with beers, you'll understand what some of these are. I like beer, some of them do. And then we have our constraint. We have a foreign key relation. Brewery ID. And then it references the breweries table brew ID. So we're going to go ahead and run some queries against this to kind of take a look at it. So here we have our explain and we select some columns from beers and breweries. And we have a little join there. And so now we can look at the query plan. Now we can look at the query plan. So we do a nested loop and we have a sequential scan on beers, get our cost to zero, our rows just have a thousand there and our width. And then we use an index scan using the breweries PK on VR. So this query plan shows the execution plan the optimizer came up with based on its knowledge of the data. The query is not executed again, right? This is how it will be executed. It does not show timing or statistics or anything, but it has some important statistics, right? We have in line one, there's no startup costs, right? We know that. The nested loop gets a thousand rows. A nested loop takes a single row from the first row source, okay? The driving row source and matches that with the second row source, the probing row source until the driving row source is exhausted. So it works this way down this query plan. So we move down in line two, we can see that we do that sequential scan on the beers. There's no startup costs and a total cost of a hundred. So this row source must produce 1000 rows to have the row source get a thousand rows, the next line's row source. So then we do an index scan on the index breweries PK and each probe of this row source costs 0.11 and returns one row, right? You can see that here at the bottom. So those are some pretty important statistics about how we're running it. We'll move on to the next one here. Oh, that was it, sorry. That's kind of explain analyze. For the most part, those are what I like to run through when I'm looking at queries, especially like slow performing ones. In a lot of graphql with the ORM kind of making its own queries because that's kind of how it runs. It can be tough to read these really long queries that they create. If we come back here to this, this generated SQL is cut off. These are really, really long queries that it creates. So you kind of have to dig through and work down through each portion of this query to figure out what it's doing. And as you can see, these are much more involved than our kind of smaller queries. And so you have to kind of work down these execution plans to figure out where you're spending your time, where you're actually spending the time. So one of the things if we look at, let's go this way a little bit, what we'll see here is loops. You can kind of see this. This is how many times it has to loop through to get all of the data of this particular portion or part of the data, right? So if there's a piece where you can say, okay, we're gonna get 110 rows, but we have to loop through 100,000 times, that's gonna be a problem. It's gonna be very slow. And so if you see something like that, we'll wanna look at why we're having to loop through that many. Maybe we need to get an index in there. Indexes in Yugo Byte are very, very advantageous. We use them all the time. If you are familiar with like Cassandra, we have our Cassandra api. If you're a Cassandra user maybe previously, index can be a little tough. Our indexes are strongly consistent. So we encourage people, if you see something like a sequential scan, we need to figure that out and probably add an index to that because we don't wanna scan the entire table. If we add an index, we can greatly, greatly increase the query speed times. I had a customer recently and he had a table and he did a join on a particular column that was present in two different tables. Now in a singular monolithic like Postgres database, that's not gonna be a bad, right? Cause it's just kind of gonna go hit that one database, run through it, find data and come back. And Yugo Byte, it wasn't that poorly performing. I think it was coming back in four or 500 milliseconds. But once we added that index, it started coming back in two milliseconds, right? So you could see that it was an immediate impact just by adding that index in. In some other cases, in particularly a graphql case, I was working with a customer and we had queries that were taking minutes because it was joining a couple of different tables. We kind of had to work through, it can get pretty intense as you're trying to work through how we're getting the data and joining these three different tables and working through some of these explain, analyze plans. And again, as we work through and figured out where we're spending the time, especially, where we were trying to kind of aggregate these different columns, we realized that, oh, we're spending all this time trying to gather data for this particular column. We're doing, we're looping over it a ton of times, right? So to get, we're looping it over this particular part a bunch just to get a small amount of data. So we add an index there and it was great. I mean, that great, we've got the query to come down to, only running in a couple of seconds down from 1.5 minutes. And that was just by adding an index. Now it took a bit more time. We had to work through it a little bit more to get it to something you'd actually want to work well. But that was another example. And my last one, I can't really share these queries because they are customer data, but I like to talk about them. Is that even just adjusting your join order, right? If you're looking through and trying to figure it out. I had a customer that was joining a couple of tables, the join order, it was a rough query. It was taken, I think, like one and a half seconds to come back and they needed it to be in the milliseconds, right? Couple hundred milliseconds needed to come back. So we just adjusted the join order of the query itself for Yougabyte. And that allowed it to actually come down quite a bit. I think we got it from like 1.5 seconds to like three or 400 milliseconds. And that was acceptable for that particular customer. So that's the other last piece I say involved is, what are your SLAs, what times do you need to meet sometimes the ORM makes it creates the query in the best way it thinks it should. And oftentimes that can be, these are built mainly for your Postgres, your single instance Postgres databases. And so with Yougabyte, you'll wanna kind of take a look at where you're spending your time. Reach out to us, ping us, we'll help work with you. Take a look at some of these and we can try to guide you on how the best to use these in a distributed system like Yougabyte. So that's pretty much, I think all I have.