8. Database Interfaces

Database Interfaces

Modern applications rarely talk to a database through raw SQL typed manually in a terminal. Instead, they use standardized database interfaces — libraries and drivers that allow programs written in Python, JavaScript, Java, PHP, and many other languages to communicate with a RDBMS in a structured, secure, and portable way.

This chapter covers the most important interface concepts and shows concrete examples of connecting to and querying PostgreSQL and MariaDB / MySQL from popular programming languages.

1. Overview of Database Interface Standards

1.1 Why Standardization?

Without standards, every database vendor would require its own proprietary API. A Python application talking to PostgreSQL would look completely different from one talking to MariaDB / MySQL or Oracle. Standards solve this:

• ODBC (Open Database Connectivity) — a C-level API standard, widely supported across languages and platforms. Provides a common interface so that one application can switch between different database backends with minimal code changes.
• JDBC (Java Database Connectivity) — the Java equivalent of ODBC. Every major RDBMS provides a JDBC driver.
• Native drivers — language-specific libraries that communicate with a specific DBMS directly (e.g., psycopg2 for Python + PostgreSQL, pg for Node.js + PostgreSQL). Often faster and more feature-rich than generic ODBC/JDBC wrappers.
• ORMs (Object-Relational Mappers) — higher-level abstractions that map database rows to programming language objects (e.g., SQLAlchemy for Python, Prisma for Node.js). Useful for rapid development, but can hide important SQL behavior.

ODBC vs. native drivers

ODBC is the right choice when you need database portability (e.g., supporting PostgreSQL and SQL Server from the same application) or when connecting from tools like Excel or Power BI. Native drivers are preferred when you work exclusively with one DBMS and want the best performance and access to database-specific features.

1.2 How a Database Connection Works

Regardless of language or driver, the general flow is always the same:

1. Load the driver — the application loads the database driver library.
2. Establish a connection — the driver connects to the DBMS using a connection string (host, port, database name, username, password).
3. Open a cursor / session — a logical handle for sending queries and receiving results.
4. Execute queries — SQL statements are sent to the database.
5. Process results — result rows are fetched and used in the application.
6. Close the connection — the connection is released back to a pool or closed.

Application
    │
    ▼
 Driver / Connector (e.g. psycopg2, pg, JDBC driver)
    │
    ▼
 Network (TCP/IP, Unix socket)
    │
    ▼
 PostgreSQL Server (port 5432)
    │
    ▼
 Database / Schema / Tables

1.3 Connection Strings

Most drivers accept a connection string (also called a DSN — Data Source Name) that encodes all connection parameters:

postgresql://username:password@host:port/database

Example:

postgresql://app_user:secret@localhost:5432/school_db

Never hardcode credentials

Connection strings contain sensitive credentials. Never commit them to version control. Use environment variables, .env files (excluded from git), or a secrets manager instead.

2. Connecting from Node.js

Node.js applications commonly use pg for PostgreSQL and mysql2 for MariaDB / MySQL.

2.1 Installation

PostgreSQL

npm install pg

MariaDB / MySQL

npm install mysql2

2.2 Basic Query

PostgreSQL

import pg from 'pg';
const { Pool } = pg;

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

async function getActiveStudents() {
  const result = await pool.query(
    'SELECT student_id, name FROM students WHERE is_active = $1 ORDER BY name',
    [true]   // parameterized - $1 is the placeholder
  );
  return result.rows;
}

getActiveStudents().then(console.log);

MariaDB / MySQL

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT || 3306,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
});

async function getActiveStudents() {
  const [rows] = await pool.execute(
    'SELECT student_id, name FROM students WHERE is_active = ? ORDER BY name',
    [true]   // parameterized - ? is the placeholder
  );
  return rows;
}

getActiveStudents().then(console.log);

Note that Node.js placeholders differ by driver: PostgreSQL (pg) uses $1, $2, … while MariaDB / MySQL (mysql2) uses ?.

2.3 INSERT with Returning

PostgreSQL

async function createStudent(name) {
  const result = await pool.query(
    'INSERT INTO students (name, is_active) VALUES ($1, $2) RETURNING student_id',
    [name, true]
  );
  return result.rows[0].student_id;
}

MariaDB / MySQL

