Theo's Typesafe Cult•16mo ago

should i use a pool or connection in mysql

This is my first time going raw on sql. i usually use an orm(prisma) So, in this application; it's an express application I am using

mysql2

mysql2

package to do my queries This application is read intensive, possibly like hundreds or more read per second Should i use a

mysql.createConnetion

mysql.createConnetion

mysql.createPool

mysql.createPool

Also, note that i will inject the connection into express

app.use((req,res,next)=>
{
req.db = Dbconnection
})

app.use((req,res,next)=>
{
req.db = Dbconnection
})

Also, do i need to await the db? Because it's going to create more complexity

17 Replies

dan•16mo ago

pool if you need the data back from the db you'll have to await it, if its just inserting should be fine to not

LopenOP•16mo ago

So i will have to release the pool per request? Isn't that a bottle neck? So if i have 100 req/sec i could have 100 pools per second? Also i was able to insert and select the datas fine without await

dan•16mo ago

you relase the connection back into the pool. you dont have to keep closing and reopening connections. The pool is just a set of open connections. you probably wont need 100 concurrent connections open, I generally start of with 10 (at least back when I raw dogged mysql2) but I would play around with the connection limit

LopenOP•16mo ago

How will i know when it's time to add more?

dan•16mo ago

when requests start taking longer you can try increase it and see if it helps if not you'd have to do more performance debugging

LopenOP•16mo ago

Can't i just start at 100?

dan•16mo ago

yea nothing stopping you, I just suggested what I generally do just play around with it and see what works

Andrea Peverelli•16mo ago

you have to use pools to maximize ur thread connections, when they're ended u simply release the connections https://blog.devgenius.io/nodejs-how-to-close-your-mysql-connections-and-why-a7cc7287132b

Medium

NodeJS — how to close your MySQL connections and why

It’s very important to keep track of your database connections in order to not fill up the maximum number of connections. If you are…

theart4290•16mo ago

as mentioned above, use a connection pool, but make sure you close unused connections (prisma does it for you btw). If you initialise a separate connection for each request, it will makes things not only painfully slow, but also might crash your db

LopenOP•16mo ago

Now to the next question Must i use await? Because if i use await then no way to inject the connection into request

theart4290•16mo ago

Have a look at the docs

async function main() {
  // get the client
  const mysql = require('mysql2');
  // create the pool
  const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
  // now get a Promise wrapped instance of that pool
  const promisePool = pool.promise();
  // query database using promises
  const [rows,fields] = await promisePool.query("SELECT 1");
}

async function main() {
  // get the client
  const mysql = require('mysql2');
  // create the pool
  const pool = mysql.createPool({host:'localhost', user: 'root', database: 'test'});
  // now get a Promise wrapped instance of that pool
  const promisePool = pool.promise();
  // query database using promises
  const [rows,fields] = await promisePool.query("SELECT 1");
}

https://www.npmjs.com/package/mysql2

LopenOP•16mo ago

Is this better?

const connection = await pool.getConnection()
const res = await connection.query("select * from users")
connection.release()

const connection = await pool.getConnection()
const res = await connection.query("select * from users")
connection.release()

theart4290•16mo ago

lgtm, give it a go

LopenOP•16mo ago

Yes it works but just asking because it's my first time

Keef•16mo ago

Take a look at trying to adopt RAII and writing some kind of helper that will help avoid the mistake of forgetting to release the connection Lots of ways to do this, in go I just do a withConn and that’ll run the func you pass on then clean up. The not releasing connections can be a reallllly really fun thing to deal with when you notice your db connections constantly hitting the limits of its compute

Keef•16mo ago

https://www.totaltypescript.com/typescript-5-2-new-keyword-using Also to be mentioned

Total TypeScript

TypeScript 5.2's New Keyword: 'using'

TypeScript 5.2 introduces 'using', a keyword that disposes anything with a Symbol.dispose function upon leaving scope, making resource management easier.

LopenOP•16mo ago

Fun fact everything is in javascript the company lacks the expertise to maintain a typescript codebase So i had to use Javascript And more funny everything they had prior was using require So i had to forceful migrate them to use import on this one I abstracted all db calls into a function So no external calls Something like this

 const Getusers = async () => {
    connection = await db.getConnection()
    const [result] = await connection.query(...)
    connection.release()
    return results
}

 const Getusers = async () => {
    connection = await db.getConnection()
    const [result] = await connection.query(...)
    connection.release()
    return results
}

Gaming

Programming

should i use a pool or connection in mysql