How to Connect to MySQL Database in VS Code

Pre-requisites:

Windows OS
Visual Studio Code
XAMPP

In this tutorial, we will walk you through the steps to establish a connection to a local MySQL database using VSCode, allowing you to run SQL queries on your database.

Download XAMPP

http://www.apachefriends.org

XAMPP provides a local development environment that includes a MySQL database server.

In the XAMPP control panel, locate the "MySQL" module, and click the "Start" button next to it 

Next, click on "Admin" next to the "Stop" button to open phpMyAdmin

Click "New" and input your database name and click the "Create" button

Download Visual Studio Code

http://code.visualstudio.com

Visual Studio Code is an IDE developed by Microsoft that you can use to edit, debug, and build code.

Install MySQL extension

Open the VS Code Extension Marketplace

Search for "sqltools" and click on Install

Next, click on SQLTools icon and click on "Add New Connection"

The window will output "Couldn't find any installed drivers"

Open the VS Code Extension Marketplace section once again

Search for "SQLTools MySQL/MariaDB/TiDB" and click on Install

Once the MySQL driver extension is installed, you can see the options MariaDB, MySQL, and TiDB

Once again, when you click on the SQLTools icon and click on "Add New Connection" you will see the MySQL option

Now, let's click on the MySQL option and provide the details about your connection

Since we are running the MySQL Server locally we will put "MySQL Local" for the connection name

Select the server and port option

For the server address choose localhost since the MySQL Server is running on the localhost instead of a remote host

Next, use the default port on which MySQL runs which is 3306

For database, put the name of the database that should pre-exist on your MySQL Server

For me, I put the database name testdb since this database pre-exist on the MySQL Server

Put the username which you use to connect to your MySQL server. In my case, it's root

For password mode, choose "Use empty password"

For authentication protocol, choose default

Click the save connection button

Select the "New SQL File" icon to open an SQL session window to run your SQL queries

Note: The maximum number of primary keys that you can put on a table is one

Here's an example SQL query to create a simple table called "Country" with three columns:

CREATE TABLE Country
(
   Code VARCHAR(3) NOT NULL,
   Name VARCHAR(52) NOT NULL,
   Region VARCHAR(26) NOT NULL,
   PRIMARY KEY (Code)
);

Here's an example SQL query to insert multiple rows in the "Country" table:

INSERT INTO Country (Code, Name, Region)
VALUES 
    ('ABW', 'Aruba', 'Caribbean'),
    ('FRA', 'France', 'Western Europe'),
    ('USA', 'United States', 'North America');

Copy your selected query and run the selected query

If you're trying to dock the SQL queries to the bottom in VS Code,

Go to Menu > View > Editor Layout > Flip Layout

The shortcut is: Shift + Alt + 0

How to show and hide the panel

The shortcut is: Ctrl + J

When you right click on your table, you can show the table records, describe your table, and you also have the option to generate insert query.

Describe table, is a MySQL command used to retrieve metadata about a table such as the data type, nullability, and key constraints.

In the future if you want to change the configuration of your MySQL connection, right-click on your connection and select the "Edit Connection" option.

By right-clicking on your connection, you also have the option to disconnect, refresh, and delete connection.