How to Run PostgreSQL in Visual Studio Code

Summary

This video tutorial demonstrates how to install and set up PostgreSQL in Visual Studio Code. It walks through the process of downloading PostgreSQL, installing it on different operating systems, and setting up databases using both SQL Shell and pgAdmin. The tutorial also covers how to use PostgreSQL extensions in VS Code to connect to a local database, create tables, and run SQL queries. Additionally, the presenter introduces two useful PostgreSQL extensions and explains how to effectively work with databases in VS Code, offering multiple options for users based on their preferences.

Takeaways

• 🖥️ The video shows how to install and set up PostgreSQL in Visual Studio Code (VS Code).
• 💡 The creator plans to make courses on Visual Studio Code and PostgreSQL, encouraging viewers to subscribe for updates.
• 🌐 The first step is to download PostgreSQL from the official website, selecting the appropriate version for your operating system.
• 🔑 During installation, the user sets a password for the PostgreSQL database, and the default port is 5432.
• 📂 The video shows how to access PostgreSQL tools like pgAdmin and SQL shell for database management.
• 🛠️ PostgreSQL can be accessed via pgAdmin or psql commands to perform SQL operations like creating databases and tables.
• 🔗 The video introduces the VS Code extension for PostgreSQL (by Chris Kolkman) to connect and manage databases directly in the editor.
• 🚀 The process of creating tables, inserting data, and running SQL queries within VS Code is demonstrated step by step.
• 🔄 Another PostgreSQL extension for VS Code is also recommended, supporting multiple databases like MySQL and MongoDB.
• 📚 The creator hints at a full course on PostgreSQL and asks viewers to like and subscribe for future content.

Q & A

What is the main objective of the video?

The main objective of the video is to demonstrate how to set up and run PostgreSQL on Visual Studio Code, as well as to inform viewers about upcoming courses on Visual Studio Code and PostgreSQL.

What is the first step in setting up PostgreSQL as mentioned in the video?

The first step is to download PostgreSQL by visiting the official website and selecting the installer file based on your operating system.

What is the default port number for PostgreSQL, and when might it change?

The default port number for PostgreSQL is 5432. In rare cases, it might change to 5433.

How do you connect to a PostgreSQL server locally using the SQL shell?

You connect by using the default settings: server as 'localhost,' database as 'postgres,' port as '5432,' and username as 'postgres.' You will need to enter your password to complete the connection.

What tools are mentioned for interacting with PostgreSQL besides the SQL shell?

The tools mentioned are PGAdmin 4, a graphical user interface for managing PostgreSQL, and extensions for Visual Studio Code like PostgreSQL Explorer.

What steps should be followed to install the PostgreSQL extension in Visual Studio Code?

You should open Visual Studio Code, go to Extensions, search for 'PostgreSQL,' and install the extension by Chris Kolkman. After installation, reload the Visual Studio Code window.

How do you create a new table in PostgreSQL using Visual Studio Code?

After connecting to a database in Visual Studio Code, you can create a new table by writing an SQL query, such as `CREATE TABLE emp (emp_id INTEGER, emp_name VARCHAR(50), emp_address TEXT);` and running it with F5 or the Run Query option.

How can you view existing databases and tables in PostgreSQL through Visual Studio Code?

You can view existing databases and tables by clicking the PostgreSQL Explorer in Visual Studio Code and expanding the database and schema tree to see the tables and their data.

What alternatives are provided if the first PostgreSQL extension doesn't work in Visual Studio Code?

If the first extension doesn't work, the video suggests installing another PostgreSQL extension by Sweizan, which supports multiple database types including PostgreSQL and offers additional features.

Why is Visual Studio Code considered a good option for working with PostgreSQL according to the video?

Visual Studio Code is recommended because it allows users to run SQL queries, manage databases, and create tables directly within the IDE without needing to switch to other tools like PGAdmin or SQL shell.

Outlines

00:00

🚀 Introduction and Objectives of the Video