async function createStudent(name) {
  const [result] = await pool.execute(
    'INSERT INTO students (name, is_active) VALUES (?, ?)',
    [name, true]
  );
  return result.insertId;
}

Why return student_id?

Returning student_id is important because this value is generated by the database (auto-increment/identity). The application usually does not know the new ID in advance but often needs it immediately for follow-up actions (for example, creating related records).

2.4 Transactions in Node.js

PostgreSQL

async function transferCredits(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE account_id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE account_id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release(); // return client to pool
  }
}

MariaDB / MySQL

async function transferCredits(fromId, toId, amount) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    await conn.execute(
      'UPDATE accounts SET balance = balance - ? WHERE account_id = ?',
      [amount, fromId]
    );
    await conn.execute(
      'UPDATE accounts SET balance = balance + ? WHERE account_id = ?',
      [amount, toId]
    );
    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release(); // return connection to pool
  }
}

Always release the client

When using pool.connect() manually (for transactions), always call client.release() in a finally block. Forgetting this leaks connections and will eventually exhaust the pool.

2.5 Fetch Size and Pagination in Node.js

For large result sets, avoid loading everything into memory at once. Use pagination for UI/API responses and streaming/cursors for exports.

OFFSET/LIMIT Pagination (simple)

PostgreSQL

const pageSize = 50;
const page = 0;
const offset = page * pageSize;

const result = await pool.query(
  `SELECT student_id, name
   FROM students
   ORDER BY student_id
   LIMIT $1 OFFSET $2`,
  [pageSize, offset]
);

return result.rows;

MariaDB / MySQL

const pageSize = 50;
const page = 0;
const offset = page * pageSize;

const [rows] = await pool.execute(
  `SELECT student_id, name
   FROM students
   ORDER BY student_id
   LIMIT ? OFFSET ?`,
  [pageSize, offset]
);

return rows;

Keyset Pagination (recommended for large tables)

Keyset pagination is usually faster and more stable than large offsets because it uses the last seen key.

PostgreSQL

const lastSeenId = 1200;
const pageSize = 50;

const result = await pool.query(
  `SELECT student_id, name
   FROM students
   WHERE student_id > $1
   ORDER BY student_id
   LIMIT $2`,
  [lastSeenId, pageSize]
);

return result.rows;

MariaDB / MySQL

const lastSeenId = 1200;
const pageSize = 50;

const [rows] = await pool.execute(
  `SELECT student_id, name
   FROM students
   WHERE student_id > ?
   ORDER BY student_id
   LIMIT ?`,
  [lastSeenId, pageSize]
);

return rows;

Fetch Size / Streaming

• With pg and mysql2, standard query calls return the full result set in memory.
• For very large exports, use streaming/cursor-based access and process rows in chunks.
• Always keep an index on the columns used in WHERE and ORDER BY for pagination.

4. Connecting from Other Languages

4.1 PHP (PDO)

PHP’s PDO (PHP Data Objects) is a database abstraction layer similar to ODBC. It works with multiple RDBMS backends.

<?php
$dsn = 'pgsql:host=localhost;port=5432;dbname=school_db';
$user = getenv('DB_USER');
$pass = getenv('DB_PASSWORD');

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT name FROM students WHERE student_id = :id');
$stmt->execute([':id' => 42]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);
echo $student['name'];
?>

4.2 Python

If you also access the database from Python, use this minimal setup:

PostgreSQL

pip install psycopg2-binary python-dotenv

from dotenv import load_dotenv
import os
import psycopg2

load_dotenv()

conn = psycopg2.connect(
  host=os.environ["DB_HOST"],
  port=int(os.environ.get("DB_PORT", 5432)),
  dbname=os.environ["DB_NAME"],
  user=os.environ["DB_USER"],
  password=os.environ["DB_PASSWORD"]
)

with conn:
  with conn.cursor() as cur:
    cur.execute("SELECT student_id, name FROM students WHERE name = %s", ("Anna",))
    print(cur.fetchall())

MariaDB / MySQL

pip install mysql-connector-python python-dotenv

from dotenv import load_dotenv
import os
import mysql.connector

load_dotenv()

conn = mysql.connector.connect(
  host=os.environ["DB_HOST"],
  port=int(os.environ.get("DB_PORT", 3306)),
  database=os.environ["DB_NAME"],
  user=os.environ["DB_USER"],
  password=os.environ["DB_PASSWORD"]
)

