Database connection management: How to make your app 2 times faster?13 min read

One in three applications/websites is suffering from poor database connection management. The result is frequent downtime and a slow application. This combined can cost millions of dollars in lost revenue per year.

I have worked as an engineer for at least 15 different web applications of large enterprises. While being a technical consultant at my present job, I have seen tens of additional applications (some of them are global-scale mobile apps/websites).

Based on this experience, I can tell that one in three applications is losing money due to inadequate (or non-existent) database connection management.

Some apps weigh billions of dollars but fail to handle database connections optimally. (I am confident that the problem is even more common for smaller-scale apps).

In this post, you will learn how to handle database connections optimally. With this knowledge, you will be one step ahead of all other engineers.

Once applied, your or your employer’s app will stop losing money while improving the user experience at the same time.

When do you use database connections?

Most modern web apps use the three-tier architecture while designing the application. You must have seen this application design if you are in the engineering field already.

In a typical three-tier application, you would have the frontend tier, the backend tier, and the database tier.

Whenever a user of the app sends a request, the request travels from the frontend to the backend (also called the middle-tier), where the backend communicates with the database.

So, your app (the middle-tier) uses the database connections to connect to the database and fulfill the user’s requests.

It can be that the application is either reading or writing to the database, which happens after establishing a connection with the database.

The architecture of 90% of the apps running on your mobile looks most probably like this.

This is also the setup I will use to demonstrate the optimal way of handling the database connections for a hypothetical chatroom project.

The problem of not managing the database connections (well)

If you do not manage the database connections properly (or at all), you might end up sending each command to the database separately.

This means that before the command is sent, your app code must authenticate (sort of a login) to the database. (DB does not let everyone read/write from it.)

This authentication process takes time, and if you send 100k read commands to the database, you will implicitly send 100k authentication commands as well (because your app must authenticate — provide a database password — before it can read/write to the database).

In reality, you do not have to send 100k authentication commands; one is sufficient. Here you can imagine how much computing power (and costs) are saved if you manage the connections well.

Now, if you do use the persistent connection and strip out those unnecessary authentication commands, your application will work much faster (based on my experience, up to 2 times faster).

Note: the authentication process is a very expensive one in terms of time. The app calls the server (that can be on the other side of the planet) to present the login credentials, and the server takes those and returns an OK to the application (if the credentials are right). Only after this round-trip can the app actually send the command in question to the database.

Chatroom project

The setup of this chat app is relatively simple. Of course, it is just a skeleton code with no security measures, contributing to its simplicity.

You can use the code I paste here to try and “feel” how persistent database connection works. If you haven’t heard of this term, here is what that means.

Your middle tier opens one connection with the database (this connection never stops). Because of that, we call it the persistent database connection.

Your customers use the frontend (user interface) to request what they desire from the backend. Still, the backend (middle tier) organizes user requests and forwards those to the database. All of that is done by reusing the already open single database connection.

Persistent code example

A decade ago, when starting my career, it was challenging to understand what persistent database connection management meant.

I bet that there are people who are now in my shoes, so let’s try to explain this by making it even simpler.

Below is a small Python code that continues forever. In other words, the program executes until you stop it manually or until the power goes off. (Python is a very common backend (middle-tier) language.)

def run_forever():
    while 1==1:
        print("I am running")

run_forever()Code language: PHP (php)

The code returns this until infinity.

I am running
I am running
I am running
I am running
....

Test the code here but do not forget to Stop it, or else you will burn someone’s computer.

What I have just shown you here is a program that never ends. The condition while 1==1 ensures that as long as 1 is equal to 1, the code keeps executing (one is equal to one, and it will be so forever). Therefore, this program continues until infinity.

Now imagine that you have the database connection line somewhere in your Python program (the program that never ends).

By doing that, you open a persistent database connection that lasts forever.

Chatroom project tiers

The GIT repository that contains all the code is here

I have used exactly the same logic in the chatroom project to maintain the persistent database connection. The app is a book example of three-tier architecture:

Frontend (user interface) is served via the browser with the help of JavaScript and HTML.

BACKEND:

Middle-tier is Python code.

The database is Redis Cloud Database.

In both frontend and middle tier, I used Socket.IO so that Python code could “listen” to the changes happening in the browser. Also, my Python code uses the Aiohttp package to serve as a web server that powers the browser user interface.

Click here if you want to start learning Python today

Frontend code

This code runs in the user’s browser and shows the welcome message. Users also sign up for the chatroom by using this page. Here is the code (plain JS + HTML).

<!-- http://0.0.0.0:8080/frontend-app -->
<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <meta http-equiv="X-UA-Compatible" content="ie=edge" />
    <title>ChatRoom</title>
  </head>
  <body>
    <h2>MyCoolChat.Com</h2>
    <h3 id="greeting">Hi!</h3>
    <input id="name" value="Write your name to join"><button onClick="getName()">Set name</button>
    <br/><br/>
    <button onClick="sendMsg()">Join now</button>

    <br/><br/>
    <div id="joined"></div>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/socket.io/2.2.0/socket.io.js"></script>
    <script>
      const socket = io.connect("http://0.0.0.0:8080",{
      cors:{
          origin:'*',
          credentials:true
      }
      });

      function getName() {
        var name = document.getElementById("name").value;
        document.getElementById("greeting").innerHTML = "Hi, " + '<span id="nameset">'  + name + '</span>!';
        }
    

      function sendMsg() {
        var name = document.getElementById("nameset").innerHTML
        socket.emit("newUser", name);
        document.getElementById("joined").innerHTML = "Success! You have just joined the chat."
      }
    </script>
  </body>