The creator introduces the purpose of the video, which is to show how to run PostgreSQL on Visual Studio Code. The creator also mentions plans to create courses on both Visual Studio Code and PostgreSQL, encouraging viewers to subscribe for future notifications. The video begins with a promise to provide multiple methods for setting up PostgreSQL, emphasizing the importance of watching until the end to avoid any issues during installation.

05:02

🖥️ Installing PostgreSQL Database

The first step in the video is to download the PostgreSQL database. The creator demonstrates how to find the download link on the official website and select the appropriate installer for the operating system. Detailed instructions are given for installing PostgreSQL version 14.2 on a Windows machine, including setting up the installation directory, choosing the default port (usually 5432), and specifying a password for connecting to the database. The creator also explains using Stack Builder to connect to the server and concludes by verifying the installation through PG Admin 4 and the SQL Shell.

10:03

🗃️ Creating and Managing Databases in SQL Shell

The creator demonstrates connecting to PostgreSQL using SQL Shell, showing how to interact with the database through various commands. Commands like `\l` to list databases, and SQL commands to create a new database (e.g., `CREATE DATABASE dev;`) are explained. The newly created database is verified, and the creator highlights the use of SQL Shell for managing databases and tables directly, including connecting to and working with specific databases using simple commands.

15:05

🛠️ Setting Up Visual Studio Code for PostgreSQL

The creator provides detailed steps to set up PostgreSQL in Visual Studio Code by installing a specific extension (`PostgreSQL` by Chris Kolkman). The installation process is demonstrated, followed by instructions to connect to PostgreSQL through the extension. The video guides viewers on creating and running SQL queries within Visual Studio Code, including creating tables (`CREATE TABLE emp ...`), inserting records (`INSERT INTO emp ...`), and verifying the changes. It is highlighted how Visual Studio Code allows for convenient database management without needing additional tools.

20:08

🔄 An Alternative PostgreSQL Extension for Visual Studio Code

The creator introduces another PostgreSQL extension for Visual Studio Code as an alternative (`PostgreSQL` by Sweizan), which also supports other databases like MySQL, SQL Server, and SQLite. The setup and connection processes are similar to the previous extension, and the video highlights the features of this new extension, including database exploration, viewing table structures, and other operations like dropping tables or adding columns. The creator concludes by summarizing the different methods for running PostgreSQL and encourages viewers to subscribe for more tutorials.

Keywords

💡PostgreSQL

PostgreSQL is an open-source relational database management system (RDBMS) that emphasizes extensibility and SQL compliance. In the video, PostgreSQL is the central tool being installed and configured within Visual Studio Code (VS Code), where the user demonstrates how to set it up, run queries, and perform database operations.

💡Visual Studio Code (VS Code)

Visual Studio Code (VS Code) is a popular lightweight code editor developed by Microsoft. It supports numerous programming languages and offers various extensions. The video focuses on integrating PostgreSQL with VS Code, showing viewers how they can manage databases directly from this editor using extensions.

💡PG Admin

PG Admin is a web-based graphical user interface (GUI) tool that allows users to manage PostgreSQL databases. In the video, PG Admin is introduced as an alternative method to connect to and manage PostgreSQL databases, highlighting its ease of use for performing SQL operations and viewing database details.

💡Extensions

Extensions in VS Code add functionality to the editor, allowing it to support additional languages, frameworks, or tools. In this video, the presenter installs a PostgreSQL extension by Chris Kolkman, which provides an SQL Explorer feature, enabling the connection and management of PostgreSQL databases from within VS Code.

💡Database

A database is an organized collection of data that can be easily accessed, managed, and updated. The video demonstrates how to create, connect to, and manage PostgreSQL databases using various tools, such as the SQL cell, PG Admin, and VS Code's PostgreSQL extensions.

💡SQL

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Throughout the video, the presenter uses SQL commands like CREATE DATABASE, CREATE TABLE, and SELECT to perform operations in PostgreSQL, showcasing its importance in database management.

💡PSQL

