If you enjoyed this video consider checking out my 13+ hours Introduction to Database Engineering course here! for more exclusive content husseinnasser.com/courses
the most interesting part was at the end ( just kidding ) . But I really want to know how you can reserve a client and using it for multiple queries and then return it to the pool, that would really suit my project. Awesome tutorial BTW!
Hi Hussein, thanks for your indepth videos. I want to suggest topic discussing Node js architecture like how it works as single threaded event driven runtime and how it differs from javascript running in client (browser). Thanks in Advance 😊
Thanks @vishnu for sharing Yes a stateless query is a query that carries all what the backend needs within it and doesn’t require anything to be in the server. You can restart the server and execute a stateless query and it should give you the correct result
Hussein, can you make same as this video you did but using NodeJS and MariaDB with use of JSON functions and operators it provides. Official Docs for this is terrible. Only you can do proper explanation for these things :P
I'm having the same problem. The issue is that the client sends the database information over to the server. I need multiple different companies that all have their own databases to be able to use a single server without risking security problems. I've got it working right now but it involves creating a new connection before each query. It's very expensive with GCP. I'm trying to figure out pulling but what I don't understand is how the server knows which pool to connect the person to. I've been stuck on trying to solve this problem for about 3 months now.
Awesome tutorial, just one doubt: What happens if there are multiple WRITE requests, and pool allocates one thread to each of the request and they start executing parallely? So how the Atomicity gets handled in that case? Is it DB which handles Atomicity?
so when we using Pool instead of "client", we don't need to close the connection like ""client.end()" ???, or its already auto close connection when we using "pool". ??
Thanks for this video bro. I’m new to data engineering and I’m using AWS redshift and Lambda to make an ETL. Would this connection pooling apply as best practice for my scenario too? The Lambda activates every night at 9pm and process and inserts values from a data frame into redshift tables. Appreciate any response bro. I’ve tried two connection methods; 1.) having a connection established and closed for every query, and 2.) connection established once outside the lambda handler (but still doesn’t use the pool method you showed)
hi, i have an express mysql application running using pm2 . i am using connection pool with limit 10 connection. but after 3-4 days it stop working and require a pm2 restart. how to solve this?
Secondly, a silly example and question. Is it the same if I open a database connection and close it versus configuring a pool of 1 resource and requesting a dedicated client and releasing the client per query?
I don't think so. Because in the first case, you would have to open DB connection for each and every API request. But in the second case, the connection is opened only once and used multiple times
Yes response time is fast because of the eager loading instead of lazy loading. For your question Two things, make sure you are not leaking your connections and correctly returning them to the pool when your done. If you are absolutely sure you are not leaking then you may increase the max clients in your pool. 20 should be ALOT and it could serve alot of requests.
@@hnasr i have an api,in that api i am using knex npm to get the connection.Api had multiple quries select,update, insert and delete,multiple tables are involved.when i gave the frequent request to an api getting connection pool full error.i increased pool size also.sometime table loked.
I'm new to this. For some reason, when I put no password in the new Pool({})/new Client({}), it still connects to the database. How do I password protect my database?
Hello Hussein Sir, This is Mani from India. We are planning to build a Social media app as our product without a chat functionality as of now!. Expecting bit many users as we have a sound sales channel, so in a note of scalability, what database we can go with? If we are going to use Node.js as our server-side language. If you can give a suggestion, We would be extremely grateful. Thanks in advance
Only the execution of your JavaScript is single threaded. Network Requests and other IO are handed off to separate threads so that your main thread can continue working and does not have to wait for those time consuming tasks. Therefore, the main thread might be concurrently waiting on multiple network requests. The concept of the EventLoop will give you more details.
in the documentation they use multi query in which you have to release the clients in the pool or write done() after the query. whats the difference between that and your method which is supposed to be "single" query. I dont get it , with your method if you want to have multiple different queries cant you use multiple functions to call for the queries from the pool?
const { Pool } = require('pg') const pool = new Pool() ;(async () => { const client = await pool.connect() try { const res = await client.query('SELECT * FROM users WHERE id = $1', [1]) console.log(res.rows[0]) } finally { // Make sure to release the client before any error handling, // just in case the error handling itself throws an error. client.release() } })().catch(err => console.log(err.stack))
thanks, great question! It really depends on what you are trying to do. When you say multiple queries Do you mean multiple queries in the same transaction? Of multiple independent transactions with a single query each? Let us assume you want to do multiple queries that is in the same transaction, if you want to do that you have to do this in the same tcp socket (client object) to do that you need to “lease” a client object from the pool, execute your queries with that client object then YES release the client back to the pool With the done () method you specified. So the pool can use it again for other stuff. However if you dont really want to do transactions and you want to execute a single query then you dont have to lease a client from the pool, you can ask the pool to execute your query and it will pick up any client from the pool and execute your query and that is what i was using. This approach is better if you are doing a single stateless queries that are not in the same transaction because the pool will take care of the lease and release of clients for you. However it is not the way to go if you want to execute multiple queries in the same transaction. With first approach you will have to make sure to release the client after you are done. If you forget to do so, then the client is leaked. The second approach the pool manages the client for you Hope that helps!
looks like it says that i have to connect to the pool each time (is it the same as connecting to database each time?) for each get request ? or just executes the unknown function once. Im not sure
@@hnasr Thanks I guess by transaction you mean one single request ? for example a client sends a get request and in that request there will be multiple queries right? I think that makes sense that the client should be lease and released. But is the pool.connect() as expensive as the old school client.connect() ?
If you have multiple different companies which I'll have different database information to the API with each call, how do you make sure that the correct pool connection is returned to the correct end user?
@@hnasr That's what I'm trying to figure out in a sense. Here's the situation. My client (a VueJS Front End) sends over an object dbObject: { host, user, pass, schema } to the server. The server uses that information to connect to the DB and run a query, then return the results. When the user first logs in, the server runs a function named connectToDBase(host, user, pass, schema) - that creates the initial connection and pool. Then every time the user needs to get more data, a new connection is created by the client sending the same dbObject: { host, user, pass, schema } back to the server and repeating the process. Instead of connecting to the already existing pool, it creates a new one. I have 30 different people with 30 different SQL databases using the service. The database info needs to go to the server with each request. It seems that the pool isn't identified by default. I'm trying to figure out how to make sure they keep connecting to the same pool.
Hello Hussein I hope you're well. I have a problem, I'm using docker-compose to spin up a postgres container and I have a backend app written in type script using NestJS as backen framework, all was working well until I changed a statement (I had to drop a table column); from so, I'm getting an error like 'ERROR: column r.cdstatus does not exist at character 75'. Summing up, postgres is executing the old statement. Do you know what I can do? I've tried deleting docker container and the docker volume attached to pgdata, but it is not working.
Jhon Zambrano hey Jhon. Postgres does not execute old statements if you are getting this error that means the app is executing this statement still. Make sure in the app you don’t have a query that still uses this columns. I am not familiar with NestJS. EDIT If you are positive you are not using this column on the app than make sure there aren’t any stored procedures. Run a postgres log and see all queries going to the db
@@hnasr with this in mind and knowing that postgres does not store statements, I figured out that NestJs does not always refresh dist directory. I deleted it and everything works fine, thanks man!
Great video .. Thanks, Hussein. What if I have multiple organizations who access my application and I have different databases for different organizations. My understanding, in that case, is that I need to have some kind of logic to connect to the database based on the url? is there a way to dynamically check whether a new connection is needed before actually connect (i.e. to avoid reaching the maximum connections)? I would appreciate if you can make a video to handle such a case?
Hey Mohammed, great 👍 question , Well, since you are building a stateful application might as well keep track of whether you have connections to a particular database by putting it in an array or having a library like this one manage it for you. For you case I would imagine you would use two connection pools. One for each org, Then based on certain key the client sends, you know which pool to hit. And the pool actually control the maximum number of connections (configurable as i showed in the video) so all of that is surely possible Hope that helps!
![[UNCUT] The Loyal Pin ปิ่นภักดิ์ EP.6 (1/4)](http://i.ytimg.com/vi/m05kZ2GDKXg/mqdefault.jpg)
If you enjoyed this video consider checking out my 13+ hours Introduction to Database Engineering course here! for more exclusive content
husseinnasser.com/courses
God bless you
Hussein! :) Another great video from you.
Holy cow, this channel is insanely good
Wow! I just found the hidden gem(channel) on TH-cam. Can't decide what to watch first. 😅
the most interesting part was at the end ( just kidding ) . But I really want to know how you can reserve a client and using it for multiple queries and then return it to the pool, that would really suit my project. Awesome tutorial BTW!
Amazing
I am new with postgres in node and was confused about pool and client
This video helped me a lot to understand
Thanks Hussein,
Great video, I really like your teaching style - thank you for this!
Thank you for the performance test!
Really great video. I used this as the basis for not using pooling for my particular data processing application. Thanks!:D
Hi Hussein, thanks for your indepth videos. I want to suggest topic discussing Node js architecture like how it works as single threaded event driven runtime and how it differs from javascript running in client (browser).
Thanks in Advance 😊
I discussed it a little bit here
th-cam.com/video/0vFgKr5bjWI/w-d-xo.html
th-cam.com/video/JhpUch6lWMw/w-d-xo.html
Amazing !! Thank you, direct to the point !
Thanks for the video..It was really informative.
Krishna thank you Krishna 🙏
Very helpful video! Thanks
Great video, this was super helpful!
Excellent tutorial. This really helped out. Thank you.
This guy is legend!!
Great Video!!!!
awesome dude .
I like how you stress the app
Very good job sir. Thank you
Solid
Great tutorial.
Nice explanation, specially with fetchAPI.
Thanks @hussein
This video was superb sir!
That was amazing thank you!
awsm.. thanks!!
Hussein, could you please make a video about the "MIME errors" we get sometimes when we try to use the 'pg' require. Ty
If you could show the DB show processlist; (something similar in PostgresSQL) also would be great.
awesome man! Thanks.
Excellent video, Hussein!
Just one question, what do you mean by "stateless query"? is that related to transactions somehow?
th-cam.com/video/nFPzI_Qg3FU/w-d-xo.html
Thanks @vishnu for sharing
Yes a stateless query is a query that carries all what the backend needs within it and doesn’t require anything to be in the server. You can restart the server and execute a stateless query and it should give you the correct result
Thanks
Ty for great videos, but is pool is better and client? What is different pool and client connection? And When we decide to choose one of it?
Hussein, can you make same as this video you did but using NodeJS and MariaDB with use of JSON functions and operators it provides. Official Docs for this is terrible. Only you can do proper explanation for these things :P
I'm having the same problem. The issue is that the client sends the database information over to the server. I need multiple different companies that all have their own databases to be able to use a single server without risking security problems. I've got it working right now but it involves creating a new connection before each query. It's very expensive with GCP. I'm trying to figure out pulling but what I don't understand is how the server knows which pool to connect the person to. I've been stuck on trying to solve this problem for about 3 months now.
Awesome tutorial, just one doubt: What happens if there are multiple WRITE requests, and pool allocates one thread to each of the request and they start executing parallely? So how the Atomicity gets handled in that case? Is it DB which handles Atomicity?
Got the answer in this one : th-cam.com/video/_95dCYv2Xv4/w-d-xo.html , thanks a lot.
so when we using Pool instead of "client", we don't need to close the connection like ""client.end()" ???, or its already auto close connection when we using "pool". ??
You take love
if you set the idleTimoutMillis to 0, when does the pool connection resource die? When the application shuts down?
Cool, so .. what should be the best number of connections in a pool?
You just earned yourself a s-s-subscribe. Boo yeaaahuhs 💞♥️😛😤🥺
Yohooo thank you 🙏
Thanks for this video bro. I’m new to data engineering and I’m using AWS redshift and Lambda to make an ETL. Would this connection pooling apply as best practice for my scenario too? The Lambda activates every night at 9pm and process and inserts values from a data frame into redshift tables. Appreciate any response bro. I’ve tried two connection methods; 1.) having a connection established and closed for every query, and 2.) connection established once outside the lambda handler (but still doesn’t use the pool method you showed)
hi, i have an express mysql application running using pm2 . i am using connection pool with limit 10 connection. but after 3-4 days it stop working and require a pm2 restart.
how to solve this?
Can we apply this pooling approach in warm state of aws Lambda.. what will be the bottleneck scenario in that case?
Secondly, a silly example and question.
Is it the same if I open a database connection and close it versus configuring a pool of 1 resource and requesting a dedicated client and releasing the client per query?
I don't think so. Because in the first case, you would have to open DB connection for each and every API request. But in the second case, the connection is opened only once and used multiple times
That was amazing.Speed of api response is high.But sometimes i am getting"The pool is probably full".How to fix it?.
Yes response time is fast because of the eager loading instead of lazy loading. For your question Two things, make sure you are not leaking your connections and correctly returning them to the pool when your done. If you are absolutely sure you are not leaking then you may increase the max clients in your pool. 20 should be ALOT and it could serve alot of requests.
@@hnasr i have an api,in that api i am using knex npm to get the connection.Api had multiple quries select,update, insert and delete,multiple tables are involved.when i gave the frequent request to an api getting connection pool full error.i increased pool size also.sometime table loked.
I'm new to this. For some reason, when I put no password in the new Pool({})/new Client({}), it still connects to the database. How do I password protect my database?
Love the hands on and the ability to see it in action in real time without reading from the holy bibles (official documentations)
Is 'pg' a better library to use than 'pg-promise'?
Hello Hussein Sir, This is Mani from India. We are planning to build a Social media app as our product without a chat functionality as of now!. Expecting bit many users as we have a sound sales channel, so in a note of scalability, what database we can go with? If we are going to use Node.js as our server-side language. If you can give a suggestion, We would be extremely grateful. Thanks in advance
why postgresql for node and not mysql though?
Plz explain connection pooling in django
Can we do Connection pooling for DynamoDB ?
@Hussein Nasser Is the connection pool needed when NodeJS is supposedly Single Threaded ? How does this actually help in increasing performance ?
Only the execution of your JavaScript is single threaded. Network Requests and other IO are handed off to separate threads so that your main thread can continue working and does not have to wait for those time consuming tasks. Therefore, the main thread might be concurrently waiting on multiple network requests. The concept of the EventLoop will give you more details.
Can you give example with Oracle & php
Can you share us how to use pgBouncer ?
in the documentation they use multi query in which you have to release the clients in the pool or write done() after the query. whats the difference between that and your method which is supposed to be "single" query. I dont get it , with your method if you want to have multiple different queries cant you use multiple functions to call for the queries from the pool?
const { Pool } = require('pg')
const pool = new Pool()
;(async () => {
const client = await pool.connect()
try {
const res = await client.query('SELECT * FROM users WHERE id = $1', [1])
console.log(res.rows[0])
} finally {
// Make sure to release the client before any error handling,
// just in case the error handling itself throws an error.
client.release()
}
})().catch(err => console.log(err.stack))
thanks, great question!
It really depends on what you are trying to do. When you say multiple queries Do you mean multiple queries in the same transaction? Of multiple independent transactions with a single query each?
Let us assume you want to do multiple queries that is in the same transaction, if you want to do that you have to do this in the same tcp socket (client object) to do that you need to “lease” a client object from the pool, execute your queries with that client object then YES release the client back to the pool With the done () method you specified. So the pool can use it again for other stuff.
However if you dont really want to do transactions and you want to execute a single query then you dont have to lease a client from the pool, you can ask the pool to execute your query and it will pick up any client from the pool and execute your query and that is what i was using. This approach is better if you are doing a single stateless queries that are not in the same transaction because the pool will take care of the lease and release of clients for you. However it is not the way to go if you want to execute multiple queries in the same transaction.
With first approach you will have to make sure to release the client after you are done. If you forget to do so, then the client is leaked.
The second approach the pool manages the client for you
Hope that helps!
looks like it says that i have to connect to the pool each time (is it the same as connecting to database each time?) for each get request ? or just executes the unknown function once. Im not sure
@@hnasr Thanks I guess by transaction you mean one single request ? for example a client sends a get request and in that request there will be multiple queries right? I think that makes sense that the client should be lease and released. But is the pool.connect() as expensive as the old school client.connect() ?
FxStreamer search for SQL transaction : a set of queries that you can confirm(commit) if everything went ok or discard (rollback) if something went ko
If you have multiple different companies which I'll have different database information to the API with each call, how do you make sure that the correct pool connection is returned to the correct end user?
I am going to assume your API will have some sort of input which we can key off and get to the desired database
@@hnasr That's what I'm trying to figure out in a sense. Here's the situation. My client (a VueJS Front End) sends over an object
dbObject: { host, user, pass, schema }
to the server. The server uses that information to connect to the DB and run a query, then return the results.
When the user first logs in, the server runs a function named connectToDBase(host, user, pass, schema) - that creates the initial connection and pool. Then every time the user needs to get more data, a new connection is created by the client sending the same dbObject: { host, user, pass, schema } back to the server and repeating the process.
Instead of connecting to the already existing pool, it creates a new one.
I have 30 different people with 30 different SQL databases using the service. The database info needs to go to the server with each request. It seems that the pool isn't identified by default. I'm trying to figure out how to make sure they keep connecting to the same pool.
Hello Hussein I hope you're well. I have a problem, I'm using docker-compose to spin up a postgres container and I have a backend app written in type script using NestJS as backen framework, all was working well until I changed a statement (I had to drop a table column); from so, I'm getting an error like 'ERROR: column r.cdstatus does not exist at character 75'. Summing up, postgres is executing the old statement.
Do you know what I can do? I've tried deleting docker container and the docker volume attached to pgdata, but it is not working.
Jhon Zambrano hey Jhon.
Postgres does not execute old statements if you are getting this error that means the app is executing this statement still.
Make sure in the app you don’t have a query that still uses this columns. I am not familiar with NestJS.
EDIT
If you are positive you are not using this column on the app than make sure there aren’t any stored procedures.
Run a postgres log and see all queries going to the db
@@hnasr with this in mind and knowing that postgres does not store statements, I figured out that NestJs does not always refresh dist directory.
I deleted it and everything works fine, thanks man!
Thanks for sharing this!
Great video .. Thanks, Hussein.
What if I have multiple organizations who access my application and I have different databases for different organizations.
My understanding, in that case, is that I need to have some kind of logic to connect to the database based on the url? is there a way to dynamically check whether a new connection is needed before actually connect (i.e. to avoid reaching the maximum connections)? I would appreciate if you can make a video to handle such a case?
Hey Mohammed, great 👍 question ,
Well, since you are building a stateful application might as well keep track of whether you have connections to a particular database by putting it in an array or having a library like this one manage it for you.
For you case I would imagine you would use two connection pools. One for each org, Then based on certain key the client sends, you know which pool to hit. And the pool actually control the maximum number of connections (configurable as i showed in the video) so all of that is surely possible
Hope that helps!
3:06 user and password: "postgres"
Me: ik this is an example lol