How to connect to MySQL/MariaDB using SQLTools in VSCode

SQLTools is a versatile extension for Visual Studio Code (VSCode), designed to simplify database management tasks. It supports a plethora of database systems, providing an intuitive interface used to connect, to query, and to manage databases right from your code editor.

With SQLTools in VSCode, you can streamline your database workflow, making it a valuable tool for developers who interact with MySQL or MariaDB databases regularly.

Install the version of VSCode for your operating system

Visit the VSCode download page and choose the version for your operating system. Then, go through the installation prompts to finish the setup.

Installing the SQLTools extension

Open VSCode, then click the Extensions button on the left-hand side (or use the Ctrl+Shift+X keys on Windows or the Command+Shift+X keys on a Mac):

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

Adding a database connection

Once the extension is installed, click the SQLTools icon in the sidebar. Next, click the "+" (plus) icon to add a new connection or click the Add New Connection button. Choose MySQL (or MariaDB, depending on your database) from the list of Database Management System (DBMS) options:

Fill in your connection details: host, port, server address, username, and password:

• If you connect using the Remote Explorer extension or to a local MySQL/MariaDB server, your Server Address will be set to the "localhost" value.
• If you are connecting to an external MySQL/MariaDB server, your Server Address will be set to the public IP address where your MySQL/MariaDB server resides. If you run into errors, try the following procedures:
  • Ensure your clientside IP address (where you are connecting from) is allowed in your firewall. For most Liquid Web servers, you can use the following command in your server’s terminal:
    • echo "tcp|in|d=3306|s=YOURIPADDRESS" >> /etc/csf/csf.allow
    • csf -r
  • Also, you can yry using the connection string method instead.
    • mysql://username:password@youripaddress:3306/yourdatabasename
• In the examples above, remember to replace these placeholders with actual values:
  • YOURIPADDRESS
  • username:password@youripaddress:3306/yourdatabase

Click Test Connection so you can make sure your connection is successful before clicking Save Connection.

Connecting to your MySQL/MariaDB database

After configuring, click the Connect icon next to your connection details. You can view and manage your MySQL/MariaDB database directly from VSCode using SQLTools:

Access tables, views, records, and perform queries

Once you are connected to your database, click the dropdown arrows next to the connection's name. Then, click the database's name to access its tables and views. You can right-click a specific table and choose Show Table Records to view the data:

You can also create queries when you first connect to the database, as a blank session.sql file is automatically created for you. As well, you can click the New SQL File button to create a new .sql file for query statements or insert a query into a specific table by clicking the “+” (plus) icon next to the particular table:

Next steps after establishing database integration for MySQL/MariaDB using SQLTools in VSCode

Now, you have a streamlined the workflow within VSCode to interact with your MySQL/MariaDB database using SQLTools. VSCode and this setup not only augment your database management capabilities, but also integrate seamlessly into your development environment.

As you grow accustomed to this setup, you'll find executing queries, viewing records, and managing your database to be a smoother and more efficient process. Happy querying!