PSQL is the command-line interface for interacting with PostgreSQL. In the video, the presenter uses PSQL to connect to the PostgreSQL database, run SQL commands, and perform operations such as creating tables and inserting data. It's introduced as a tool for users who prefer command-line interaction over graphical interfaces.

💡localhost

Localhost refers to the local machine or server where PostgreSQL is running, often represented by the IP address 127.0.0.1. The video frequently refers to localhost when connecting to PostgreSQL from tools like PSQL and VS Code, emphasizing that the database is being hosted locally.

💡SQL Query

An SQL Query is a request made to the database to retrieve, insert, update, or delete data. In the video, SQL queries are demonstrated for actions like creating tables, inserting data into them, and retrieving records using the SELECT command. These queries form the foundation of how the presenter interacts with PostgreSQL.

💡Port 5432

Port 5432 is the default communication port for PostgreSQL. The video mentions this port multiple times when setting up PostgreSQL connections, ensuring that the database is accessible through this port when connecting from PSQL, PG Admin, or VS Code.

Highlights

Introduction to running PostgreSQL on Visual Studio Code.

Announcement of future courses on Visual Studio Code and PostgreSQL.

Step-by-step guide on downloading PostgreSQL via official website.

Explanation of PostgreSQL installation process and setup for Windows.

Detailed instructions on setting up a database password and configuration settings.

Insight into default PostgreSQL port settings (5432, occasionally 5433).

Using pgAdmin to connect to the PostgreSQL database locally.

Introduction to using psql tool for connecting and managing PostgreSQL databases.

Creating and verifying a new database (named 'dev') in PostgreSQL using SQL shell.

Connecting to PostgreSQL via pgAdmin, and managing databases through the interface.

Installing Visual Studio Code extension for PostgreSQL (recommended from Chris Kolkman).

Reloading Visual Studio Code and verifying PostgreSQL Explorer setup.

Demonstration of creating and inserting records in a table ('emp') via Visual Studio Code.

Using an alternative PostgreSQL extension (from s w e i z a n) for extended compatibility.

Overview of managing databases, including creating tables and records, entirely within Visual Studio Code.

Summary of options to work with PostgreSQL (pgAdmin, SQL shell, or Visual Studio Code).

Transcripts

in this video i will show you how you

can run postgresql on visual studio code

and if you want to master visual studio

code or postgresql then i am planning to

create courses on visual studio code as

well as postgresql so what you have to

do is you just have to subscribe to our

channel so that once the videos are

published you will get further

notification so without wasting much

time let's get started

[Music]

all right i will share multiple ways

using which you can install or you can

set up your postgresql in vs code so

watch the video till end so that you do

not face any issues so let's start all

right so the very first step is to

download postgresql database so in your

favorite browser in my case i have

google chrome just type download

postgresql and the link you get is

www.postgresc

this is the official website and you

just come here and based on your

operating system you can download the

installer file in my case i have windows

i'll just click on this windows button

it will lead to this particular place

and i'll just click on this download the

installer

and it will lead to an external website

that is enterprise db.com because they

are maintaining this

so at this moment while recording this

video

the postgresql version is 14.2

so based on my operating system so in

case of windows you can click here if

your mac you can download from here in

case you have linux then you can

consider clicking on this particular

link so let's click on this particular

link so that i'll download the installer

file for windows

so the file is downloaded i'll just

click on this executable file

click on next you have to select the

installation directory i'm not going to

change this so you can see that it is

going to settle in program files

postgresql and the version at this

moment is 14.

i'll check all these so by default all

these options are checked and i'll just

click on this next

again i'll just click on next

and you can give the password so this is

the password using which you will

connect to your postgre sql so do not

miss it so i'll just give a password

simple password

and

i'll just click on next

you remember that for postgresql the

port is 5432 in few of the rare

scenarios it will be 5433 so i'll just

click on next

and you see here it is giving a lot of

options for locale i'm not going to

change this and i'll keep the default

local

click on next

