Database APIs

Definition

A Database API is a standardized set of functions/interfaces — such as ODBC, JDBC, ADO.NET, or Python's DB-API (e.g., via psycopg2) — that application code calls to interact with a database: connecting, executing queries, fetching results, and managing transactions, all without the application needing to know the vendor's native wire protocol directly.

How It Works — One Stable Contract, Many Backends Underneath

Using Python's DB-API, the same basic pattern works regardless of backend:

python
conn = connect(...)
cur = conn.cursor()
cur.execute("SELECT * FROM Student")
rows = cur.fetchall()

This exact connect() → cursor() → execute() → fetchall() pattern works whether the underlying driver connects to PostgreSQL, MySQL, or SQLite. The API is the stable, standardized contract; the driver (2.13) underneath does the actual vendor-specific protocol translation.

How the Full Stack Fits Together

Putting 2.11–2.14 together as one layered stack:

Application code → Database API (standard functions) → Driver (vendor-specific translator, implements the API) → optionally routed/pooled by Middleware → the actual Database Server.

The API standardizes what you call (execute, fetchall, etc.); the driver implements how that gets translated for a specific vendor; middleware and connection pooling manage when, and through which shared connection, those calls actually reach the database.

Edge Cases and Pitfalls

• False sense of full portability: relying on a vendor-specific SQL extension or function inside the SQL string passed to a standard API call still breaks portability, even though the API call itself (execute(sql)) is completely standard — the API being portable does not make arbitrary SQL text portable.
• Resource leaks: not properly closing cursors/connections obtained through the API leaks server-side resources over time — this is exactly the kind of problem connection pooling (2.12) is designed to mitigate, by managing and reclaiming connections centrally rather than leaving it entirely to the API caller's discipline.

Interview Takeaways

• Q: If an app uses a standard database API, does that guarantee it's portable across database vendors? No — the API calls are portable, but any vendor-specific SQL syntax or functions embedded inside the query strings themselves are not; true portability requires both a standard API and portable SQL.
• Q: In the layered stack, what is the difference between what the API controls versus what the driver controls? The API standardizes which functions an application calls; the driver determines how those calls are actually translated into one specific vendor's wire protocol.