DB Interactions: How Applications Talk to DB

Source: Dev.to

Hello, I’m Maneshwar. I’m working on FreeDevTools online — a free, open‑source hub where developers can quickly find and use dev tools, cheat codes, and TLDRs without the 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

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.

Characteristics

• 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, which the DBMS invokes when it needs to manipulate data inside the application’s memory space.

Benefits and Limitations

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

Typical Use‑Cases

• 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 inter‑process communication (IPC).

Common IPC Mechanisms

• 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.

Differences from the Embedded Model

• 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)

• 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.

• 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 lets:

• 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.

Resources

👉 Check out: FreeDevTools

Any feedback or contributions are welcome! It’s online, open‑source, and ready for anyone to use.

⭐ Star it on GitHub: freedevtools