and again i'll just click on this next

button and it is ready to install

now you can see that it is finished

this option should be checked and this

is stack builder and i want this click

on finish

and now you see that it says which is

the server you want to connect so in my

case i have postgre sql 14 i'll just

click on this i mean i'll just select

this and if you want to connect to a

remote server you can have this

particular option but in that case you

should be having all the details of that

particular remote server in my case we

will be installing this on our local

machine that's the reason we'll select

the second option

click on next

and it is going to download

these options and actually these all

options are not required so

what you can do is you can just cancel

it okay

and i'll just click on this yes

all right now what i'll do is i'll just

go to this particular option

that is start and i'll just come down

and i should be having p right you see

here under p i have postgresql and these

two tools are very important under this

first one is pga admin 4 using which you

can connect to database and you can

write the sql commands and you can

perform lot of sql operations the other

one is sql cell so if i just click on

this sql cell using this sql cell also i

can connect to my postgre sql database

you see here it says server as localhost

i'm not gonna change it please remember

it is localhost

database is postgres please remember i'm

not gonna change it at this moment

port is 5432 so i'm not gonna change it

username is postgres please remember i'm

not gonna change this at this moment and

password you can give your own

and please remember this password using

which you will connect to your database

so i'll just give here

a simple password please remember this

and now what i'm gonna do is you see

here it says type help for help now just

type help and you see that it gives few

options for example backslash h is for

all the sql commands

backslash question mark is for all the

piece sql commands and so on so let's

say that if i just give backlash edge

you see that all these options are

related to my database all right if i

just give q q is for coming back or

escape so if i just give q

you see that i have just come back and

what i can do is for example i can just

give backslash l to check the current

databases

now you see i have three database at

this moment and if you want you can

create your own database for example i

can just give here create

database

and i can give name of a database for

example

dev and then semicolon at end

you have enter

and just wait for some time

so that it creates a database for you

now you see that this particular create

database has worked and it has created

one database if i just give back class l

you see that now i have four rows and

this is the database which is created so

i just wanted to show you something

about psql and let's say that if i just

close this

and i just forgot to tell you okay i'll

just show you again

uh if i just go to

psql again so let me just open this if i

just go to p

i'll just come here and you see you can

have a shortcut for this as well but i'm

not going to create any shortcut at this

moment click on this

give enter

database this time i'll just give div

because i want to connect to dev

database only give enter port is fine

username is also fine password i'll give

my password

now you see that i have connected to

this particular database so at this

moment i do not have any record or i i

do not have any tables in this

particular database now i just wanted to

show you that you can connect to your

own database only using sql cell now

let's close this

okay now

i'll just show you another option using

pg admin

i should be having pg admin

so under postgresql you see i have pg

admin 4 and you can connect the database

using this pd admin 4 as well

so it is asking to set a master password

i'll give a particular password

and now you see here

i have these servers if you just click

on the servers

i have postgresql 14 and i'll just give

password so this is the password which

you have to give to connect to

postgresql server and i ask you to

remember this password just give okay

and you can see that you have lot of

details in this particular tool

so this is a very good tool and in fact

i'm going to have a particular course on

postgresql where i'll be using this

particular tool so if you want to check

that particular course you can check the

description of this video and you might

get the link by the time you'll see this

video all right and if i just go to this

databases if i just click on this

databases

you see i have two database at this

moment one is postgres and one is dev so

you see there is a question mark here

because i have not connected to this if

i just click on this particular database

so you see that it says please connect

to the selected server to view the

dashboard so at this moment there are no

tables so i'll just close this but this

is the default database that is postgres

and if i just click on this if i just go

to schema

you see i have this public and under

this public if i just come down i have

tables

if i just click on this particular table

and now

you see that i have query tool i have

psql tool i have reference button and i

can create a table right so at this

moment for this particular database

under tables again we don't have a table

so if you want you can create a table

itself

so just give right click and you can

click on create table

and

you can create a table using this but

this video is not about pg admin 4