</html>Code language: HTML, XML (xml)

Once the app server runs, this is how the welcome page looks like in the browser (I also entered my full name).

The “connection” between the user interface and the backend code is via the sendMsg() function that uses Socket.IO to broadcast the changes from the user interface to the middle tier (Python).

Once triggered on the “Join Now” button click, the function goes to socket.emit("newUser", name); which will make the middle tier aware of who is joining the chat.

Backend code

The backend is written in Python. The two main packages that make this integration possible are aiohttp and socketio.

The first serves the static webpage in the browser, while the second is used to “listen” to the changes happening in the user interface.

The Python backend code also connects to the Redis Cloud database.

Here is the central part of the backend code that powers the chat (the complete code you can find in this Git repo so that you can run it on your computer).

from aiohttp import web
import socketio
import utils
#Access to XMLHttpRequest at 'http://localhost:8080/socket.io/?EIO=3&transport=polling&t=OPklIs9' from origin 'http://0.0.0.0:8080' has been blocked by CORS policy: The request client is not a secure context and the resource is in more-private address space `local`.


## a new SocketIO server that serves as an event-based messaging system between the server (Python) and the UI (web-browser)
sio = socketio.AsyncServer(cors_allowed_origins=['http://0.0.0.0:8080', 'http://localhost:8080'])


## Aiohttp creates a new (http) web app server with the give routes
app = web.Application()

# Connects the SocketIO server to our Web App http server
sio.attach(app)

# Initialize connection to the Redis Database
utils.init_redis()

## An aiohttp HTTP endpoint is defined as this
async def chat(request):
    with open('frontend-app.html') as f:
        return web.Response(text=f.read(), content_type='text/html')

## Event coming from the frontend that the backend is listening for
@sio.on('newUser')
async def print_message(sid, message):
    print("_______________")
    print("Another user has just joined...")
    print("Socket ID: " , sid)
    print("Name: " + message)

    # Interact with the open connection of Redis DB
    # add the current user to the set of online users
    utils.redis_client.sadd("who_is_online", message)
    print("Currently online:")
    print(utils.redis_client.smembers("who_is_online"))


## This is the route definition
# We bind our aiohttp endpoint to our SocketIO app
app.router.add_get('/frontend-app', chat)

## We kick off our server
if __name__ == '__main__':
    web.run_app(app)Code language: PHP (php)

Once you run the backend.py file, the app will be accessible from your browser at the below address. (Note that you must have all the files from the repository to run the code.)

http://0.0.0.0:8080/frontend-app

As in the earlier example, this Python code continues forever due to the libraries used in it (the logic is exactly the same).

The line utils.init_redis() initiates the connection with the database, and since the program continues to execute until infinity, the database connection is persistent.

Just one database connection is open, and all the chat users will read and write to the database through that tunnel.

Python listens to the frontend changes with @sio.on('newUser'). Here, the user’s name is captured and forwarded to the part of the code that writes the user who joined the chat to the list of online users (stored in the Redis Cloud database).

Database

I used a free Redis Cloud database for this example. You can register and get a free Redis Cloud database for this example (or for your other projects).

Disclosure: I work at Redis, but even if I did not, I would use Redis Cloud for this example since it takes 3 minutes to get started, and it is extremely simple.

Check out my post on common Redis errors and how to migrate to Redis Cloud without downtime.

Results

Once you run the backend.py file, you can access the user interface in your browser (http://0.0.0.0:8080/frontend-app).

This is for demo purposes only, and the app only runs on your computer. This means that no one else can access it. In a more realistic environment, the app would run on a domain name called mycoolchat.com, and many users could access it at the same time.

To try to imitate this, I will open several browser tabs and enter different names in every chat to join multiple users in the chatroom.

Here I am joining my first user, “Peter.”

The other two users are also coming shortly.

Here is the overview of the backend code that tracks the users and reports what has been read and written to the databases.

All three users reach the application independently from their browsers and all that through the single (persistent) database connection.

Let’s look at the Redis Dashboard Metrics that show the open connections.

As you can see, the reported number of connections is 1. This demonstrates that all the interactions with the databases happened via this single open database connection.

Summary

In this post, I wanted to explain the topic of database connection management. Over the years, I have seen multi-million dollar apps having poor database management and losing millions of dollars in revenue due to the slow app.

With the demo code, I showed that multiple users could connect to the same database and read/write from it using the persistent database connection. (Rather than connecting to the database for each command individually.)

Based on earlier experience, applications that implement similar solutions do experience a speed increase in their app up to two times.

Using this logic will save a lot of computing resources (the price you pay goes down), speed up the application, and educate your team members to use the more future-proof (and efficient) methods.

Note: The code here lacks many security features, and it is only for demo purposes. If you decide to use some parts of it in your app, make sure that you implement the proper security measures and optimize the code further.