Connect Node to Postgres

hey everyone welcome to Wick code we're in this video we're going to learn how to connect a node application to a postgres database using the node post Grace or PG npm Library so if we want to add persistence to a node application we can use the relational database postgresql to begin let's first initialize a directory as an npm project by using npm init y this command creates a package.json file which we can see right here to hold information about the project the dash Y flag provides default information to this file as opposed to us typing it in manually and now to connect node to postgres we're going to use the PG npm Library this library is a pure JavaScript implementation of a postgresql client a postgresql client is essentially a connection to postcards SQL Server that can issue commands and database operations now we can see PG listed under dependencies so there are two different ways to connect to postgres with the PG Library a client and a pool both of these objects are exported from the PG library to use them let's create index.js file and import them so a client is one static connection to the postgres server while a pool is a dynamic number of clients that have automatic automatic reconnect functionality in other words the pool object is a connection pool while the client object is a single connection a connection pool should be used in applications they need to handle concurrent requests or frequent queries this is because connecting to the postgres SQL Server carries a fair amount of overhead for this demonstration we will use the pool object but so the best way to provide connection information to postgres is through environment variables this is so we can set up different databases for development and production to easily work with environment variables in node we use the dot EnV Library the dot EnV Library loads environment variables from a EnV file into nodes process.env object to work with this let's create a file called development.env and place some environment variables inside so these are the environment variables that we will use to connect to postgres and the user variable is the database user a fresh postgres database will have the predefined user called postrace host is the location of the postcardsql server here the server is running on our computer or localhost database is the name of the database most postgres servers have the database called postgres defined by default password is the password to access postgresql the default postgres password is postgres though it will be the password used when postgresql server was created Port is the port the postgresql server is running on by default this is Port 5432 but now let's load this environment file into our node application with DOT EnV so this config function from the EnV Library loads the environment variables into process.env we can pass it to the path option to accept a path to the environment variable file which here is our current directory and then development.env which is the location of this file we can also provide the override option which will override any environment variables already set on our system of course when more environments are added the path should not be hard-coded like it is here but rather a dynamic path to the environment variables applicable to The Chosen environment so now let's actually connect to postcard SQL with a pool so to connect to postgres with a connection pool we instantiate a pool object we then need to supply it with the environment variables that we created inside our development EnV file so this pool is initially empty and clients are created when they are needed now to ensure that we are connected let's query the postgresql server first let's check out a client from the pool this is done with the connect method we will wrap this all in an asynchronous iif so we can use the await keyword so now that we've checked out a client or connection from the pool we can use that client to query the postgres server we can query it with the query method and extract The Returned rows from the response let's get the current user which here is the postgres user let's also wrap all this inside a try catch and finally foreign so we need to wrap this query inside a try catch finally block so that we can release the client back into the pool once we've used it we want to do this whether the query was successful or not this is because if we don't release the client back into the pool then soon the pool will be depleted and there will be no clients to handle any requests however if we want the node postgres library to handle checking out and releasing clients from the pool for us we can call the query method directly on the pool as opposed to the connection checked out from the pool but before we do that let's just run this to make sure everything's working we run it with node and then the name of the file index.js and nothing happened because we need to call our Ife by having this here and what I got is auth failed and it's because my default password is not post Grace but on my low post it's just t-o-o-r so if we try this again and we get logged out of the current current user which is postgres but now let's call the query method directly on the pool and here we get the same response for the current user is postgres and what's nice about this once again is that the PG Library handles checking out a connection from the pool and returning it to it by itself we don't have to manually check out the connection here query it and then release it like we do if we check out a connection from the pool and this connection is essentially the same as working with this client right here so this is my video on connecting node to post Grace if you have any questions leave them in the comments and I'll get back to you besides that I want to thank you for watching and subscribing today and I hope to see in the next one have a good one

Channel: WittCode

Views: 13,359

Keywords: WittCode, wittcode, node, postgres, postgresql, connect node to postgres, postgres node, postgres node connection, connection pool, pg, node-postgres

Id: O4bNwkC1ZxA

Length: 8min 33sec (513 seconds)

Published: Mon Apr 17 2023