How to connect AWS Lambda to a MySQL database to update it? (using RDS proxy)

today's video is about using rds mysql or mariadb server from a lambda function a database such as mysql is probably the most important part of creating an app it's definitely something i use in every project and it's a common question whether mysql is a good match for serverless or is it better to use a nosql server such as mongodb or something similar and in my opinion the answer is definitely yes it's a very good match and mysql and serverless work great together with the help of aws rds it's very easy to set up a mysql server to use in our lambda functions in today's video we'll take a look at how to connect from a lambda function to an rds server in the most efficient way we'll also take a look at rds proxy and what is it used for ideas proxy is another service from aws and we'll take a look what is the purpose of it why is it useful and how to use it i've created this lambda function called rds test it uses pretty much the default settings i've also created an rds database called database one and in this case as well it's pretty much all the defaults i use the mysql community engine it's a small instance and what we need to take from here is the endpoint this is the address of our rds server i also have this simple typescript file with the handler i'll link to my previous videos in which i showed how to create a handler like this and how to deploy it to lambda automatically so here is pretty much empty we have these four variables which we use to create connection objects which we'll use to connect to our mysql database the host is the address of our database that we created and when i created the database i also chose a username and a password so our username is admin and the password is demopass we didn't create a database on our server yet so i'll keep it empty and now let's try to connect to this mysql server from the cli to see that it works so we'll use the mysql client we'll set the host and the username and the connection works we don't have any databases yet we didn't create any so let's create the first database we named our database testdb so let's copy it into our configuration file now let's create our first table we'll call the table vals and it will have three columns the id which will be an integer auto-incremented and this will be our primary key a column name key which will be a string and a column name val which will also be a string now let's insert a row into our new table so our new row the key and the valve will be the values hello world so currently in our table we only have a single row so now that we know that our rds database works let's try to connect from our lambda function to this rds instance so we have the connection object and in this project i'm going to use a library called next js this is a node query builder and i really recommend this library and we'll use mysql node library so we need to install both and then in the handler we'll actually try to do two things first we'll need to create the connection to the mysql database and second we will use this connection to execute queries so the way lamda function works every time a new request is received the handler will be executed which means that if we'll put the connection to our mysql database within the handler a new connection will be created for every request that's why we want to move out this connection to outside of the handler the way it will work is when aws will create an instance of this lambda function a connection will be created and then all the other requests that lambda function will handle will be handled by the handler and the same connection will be reused for all of them this is much more efficient than trying to create a connection on every request so let's actually create the connection and see how it works i've imported the next object from the next library and i'm going to pass it two params first one is a client which will be mysql and this basically tells next to use the mysql library that we installed in here and then it also expects the connection object if we were using the mysql library directly we would just pass it the connection object but since we use nex it will pass the connection object to the mysql library for us now that our connection was created we can use this connection within our handler let's also create a counter in here every time our handler will be called will increment our counter now let's add a few queries to our database what we're going to do in our handler is first of all we're going to insert a new row every time there is a new requester along the function so every time the handler is called we are going to create a new row and we'll add the counter to it then we're going to select all the rows from our table and then log them to the console now let's deploy our function and see that it works in the previous video i showed how to create a deployment script such as this one then i'll link to this video below now that our function was deployed we can go to the console and try to run it so we'll go to the test tab invoke and the result we can see that there are two rows now first the row that we created using the mysql cli and then the second row with the first execution of the slam the function so if we'll call it again we should have three rows now so the first time we called this function on execution number one the connection was created and the count variable was initiated to zero and then when we called the handler the count was incremented to one and that's how we got the first execution but on the next the following execution we reused the same connection so the count variable was still in memory that's how we got the value 2 for it even though we called the handler function twice the instance of this lambda function still kept this count variable in memory and in a similar way it also kept this connection to mysql in memory as well so every time we call our lambda function we'll keep reusing this connection and the counter will keep being incremented however if we'll deploy a new version of our function let's try to publish new version and now if we'll invoke it again we'll see that the latest row that was added was execution one so when we deploy a new version of the lambda function a new connection will be created and this variable will be of course reset to zero so we can't rely on the fact that this connection will always be reused but at least we'll be able to reuse it per instance of the lambda function and it's really up to aws how many instances of this lambda function will be created for example if our lambda function starts handling a lot of requests at the same time then aws might decide that it will need to create more instances of this lambda function and every instance will have its own mysql connection so these connections won't be reused but then for this instance all the requests that will be handled by it will reuse the same mysql connection that we created here let's say this is our lambda function and mysql rds database and we connect from our lambda function to the database as we just saw then let's say we have another lambda function so this function will have its own connection to our mysql database and then maybe a third function and a fourth and fifth and etc and it's possible that one of these functions handles a lot of traffic and then aws will start several instances of these functions and then each one of them will have their own connection it will actually work pretty well for even above average number of requests because the mysql database will be able to handle all of these requests and the different connections to it so i would say for an average or even above average amount of traffic you don't need to worry about having too many connections however if you start making a lot of connections and you have a lot of functions like this then the mysql database might be overwhelmed with the number of connections and will start having issues with handling all of them this is where rds proxy comes in because with an rds proxy instead of connecting directly from the lambda function to the mysql server we're going to connect to the rds proxy instead and then the proxy will connect to the mysql database for us this way the proxy will be able to manage all the connections to the mysql database much more efficiently it will handle failovers better and make sure that the connections that it does make will be reused and that the mysql database will not be overwhelmed by the number of connections nowadays proxy also gives the benefit of security because when we use rds proxy we give it the username and password to the mysql database and then we don't need to use these username and password in the lambda functions we can just use iam to manage the permissions of which lambda function can connect to the rds proxy and turn to the mysql database and since we use iem to manage all the permissions for all of our aws services we'll get the benefit of being able to use it for this use case as well and avoid putting the username and password to the mysql database in our lambda functions so now let's convert the connection from our lambda function so instead of connecting directly to the mysql database we will now create an rds proxy and then connect to it and let it handle all the connections to the mysql database for us by the way my youtube channel is all about practical videos about aws such as the one you're watching so if you enjoyed this video and you learned something from it don't forget to subscribe to my channel now let's create the proxy that we're going to use in our lambda function so in the amazon rds console we'll go to proxies and create proxy we'll give our proxy a name then we'll choose mysql as the engine we'll choose the database that we are going to use currently we're connecting to this database directly from the lambda function but we're going to establish a connection from the proxy to the database so the last thing we have left is somehow let the proxy know about the username and the password for our database so it can connect to it and we're going to do that using the secrets manager so we'll create a new secret and when creating a new secret there is an option to create credentials for an rds database so we'll give it the username and the password the password is demo pass the same password that we used in our lambda function and finally we'll choose the database that we're going to connect to from the proxy we'll give our secret a name we'll disable automatic rotation now in the rds proxy settings we can choose the secret from the options in our example we won't use iem for authentication we'll keep using the username and the password but as mentioned before you can use iem authentication when using the rds proxy and this way you don't need to pass the username and password to the proxy a proxy is currently being created and once it will finish we should have a proxy endpoint when the proxy endpoint is ready we're going to copy it and under lambda function if you'll go back to the configuration the host is currently set to the address of our rds database but we're going to replace it with the address of the proxy everything else stays the same but instead of connecting directly to the rds mysql database we will now connect to the rds proxy and then in turn the proxy will connect to our database we still need to pass the username and password and the proxy will match it to the ones we stored in the secret manager and then use these credentials to connect to the database so now everything should keep working the same as it worked before let's try to deploy this lambda function and see that it still works [Music] there's one last thing that we need to do before we try to run this function and that is to let this function know that we're going to use the proxy we just created so we'll go to configuration database proxies and add database proxy since we already connected the proxy we'll choose an existing database proxy and click add now we should be able to run our function just like before so we'll go to test and run invoke and our function still works and we can see in the results that it still reads the rows from our database however now instead of connecting directly to the mysql database we connect to the rds proxy that we created and we let this rds proxy to manage the connection to the mysql database for us so at this point we can add more lander functions and configure all of them to use the same proxy that we created for our database this proxy will know to manage all these connections efficiently reuse them handle failovers and make sure that our database is not overwhelmed by too many connections in this video we saw how to establish an efficient connection from a lambda function to a mysql rds database then we saw what is the purpose of an rds proxy we created one we adjusted our lambda function configuration to connect to the proxy instead of directly to the database and so what is the purpose of rds proxy in terms of reusing connections and making the connection more efficient and error proof if you learned something from this video please leave it a like so it will reach more people and if you'd like to be notified when i upload more videos similar to this one please don't forget to subscribe to this channel

Channel: BiteSize Academy

Views: 7,316

Keywords: AWS, RDS, lambda, serverless, mysql, mariadb, proxy, database

Id: jOLgUjcTFEI

Length: 14min 34sec (874 seconds)

Published: Thu Jan 21 2021