DB Interactions: How Applications Talk to DB

Hello, I'm Maneshwar. I'm working on FreeDevTools online currently building **one place for all dev tools, cheat codes, and TLDRs* — a free, open-source hub where developers can quickly find and use tools without any hassle of searching all over the internet.*

In the previous post, we focused on transactions, the abstraction that allows databases to remain correct in the presence of concurrency and failures.

We saw how the DBMS guarantees atomicity, isolation, and durability while applications define what a transaction does.

But that raises a practical question:

How do applications actually talk to a DBMS, and how do they express what they want done?

Modern database systems are not accessed by manipulating files or blocks.
Instead, applications interact with a DBMS through well-defined interaction models and languages.

Understanding this boundary is critical, because it determines performance, isolation, scalability, and even failure behavior.

Two Aspects of Database Interaction

Interaction with a DBMS has two distinct dimensions:

1. The model of interaction How applications connect to and exchange information with the DBMS.
2. The language of interaction How applications define, query, and modify data through the DBMS.

Both choices shape how databases are built and used in real systems.

Models of Interaction with a DBMS

A DBMS typically operates in one of two interaction models:

• Embedded model
• Client–server model

Each reflects different system constraints and design goals.

Embedded DBMS Model

In the embedded model, the DBMS is implemented as a user-level library that becomes part of the application at runtime.

Here:

• The DBMS code runs inside the application’s process
• Applications directly execute DBMS functions
• Communication happens through ordinary function calls
• All execution occurs within a single process address space

This is effectively a self-service database system.

Applications interact with the DBMS via APIs, just like they would with any other library.

Some embedded systems even allow applications to register callback functions.

These callbacks are invoked by the DBMS when it needs to manipulate data inside the application’s memory space.

Characteristics of Embedded DBMSs

• No separate server process
• Minimal overhead
• Low memory footprint
• Tight coupling between application and DBMS
• Limited concurrency and scalability

Embedded DBMSs are commonly used in:

• Mobile devices
• Embedded systems
• Resource-constrained environments

In these contexts, running a separate database server is unnecessary or impractical.

Client–Server DBMS Model

In the client–server model, the DBMS runs as a separate server process.

Applications act as clients:

• They do not execute DBMS code directly
• They contain only a lightweight DBMS driver
• They communicate with the DBMS server via interprocess communication (IPC)

Communication typically uses:

• TCP/IP sockets
• Shared memory
• Other OS-supported IPC mechanisms

Clients send requests, the server executes them, and the results are sent back as responses.

Unlike the embedded model:

• There are no callbacks into application code
• The DBMS is isolated from application bugs
• The server manages concurrency and transactions centrally

Most commercial and production-grade DBMSs use this model and are often called database servers.

Why Client–Server Dominates

• Strong isolation between applications and DBMS
• High concurrency support
• Centralized transaction management
• Better fault containment
• Scales across machines and networks

This model keeps application address spaces small and prevents application crashes from corrupting database internals.

Language of Interaction: How Work Is Expressed

Connecting to a DBMS is only half the story. Applications must also describe what they want the database to do.

There are two broad approaches:

Procedural (Prescriptive)

In procedural systems:

• Users specify how each step should be executed
• The DBMS is instructed at a low level
• Control flow and execution order are explicit

This approach tightly couples applications to execution strategies and storage details.

Declarative (Descriptive)

Modern DBMSs overwhelmingly use declarative languages.

In declarative systems:

• Users specify what they want
• The DBMS decides how to execute it
• Execution strategies are hidden from applications

This separation is deliberate and powerful.

SQL: The Language of Relational Databases

The most widely used declarative language in relational databases is Structured Query Language (SQL).

SQL allows applications to:

• Describe desired results
• Ignore storage layouts
• Avoid reasoning about indexes, blocks, or access paths

Each SQL statement:

• Is a standalone program or subprogram
• Specifies what to compute, not how to compute it

This division of responsibility is crucial:

• Application developers focus on correctness and intent
• DBMS developers focus on optimization and execution

SQL Components

SQL defines:

• A standard set of primitive data types (integer, varchar, date, timestamp, blob, etc.)
• User-defined domains composed of these primitives
• Syntax and semantics for interacting with relational data

SQL statements fall into two major categories:

Data Definition Language (DDL)

Used to define database structure:

• Tables
• Indexes
• Constraints
• Domains
• Views
• Triggers

DDL shapes the schema and integrity rules of the database.

Data Manipulation Language (DML)

Used to work with data:

• Query rows
• Insert new rows
• Update existing rows
• Delete rows

DML statements are executed inside transactions and are subject to ACID guarantees.

👉 Check out: FreeDevTools

Any feedback or contributors are welcome!

It’s online, open-source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools