Demystifying DB-API

DB-API is an acronym of DataBase Application Programming Interface and a library which lets python connect to the database server. Depending on which relational DB library you use, they have their own DB-API modules.
Similar to the Web APIs we mostly deal with as developers, it is a computing interface specifically for databases between server-side and database and it enables us to communicate with a database using certain protocols such as TCP/IP.

When we work on a client-side web application, we often pull the data from web APIs to display them to end-users and if end-users modify/upload existing/new data, we need to make sure the CRUD operation is invoked accordingly. This client-server model is applied to many modern-day systems to interact with servers. Simply put, databases are the same, interacted with using client-server interaction over a network. When an end-user makes a request, a browser will do the same to the web server and at that point, the web server becomes a client which makes a request to the database which acts as a server to fulfil the request.

When we talk about the data and transferring data over the network, the two main protocols, TCP and IP are involved. They use IP address and Ports number. Since they are connection-based protocols, we always have to establish a connection from DB-API to database server over TCP/IP. In other words, we need to explicitly start a session for the connection and end the connection for a session.

# DB-API for PostgreSQL 
import psycopg2 

connection = psycopg2.connect('dbname=test')
......
connection.close()

In each database session, there are many transactions that can occur. The session enables us to control each transaction like git. Just imagine you made your code change for your task on a new branch and add the branch before committing and pushing it to the stage. But, if a senior developer found a bug, you may end up reverting it so to speak.

transaction.add('CREATE TABLE coffee (
    id INTEGER PRIMARY KEY,
    item STRING NOT NULL') ;
)
transaction.add('''
    INSERT INTO coffee (id, item) VALUES (%(id)s, %(item)s);',
    { 'id': 1, 'item': 'Cafe Latte' }
''')

transaction.commit()
transaction.rollback()

If you are familiar with git command lines, as I explained above, this may help you reach enlightenment for the database transaction. It's intuitive and as the database sometimes can fail, we can roll back to a previous point before the change was made.

Each transaction is an atomic unit of work to access the database and it lets us read and write data. There are 4 characteristics to make the database more maintainable and reliable: Atomicity, Consistency, Isolation and Durability. Just like git, each transaction of database operation should meet these properties in order to prevent any errors from concurrent executions, power failure and so on.

I hope my explanation for DB-API helps you to understand not only the DB-API but also, the general idea of databases and APIs as well. Thank you for reading and I hope you enjoy learning web development!

Photo by Markus Spiske on Unsplash