Let’s SQL on VsCode || MySQL

Picture Credit Programming guru

Hola people!

A few days ago, I felt the need to perform some erroneous SQL operations to reestablish my proficiency in crafting effective SQL queries. I haven’t worked on SQL for six months, which I feel is too long for any developer to go back to zero after being a hero (if I was one, of course, 😛).

So now, my requirements were simple:

1. I just wanted to start with basic SQL operations and simple nested queries.
2. My practice data was limited and included only simpler tables with few columns.
3. I am doing the environment setup on my own PC (MacBook Air M2), so I had no plans to install heavy IDEs like DBeaver or SSMS.

I did the setup for MySQL, but the same procedures apply to other DBs as well.

Step 1 — Install the required MySQL server according to your OS. (I did this for MacOS 13 ARM 64-bit version 8.0.34)

Note 1: DO NOT install the 8.1 version. It is not compatible with MySQL Workbench's latest version. You may run into connection issues.

Once installed, your system settings will show the MySQL option.

Note 2: You will be required to enter a root user’s password while installing the server. Keep your password safe because you’ll need it again to establish a connection to the MySQL server.

MySQL UI

Step 2: Now that we have completed the initial setup, we must use MySQL Workbench to configure the database connection. Install a similar macOS version of the MySQL workbench.

Step 3: Click on the + icon near the MySQL Connections title and give the connection the name you want to use while maintaining all other configurations (such as the localhost IP address) that are necessary. Use the root user password that you provided when creating the account.

A successful connection will end up looking like the following:

MySQL Workbench UI

Step 4: Now, verify that VSCode is installed locally. Open the program, pick the extensions tab on the left toolbar, type SQLTOOLS, and then choose Matheus’s extension (which enables connections to a variety of DBs inside VSCode).

Refer below:

The first option with a yellow DB icon

Note 3: Once the extension is installed, we can see a DB icon on the left pane which we will use to connect to DBs and execute our queries. Refer below-

Last icon in the image above

Step 6: Now go to the extensions page again and download the necessary DB driver. The one for MySQL that I installed is listed below with its precise name.

MySQL Driver for DB connection inside VSCode

Step 7: Go to the DB icon on the left & click on Add a Connection. By entering all the necessary credentials, we can now establish a DB connection inside the VSCode environment.

On successful completion, you will see the below status.

Note 4: I created a DB named sqlQueries using Workbench inside the new connection. This is because the database needs to exist while creating a connection inside VSCode.

Successful Connection in VSCode

This is how it looks after a successful connection. You can see all your tables and views there.

MySQL DB details after a successful connection

That’s it! WOOHOOO 🎊 YOU MADE IT 💯

Now, by right-clicking on the table name, you get some options to query the data present in it. Or else, you can also write a simple insert query if the table is empty.

Following are a few of the results from the queries I tried:

VSCode UI when querying a table

Data visualization in VSode

That’s all for this article. See you in the next one with something new 👩🏻‍💻

Happy Reading!! 😇