cur = conn.cursor()
cur.execute("SELECT student_id, name FROM students WHERE name = %s", ("Anna",))
print(cur.fetchall())
cur.close()
conn.close()

4.3 Java (JDBC)

import java.sql.*;

String url = "jdbc:postgresql://localhost:5432/school_db";
String user = System.getenv("DB_USER");
String password = System.getenv("DB_PASSWORD");

try (Connection conn = DriverManager.getConnection(url, user, password);
   PreparedStatement stmt = conn.prepareStatement(
     "SELECT name FROM students WHERE student_id = ?")) {
  stmt.setInt(1, 42);
  try (ResultSet rs = stmt.executeQuery()) {
    if (rs.next()) {
      System.out.println(rs.getString("name"));
    }
  }
}

4.4 ODBC (Excel, Power BI, generic tools)

ODBC allows desktop tools like Excel or Power BI to connect directly to PostgreSQL and MariaDB / MySQL:

PostgreSQL

1. Install the PostgreSQL ODBC driver (psqlODBC) on Windows.
2. Open ODBC Data Sources in Windows and create a new DSN (Data Source Name).
3. In Excel, go to Data → Get Data → From Database → From ODBC and select your DSN.
4. In Power BI, choose Get Data → ODBC and select your DSN.

MariaDB / MySQL

1. Install a MariaDB/MySQL ODBC driver on Windows (for example MariaDB Connector/ODBC or MySQL Connector/ODBC).
2. Open ODBC Data Sources in Windows and create a new DSN (Data Source Name).
3. In Excel, go to Data → Get Data → From Database → From ODBC and select your DSN.
4. In Power BI, choose Get Data → ODBC and select your DSN.

32-bit vs. 64-bit ODBC on Windows

On Windows, there are two separate ODBC administrator tools — one for 64-bit drivers and one for 32-bit drivers:

• 64-bit: C:\Windows\System32\odbcad32.exe (despite the name, this manages 64-bit DSNs)
• 32-bit: C:\Windows\SysWOW64\odbcad32.exe (this manages 32-bit DSNs)

The architecture of the client application determines which driver it can load:

Application	Required driver
Excel 64-bit (Microsoft 365)	64-bit ODBC driver
Excel 32-bit (older Office)	32-bit ODBC driver
Power BI Desktop	64-bit ODBC driver
Custom 32-bit app	32-bit ODBC driver

A common mistake is installing only one driver bitness and then connecting from an application with a different architecture. In that case, the DSN will simply not appear in the application’s driver list. Always match the driver bitness to the connecting application.

Read-only ODBC users

When exposing PostgreSQL or MariaDB/MySQL data via ODBC to Excel or Power BI users, create a dedicated read-only database user/role with SELECT rights only. This prevents accidental data modification from desktop tools.

DSN-less Connections

Instead of pre-configuring a DSN in the ODBC administrator, you can embed all connection parameters directly in the application’s connection string:

PostgreSQL

DRIVER={PostgreSQL Unicode};SERVER=localhost;PORT=5432;DATABASE=school_db;UID=app_user;PWD=secret;

MariaDB / MySQL

DRIVER={MariaDB ODBC 3.1 Driver};SERVER=localhost;PORT=3306;DATABASE=school_db;UID=app_user;PWD=secret;

This avoids manual DSN setup on each machine and is useful for automated deployments or when the application is distributed to many users.

Unicode vs. ANSI Driver

For PostgreSQL, psqlODBC ships in two variants:

• PostgreSQL Unicode — handles UTF-8 and international characters correctly. Always prefer this.
• PostgreSQL ANSI — legacy variant for older applications that explicitly require ANSI encoding.

For MariaDB / MySQL, choose a modern Unicode-capable ODBC driver (for example MariaDB Connector/ODBC Unicode or MySQL Connector/ODBC Unicode) unless a legacy ANSI-only environment requires otherwise.

User DSN vs. System DSN

When creating a DSN in the ODBC administrator, you choose between two scopes:

Type	Visible to	Typical use
User DSN	Current Windows user only	Personal tools, development
System DSN	All users and Windows services	Shared applications, services

Windows services (e.g., a background sync job) run under a service account and typically cannot see User DSNs. Always use a System DSN for server-side or shared applications.

SSL/TLS

