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.
