In this article we will learn about Database Connections and their lifecycle. Next we will learn Connection Pools and their lifecycle and why we need to use them.
What is Database Connection?
Any software application needs to store data in the Database and in order for the application to interact with a database server, we need a Database Connection. Database Connection is a way for the application to interact with the DB and we use the connection to send commands (SQL) to the DB and get the results returned from the DB.
DB application always runs on a dedicated server called Database server, it is different from application server. The database application runs on a port on the Database Server. On it the application server can send commands (SQL) and receive data.
Whenever Client (browser or mobile app) requests data from Backend Application. Backend Application will tell Database to return data and Backend to return response to Client. If the Backend Application wants to connect to DB Server, it needs to make a call based on TCP-IP protocol with DB Server IP and Port and credentials (DB’s username, password). The process of Application Server connecting to the DB Server to get data is achieved through a mechanism called Database Connection.
Once connection to Database is created, it can open and close at any time and we can also set timeout for them (connection).
If there is no connection, communication with the database cannot be performed. Creating a Database Connection is an expensive operation.
Lifecycle of a Database Connection
We can see the details of Database Connection in the image below.
- Open connection to database using Connection String
- Authenticating before establishing connection
- Initialize and open a TCP socket for reading and writing data
- Send and receive data on socket socket
- Close database connection
- Close TCP socket
The process of creating a connection to the Database is too many steps and is an expensive and time consuming operation. If your application grows in users and you open a connection every request, the number of connections created simultaneously at a time increases and it will increase CPU, Ram, Database can hang, which is very dangerous.
That’s why we use Connection Pool by not creating connection every request and reusing existing connection. If no connection exists in the Pool, a new one will be created.
What is Database Connection Pool?
Connection Pool is a method to increase system performance for connection initialization problem. Connection Pool is the Pool of Database Connection, it can be created before the application is launched and share the Connection when the Application needs to access the Database.
How is Database Connection reused from Connection Pool?
The figure below details how a connection is reused.
Why need Connection Pool?
- Database Connections are initialized at an expensive cost, so instead of creating them at every request, we will initialize them first and call whenever we need to access the Database.
- Database is a shared resource, so it makes sense to create a pool of connections and share them across all transactions.
- The Datatabase Connection Pool limits the amount of access to the Database at a time to minimize the Database Server crashing.