VS Code is a popular IDE for many kinds of development.
If you want to access your database to run SQL, you can use a separate tool.
But you don’t have to. You can run SQL statements on your database from directly within VS Code,
saving you time from switching tools.
In this video, we’ll install the plugin in VS Code to do this, connect to a database,
write and run some SQL, and see a few other features in the plugin.
In order for you to connect to a database from VS Code, you’ll need to have VS Code
downloaded and installed. You’ll also need to have access to a database, such as SQL Server.
In this video, we’ll be creating a connection for SQL Server, MySQL, and PostgreSQL. Oracle database
is not supported with this particular plugin, but there will be a separate video for that.
I assume you are using another tool to access the database, such as SQL Server
Management Studio or MySQL Workbench, and want to use VS Code to access the database instead.
I’ve got separate videos on setting up a database if you want to watch those.
Let’s get into it.
Here we have VS Code. The first step to access a database from VS Code is to add an extension.
To do this, go to Extensions. You can click on this icon on the left sidebar,
or press Shift Command X on a Mac, or go to View, then Extensions.
The Extensions panel will be shown. In the search box at the top,
type SQLTools without a space. This is the name of the extension that we will use.
Click on the extension that appears in the list,
called SQLTools, by Matheus. At the time of recording, it has 1.5 million downloads.
You can see more information in the main panel here when you click on the extension.
You’ll see a list of supported databases, which includes AWS Redshift,
MariaDB, SQL Server, MySQL, PostgreSQL, and SQLite.
Oracle is not supported by the recent version of this extension. However,
there is another extension by Oracle that you can use. I’ll demonstrate that in another video.
On this page here for SQLtools, click on Install. The extension will be installed in VS
Code in a few seconds. You’ll see a cylinder database icon appear on the left sidebar.
In the list of connections, there will not be any listed. Click on the Add New Connection button.
A new tab will open to help you connect to the database. However, there are no buttons
or icons to click. Just a message that says “Couldn’t find any drivers installed yet”.
This message just means that there are no drivers installed for any of the database vendors.
This is easily fixed though. Click on the link “Search VSCode marketplace”. This will run a
search in the Extensions list or Marketplace for anything with a tag of sqltools driver.
There are a range of drivers for different databases. We’re going to install a few of them:
one for MySQL, one for Postgres, and one for SQL Server.
If you only need one of these databases, you can just install that one.
You don’t need all three, and you don’t need SQL Server if you only work with MySQL, for example.
First, click on the MySQL MariaDB driver.
You can click on the small Install button on the left, or you can click
on the search result to see the details in the rest of the window like I have here.
Click the blue Install button at the top, and in a few seconds it will be installed.
On the left in the search results, click on the Postgres drivers entry,
and install that too, if you want Postgres.
Finally, click on the Microsoft SQL Server entry and install that driver if you want that.
All of the drivers we need are now installed.
Now that we have our drivers installed, we can try to connect to the database. Click on the
database icon on the left sidebar. Then, click on the Add New Connection button.
Now, on the Connection Assistant page, we’ll see a few icons for each of the drivers we
have installed. If you have installed one driver, you’ll see fewer icons.
We’ll set up a connection for MySQL, PostgreSQL, and SQL Server in this video.
You can ignore the steps for the databases you’re not using.
We’ll connect to MySQL first. Click on the MySQL icon, and you’ll see this connection tab.
Enter a connection name that you can use to identify it in the list of connections.
I’ll enter MySQL Local.
The connect using can be left as Server and Port. I’ve left the
server as [localhost](http://localhost) as I’m connecting to a database on my own computer,
but you could change this if your server name is different.
For the port, the default is set to 3306. However, I had issues with connecting for a while,
and after some research, there was a suggestion to use port 33060 which works for me.
For the Database, enter the database that you want to use.
Now, this is a mandatory field on this form, and the database needs to exist. This is OK if
you already have one, but if you’re setting up a database installation from scratch, then you won’t
have one. I’ve created an empty one called new_app using MySQL Workbench, which you may need to do.
Enter in the username. In my database, the username is root, and yours may be different.
If you want to save the password, change Use Password to Save Password.
Enter the password to use for this account.
Change the Authentication Protocol to xprotocol, otherwise you’ll get errors connecting.
The Connection Timeout may be left blank.
I had issues with an idle wait time when running queries,
so I could enter a value of 30 in here to get it to work, but I’ll leave it empty for this example.
Click on Test Connection and you should see Success.
Then, click on Save Connection.
You’ll see a JSON-formatted summary of the connection details.
If you want to proceed with working on this database, click Connect Now.
I’m going to create another connection to another database, so I’ll click Create Another.
We’re going to create a connection to SQL Server.
On the Connection Assistant screen, click SQL Server/Azure.
The connection settings screen appears.
Enter a name for your connection that is shown in the list, such as SQL Server Local.
For the server address, change this to whatever your address is. My database is
running locally, so I’ll leave this as localhost.
I’ll also leave the port as 1433 which is the default port for SQL Server.
For the database, enter the database you wish to use. Like the MySQL connection earlier,
this is a required field and the database needs to exist. If you don’t have a database to use,
then you can use SSMS or another tool to create one on your SQL Server, like I did with this one.
For the username, enter the user you want to connect as, such as “sa”.
For Use Password, change it to Save password if you want to save it.
Otherwise you’ll be prompted for each connection. Then, enter the password.
Now, in this panel here, there is a range of SQL Server-specific parameters.
They can all be left as the default values, unless you have a specific need to change any of them.
Scroll to the bottom and click Test Connection, and you should see Success.
Click Save Connection.
Just like with MySQL, you’ll see a JSON-formatted summary of the connection details.
If you want to proceed with working on this database, click Connect Now.
I’m going to create one more connection to a Postgres database, so I’ll click Create Another.
To create a connection to Postgres, click on the PostgreSQL icon.
A new connection tab will be shown.
Enter a connection name to be shown in the list of connections, such as Postgres Local.
For the server address, change this to where your database server is.
I’m running Postgres on my computer, so I’ll leave this as localhost, but you may want to change it.
For the port, the default is 5432 which is the default Postgres port
but you can change it if needed.
The database field is required, just like the MySQL and SQL Server connections.
However, I was able to get this to work on my database by specifying postgres as the database,
which contains several schemas. Your database name could be different.
For the username, enter the user you want to connect as. Here, I’m entering the postgres user.
For Use Password, change it to Save password if you want to save it.
Otherwise you’ll be prompted for each connection. Then, enter the password.
You can leave the fields in the node-pg panel as their defaults,
unless you have a reason to change any of them.
Scroll to the bottom and click Test Connection, and you should see Success.
Click Save Connection.
Just like with the other databases, you’ll see a JSON-formatted summary of the connection details.
Now that we have created our connections, we can close the tab at the top of the screen.
Let’s connect to our database and start working with SQL.
In this example, we’ll use a MySQL database, but the process will be similar for other databases.
In your connection list, hover over the connection name and click on the green plug icon to connect.
You should see a green dot and an arrow appear next to the connection name,
to indicate it is connected.
You can click the arrow to expand the connection,
which will show the databases on the server, and then the tables and views within that.
In this example, there are no tables, but we can create one.
A new SQL window has appeared on the main area of the screen, where you can enter your SQL.
We’ll enter a Create Table statement to create a new table, called test_table, with one column.
Once the statement is written, we need to run it. One way to run the statement is to click
on the “Run on active connection” button at the top of the tab here.
This will run all queries in the file, which is OK for now as we only have one.
Click this button. On the right, you should see a
green box that says Query returned 0 rows. This means it was successful.
On the bottom left, we have a panel called Query History. This shows a
history of all queries that you have run, which includes the Create Table that we just ran.
On the Connection list,
we can click the refresh button here to see the new table appear in the list.
Let’s add some data to this table with an insert statement. Write a simple Insert statement to
add one row. You’ll notice here that the table name and column name both appear in
the auto complete list, which makes it easier to write the query and to get the correct objects.
Now, let’s run this query. If we click on the same “Run on active connection” button,
we’ll get an error. This is because all of the queries in this file are run,
starting with the Create Table. The table already exists, so an error is encountered.
To run just a single query, we can highlight the query we want to run in the editor here,
then right-click on it. Then, click on Run Selected Query.
Only that query will run. On the right, you should see a green box indicating it was successful,
and the query will appear in the Query History window.
Now we have inserted a value, let’s see how we can select data from the table.
Write a select query to select the sometext value from the test table.
To run just this query, highlight it, then right-click on it, and select Run Selected Query.
In the right panel, you’ll see the results of the query. We see the column header here, and the
single row that was inserted. This will show more columns and data if your table and query has them.
Let’s insert another row. We’ll copy the previous insert query and use a different value.
This time we’ll run the query in a different way. We know we can highlight a query,
right-click on it, and select Run Selected Query. However, you’ll notice that in this context menu
here is a set of keys inside brackets next to Run Selected Query. This is the keyboard shortcut,
and shows Command E Command E. If you’re on Windows it will show different characters.
This means that you can press Command E, then Command E,
and the selected query will be run. Let’s try that.
When we press these keys, the query is run. A new value is inserted into the table.
There are a few more features in this SQLTools extension that make it easy to work with data.
On the connection tree on the left, if you hover over a table name you’ll see two icons:
a plus icon and a magnifying glass icon.
Click on the plus icon, and an Insert statement for this table will be added
to your SQL editor window where your cursor is, with the first value highlighted. This
makes it very easy to generate an insert statement for your table.
You can then enter a new value, and run the statement, and the row is inserted.
The second icon you see when hovering over the table is the magnifying glass.
Click on this icon, and you’ll see the results of a select query on the right
panel. It shows three records in our table, which is what we have inserted.
When you right-click on a table on the left panel, there are a few options. If you click “Add
name(s) to cursor”, then the name of the table is added to your SQL editor where your cursor is.
This is helpful if you’re writing a query and want to add the table name into it.
You can also right-click on the table and select “Copy value(s)”. This will copy the
name of the table into the clipboard, for you to use anywhere else, such as the SQL Editor.
You can do the same thing with a column: right-click on the column inside the table,
select Add name to cursor, and the column name is added to your query.
Another feature in this extension is the ability to describe a table, which
is to see more information about it. To do this, right-click on a table and select Describe Table.
On the right panel, you’ll see information about your table, such as column names,
data types, and different constraints.
The other option in the right-click menu for a table is Generate Insert Query. This is the
same as clicking the plus icon that we saw before, where it adds an Insert query into our SQL Editor.
And that brings us to the end of this video on setting up VS Code to run SQL statements.
As we can see, using this SQLTools extension is pretty easy and allows
us to run SQL queries on the database without using a different application.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the process of installing the extension,
or was it the keyboard shortcut to run a query, or something else?
Thanks for watching.