psqlODBC supports the sslmode parameter, matching PostgreSQL’s standard SSL modes:

sslmode value	Behavior
disable	No SSL (plaintext)
require	SSL required, certificate not verified
verify-ca	SSL + verify server certificate against a CA
verify-full	SSL + verify certificate and hostname

Add it to the DSN advanced options or to a DSN-less connection string:

...;SSLmode=require;

In any production or network environment, use at least require.

Credentials in DSNs

System DSNs store the username (and optionally the password) in the Windows registry. The password is visible to local administrators. For production systems:

• Do not store the password in the DSN.
• Let the application or user provide the password at connection time (prompt).
• Alternatively, use Windows-integrated authentication (SSPI/Kerberos) if your PostgreSQL server is configured for it.

Timeout Settings

By default, ODBC connections and queries can hang indefinitely on network issues. Set these parameters in the DSN or connection string:

• LoginTimeout — seconds to wait when establishing the connection (e.g., LoginTimeout=10).
• QueryTimeout — seconds before a running query is cancelled (configurable per statement in most drivers).

Without timeouts, a broken network connection can freeze an Excel spreadsheet or application indefinitely.

Cross-platform Notes (Windows, macOS, Linux)

The same ODBC concept exists on all major operating systems, but setup details differ:

Topic	Windows	macOS	Linux
ODBC manager	Built-in ODBC Administrator	Usually unixODBC or iODBC	Usually unixODBC
DSN storage	Registry (User/System DSN)	~/.odbc.ini and /etc/odbc.ini (common)	~/.odbc.ini and /etc/odbc.ini
Driver install	MSI/EXE installers	Homebrew/PKG (depends on driver)	Package manager + config files
Platform pitfall	32-bit vs. 64-bit mismatch	Intel vs. Apple Silicon (x86_64 vs. arm64)	Missing shared libraries / driver paths

Additional practical points:

• Bitness/architecture must match: On Windows, app and driver must both be 32-bit or both 64-bit. On macOS, verify native architecture (arm64 on Apple Silicon) to avoid Rosetta-related issues.
• Driver manager differences: unixODBC and iODBC are not configured identically. When following setup guides, always use instructions for the manager actually installed on your system.
• Permissions matter on Unix-like systems: A DSN in /etc/odbc.ini may require elevated rights to edit; user-specific DSNs in ~/.odbc.ini are often better for development.
• SSL certificates differ per OS: Trust store and certificate paths are handled differently. If TLS works on one OS but fails on another, check CA path/certificate configuration first.
• Desktop BI tool availability differs: Power BI Desktop is primarily Windows-based, while macOS/Linux often rely on alternatives or gateways.

5. Comparison of Interface Approaches

Approach	Typical use case	Portability	Performance	Complexity
Native driver (psycopg2, pg)	Python / Node.js web apps	Low (DB-specific)	High	Low
ODBC	Desktop tools, multi-DB apps	High	Medium	Medium
JDBC	Java enterprise apps	High (via driver)	High	Medium
ORM (SQLAlchemy, Prisma)	Rapid development, CRUD apps	High	Medium	Low–Medium
PDO (PHP)	PHP web apps	Medium	Medium	Low

6. Security Checklist for Database Interfaces

Secure database access is a critical part of application security. Before going to production, verify:

• Credentials are stored in environment variables or a secrets manager, not in source code.
• All SQL queries use parameterized statements — no string concatenation with user input.
• The application database user follows least privilege — only the tables and operations it actually needs.
• The PostgreSQL server is not exposed to the public internet (firewall rules restrict access to app servers only).
• TLS is enabled for the database connection in production.
• Connection pooling is configured to avoid connection exhaustion.
• Error messages shown to end users do not expose internal database structure or stack traces.

Summary

Database interfaces are the bridge between application code and the database engine. Key takeaways:

• Native drivers (psycopg2, pg) are the most common choice for Python and Node.js applications. ODBC/JDBC add portability across RDBMS vendors. ORMs provide a higher-level abstraction.
• Always use parameterized queries to prevent SQL injection.
• Use connection pools in any multi-user or web application context.
• Store credentials in environment variables, never in source code.
• Apply the least privilege principle: the application user should only have the minimum database permissions it needs.
• When troubleshooting, check both the application error and the PostgreSQL server log for the full picture.