rather we will be using the visual

studio code to connect to postgresql

database and we will be creating our

table or in fact we can create databases

and we can perform lot of other

operations i mean sql operations using

visual studio code so first i'll just

close this

again i'm gonna close this

and what i'll do is first i should be

having visual studio code so i'm sure

that you guys are having visual studio

code if you do not have it go to this

link that is code.visualstudio.com

and you can download the visual studio

code based on your operating system for

windows you can download from here for

mac you can see that the stable version

is here and for linux you can download

from here all right if you want to

install a fresh installation of visual

studio code make sure that

normally you check all these four

options i always suggest this okay let

me just close this and just open your

visual studio code and what you can do

is first the very important step is that

go to extensions

and i'll tell you to install one

extension so if i just search for post

gray sequel you see the first one okay

so this is a very good extension

and this is from chris kolkman please

note that there is another extension

that is this

sql extension from microsoft do not

install it and this is not a very good

extension in fact you can install the

previous one from chris kolkman just

click on install button and i'll show

you how easy it is to connect to

postgresql using this extension i always

suggest that whenever you install an

extension you should reload your visual

studio code so i'll just give shift

control and p to open the command

palette and here i'll just give

developer reload window so that my

visual studio code is refreshed

automatically now you see that my

extension is installed and i'll just

click on this explorer button

and you see here i have four sql

explorer you see this elephant icon just

click on this and you see at top you

have postgresql explorer just click on

this plus button

and

you can give the host name so by default

it is

127.0.0.1 or you can just give localhost

localhost

and you can give the name of the user if

you remember the name of the user was

postgres so i'll just give postgres

give enter you can give the password

using which you have to connect the

postgre sql database i'll just give my

password

and you should have remembered this

password

and 5432 is the port

and

it gives two options

you want to connect using secure

connection so do you have a server for

which you have a secure connection or

for localhost it is always suggested you

go for standard connection so if i just

click on this standard connection now

you see that it says show all databases

or postgres or dev so if you remember we

create this dev database using sql cell

so that is the reason it is showing this

particular database as well if you do

not create that database it will only

show postgres so i'll just click on this

show all databases

and now what is the display name or

label you want to keep so i'll just give

for example

localhost

post

graysqldb

give enter so this is just a display

name you can give your own name so you

see the same display name is coming here

i'll just click on this

arrow and i have two databases the first

one is dev and the other one is postgres

so first i'll just show you how to

connect to postgres now see that if i

just click on this and at this moment i

think i don't have now first let me just

create a table so this is the default

database that is posted this what i'll

do is i'll create tables on our database

which we created so you see this is the

database which we created that is dev so

you just give right click and you see

new query

and you can create a table so i can just

give for example create

table

and name of the table so i'll just give

for example let's say i want to create a

table for employees so i'll just say

emp

so this is for employee table

and i'll just say for example

employee id and i'll just give for

example integer

okay i'll just give integer

and

now let's say

i'll just have employee name

so employee name i'll just give for

example text field or it is better i'll

just give where care

of let's say 50

and again i'll be having for example

employee address

and i'll just give a text field

so that i should not be worried about

the length

and attend i'll just give a semicolon

and you see i'll just give right click

and i'll just give run query you can

give f5

in keyboard so once i run this

you see it says null rows created that

means the table is created but there are

no records

now i'll just comment it out

first let me insert few records in the

table so i'll just give insert

into this particular table that is emp

and i'll just give emp id

comma emp name

comma emp

address

and i'll just give values

so i'll just give for employee id for

example one for employee name i'll just

give for example

sandy

and

for this particular employee address

i'll just give a dummy address

okay

and

now

if i just run this so i'll just give f5

and it says sandy does not exist so

there is a problem so okay let me do one

thing

i'll just give in single quote actually

sorry for that

and here i should be giving in single

quote

and if i just give f5

you see it says one row is created again

i'll just give here two

and i'll just give for example let's say

mandy

and i'll just give dummy street two and

again i'll run it

