MySQL Async Pooled Connection Connector with #tutorial #mysql
Create a MYSQL connection outside of the linked resource method using module.exports
Credits:
@Zane: Thanks bruh.
@FatSack, @Leguchi, @Akanixon (everyone had a part, even if not knowing it :) )
Updated: Wednesday, March 15th 2023
- Now includes a Google sheets to MySQL table duplicator
- Charset updated to allow emoji inserts
- Updated for cases when column names match sql keywords thnx Roh
❗ Files utilizing module.exports need to remain outside of the functions folder to operate correctly. ❗
This setup uses a pooled connection and the execute helper for queries.
Advantages to using a pooled connection with query execution:
- Using a Pool reduces connection overhead by reusing a previous connection.
- Using the execute method improves security against SQL injection.
Getting Started:
You will need a SQL database to connect to.
For convenience I am including a quick tutorial thread on using PlanetScale as a MySQL solution.
See Thread here: https://autocode.com/Good-sie/threads/mysql-async-pooled-connection-connector-tutorial-224c1893/
Following the tutorial should have you ready to use the MySQL Connector App! 💙
So now you're thinking; "Great! But, how do I use it?" ❓
Let's get into that now.
Environment Variables
process.env.HOST,
process.env.USERNAME,
process.env.PASSWORD,
process.env.DATABASE,
These are the variables used in the mysql helper file to create a connection to your database.
This file can be found in the following location:
I do NOT recommend editing this file unless you are aware of what you are doing.
That aside, it does have many options that I have kept disabled as they are not required for general use.
More advanced users may want to have a look if certain specifics are needed.
Ok, enough of that. Lets get back to our database usage. ✅
In the functions/queries folder I have added an example table CREATE script:
The contents of that file are:
I have created this within the functions folder so that it can be ran from the editor to show console output.
Please do so now using the run button on the bottom right of the editor view.
You should now see something like this:
Notice serverStatus: 2. This means we have successfully executed our script.
To further verify the successfulness of your table creation; You can select Schema from the menu links in your PlanetScale dashboard:
this will require you having setup an account with PlanetScale
You will likely need to Refresh your Schema generation. You can do so by clicking the button labeled "Refresh schema".
This will show you your newly created table in all its glory 🥰
We are now 100% positive that we have been successful up to this point.
Let's put some data in that table to play with. 📡
Again in the functions/queries folder I have added an example table INSERT script:
The contents of that file are:
Go ahead and run this script now.
Notice this time we have affectedRows: 1 and serverStatus: 2. This means we have successfully written something to the table.
Lets go ahead and get that data back so we can see what it looks like.
I am going to show 2 methods of accessing the data. Why? Because the data returned will be an Object within an Array.
In the functions/queries folder I have added an example table SELECT script:
Let's call this our 'pure' SELECT method and Result.
Which gives us:
As you can see, we have an Array [] with an Object {} in our result.
So if we now select an Array position to return, exampleResult[0] like in the following example.
We get a result that looks like this:
You can then access the information using the following method:
Which will give us a result of:
You have now successfully done the following:
- Created a database.
- Created a table in a database.
- Written data to a table.
- Extracted data from a table.
- Used and Displayed information from a MySQL Database.
This the part where you pat yourself on the back 🥰