so again it will create another record

now i'll just comment it out

and if i just give

select

star

from

name of the table which is emp

now if i just

run this

you see i have two records

that is employer id employee name and

employee address and it is showing

employee id one two sandy mandy and then

it is showing the corresponding address

now if i just come here in the div you

see if i just refresh it

you see under public i should be having

a table you see in emp

and if you just give right click

you can just select

top

1000 records or some top record for

example if i just want to select top

thousand records i i'll just give here

i'll just select on this and it will

give me top thousand records since we

have only two records that is the reason

it is displaying only two records so

this is how you can create tables in

fact you can perform all the operations

for example alter you have drop you have

delete so all the sql statements you can

perform using this visual studio code

and you do not have to connect to pj

admin or sql cell and in fact you can

create your own database here itself so

i'll just give right click

and you see here it says you can edit

this connection you can rename it you

can have new query so if i just give new

query

i can create my own database so i can

just give here create

database

this time i'll just give the name of the

database as prod okay and i'll just give

here database

and if i just give f5 or run query

you see it says waiting for query to

complete it will take some time because

it is creating a database which involves

lot of indexes clusters and so on so

that is the reason it is going to take

some time now it says null rows created

now you see here if i just refresh it

now

you see

i have dev and prod again

if you just come here you can have a new

query and you can create tables here

now if for some reason this particular

extension is not working for you i'll

tell you another extension which is

extremely good

so go to extension manager again

give post

gray sql

and you have this particular extension

that is postgresql from s w e i z a n so

this is an awesome extension which i

have myself tested it out and it behaves

in a very perfect manner i'll just click

on this install button

now it is installed i'll just go to

command palette and i'll reload the

window again so that whatever extension

i'm uploading or i'm adding it should

behave in a proper way

all right and now you see that

once you install this extension it is

going to create two particular icons so

here it is database explorer and at the

bottom it is other explorer so the

database explorer i'll just explain

first

and you see first let me just come to

this particular option that is explorer

click on this database explorer and you

see here you can connect to github as

well and you can refresh and there are

other options which i'm not gonna talk

just click on this plus so that you can

add your own connection so if i just

give plus

first let me just close the previous one

it is not required

and

now this is also not required

this is also not required

okay now you see if i just click on this

plus button

i'm getting this option that is

connected to database server the beauty

of this extension is that it is

supporting different different databases

for example mysql postgresql sql server

sqlite mongodb and so on since this

video is all about postgresql i just

click on this postgresql and hostname

either i can just give 127.0.0.1

which corresponds to localhost if you

remember port was 5432 username was

postgres you have to give the password

using which you have to connect to

postgresql so i'll just give my password

and if you remember i told you to

remember this password and i'll just

click on this

connect option you see databases and all

you don't have to change because i'm

going to connect to postgres if you want

to connect to different database you can

give that particular database name here

okay just click on connect now

and now we see that i have these three

databases now in the same way you can

perform all the operations here so for

example let's say that i was into dev

and i was into this particular public

right

and we created this table that is emp

now you see if i just give right click

you see there are a lot of options here

now you can drop the table you can

generate the mock data you can add a

column using alter command and so on and

you see if i just click here see this

has given me the details of this

particular table i mean how many records

this particular table contains so this

is a very clean interface

and if the previous extension is not

working for you you can install this

extension and it will behave in a very

good manner

so that's it in this video i hope you

enjoyed this video

and i have given multiple ways of

running postgresql commands for example

you can run it using a sql cell you can

run it using pga admin and in fact the

best way is that you can connect with

visual studio code itself if you are a

vs code lover and you can install either

one of the extensions and you can work

in a very proper way without any issues

with postgresql so that's all if you

like the video or the video was helpful

please hit on the like button and if you

want to watch the complete tutorial on

postgresql do subscribe to our channel

so that once i publish that video you'll

get the notification and that's it keep

on practicing i'll meet you in our next

video till then take care bye

[Music]

hold me close till i get up

time is

[Music]