Skip to content
Tutorials Center
My learning

Topics

Lesson 104 of 125 ยท Database Systems

Connecting to Databases from Code: Drivers, ODBC and ORMs

Learn how apps talk to databases through drivers (JDBC, ODBC, DB-API, PDO), connection strings, pools, parameterized queries, ORMs and the N+1 problem.

Almost nobody types SQL into a database client in production. Queries come from applications: a web backend looking up a customer, a nightly job loading sales, a mobile app's API saving an order. Between your code and the database sits a stack of software: a driver that speaks the database's network protocol, often a connection pool, and sometimes an ORM that writes the SQL for you. Each layer has habits that decide whether your app is fast, safe and reliable.

This lesson walks through that stack from the bottom up: drivers and the standard APIs on top of them (JDBC, ODBC, DB-API, PDO), connection strings, the life cycle of a query in code, connection pooling, transactions, and ORMs, including the infamous N+1 query problem. Code in Python, Java, PHP, JavaScript, C# and Go is read-only; the SQL each one sends runs right here against the shop database.

Drivers and standard APIs

Every server database has its own wire protocol: the bytes sent over the network to log in, send a query and stream back rows. A driver is a library that implements that protocol for one programming language. To keep application code from depending on one product, most languages define a standard API that all drivers implement:

Language Standard API Example drivers
Java (and Kotlin, Scala) JDBC PostgreSQL JDBC, MySQL Connector/J, Microsoft JDBC Driver, SQLite JDBC
Python DB-API 2.0 (PEP 249) sqlite3 (built in), psycopg, mysqlclient, PyMySQL, pyodbc
PHP PDO pdo_pgsql, pdo_mysql, pdo_sqlite, pdo_sqlsrv
C# / .NET ADO.NET Microsoft.Data.SqlClient, Npgsql, MySqlConnector
Go database/sql pgx, go-sql-driver/mysql
JavaScript (Node.js) No single standard pg (node-postgres), mysql2, better-sqlite3
Any (C-based) ODBC Vendor ODBC drivers for almost every database

ODBC (Open Database Connectivity) deserves a word. It's an older, language-neutral standard with a C interface, configured through a driver manager on the operating system. Tools such as Excel, Power BI and many reporting products use ODBC to reach any database without knowing its protocol. In application code, a native driver is usually faster and simpler; ODBC is the universal adapter for everything else. (JDBC was originally modeled on ODBC, which is why their concepts match so closely.)

Whatever the language, the same handful of objects appears:

  • a connection (one logged-in session with the server),
  • a statement or cursor (one query being prepared and executed),
  • a result set (the rows coming back, often streamed in batches),
  • and a transaction boundary (commit or roll back).

Connection strings

A driver needs to know where the server is, which database to use, who you are and how to connect. That information is packed into a connection string, and each ecosystem writes it differently (read-only):

TEXT
# URL style (PostgreSQL, MySQL, many ORMs and cloud consoles)
postgresql://shop_app:[email protected]:5432/shop?sslmode=verify-full
mysql://shop_app:[email protected]:3306/shop

# JDBC URL (user and password usually passed separately)
jdbc:postgresql://db.example.com:5432/shop?sslmode=verify-full
jdbc:mysql://db.example.com:3306/shop?sslMode=VERIFY_IDENTITY
jdbc:sqlite:/var/data/shop.db

# ADO.NET / ODBC key=value style
Server=db.example.com;Database=shop;User Id=shop_app;Password=secret;Encrypt=True
Driver={ODBC Driver 18 for SQL Server};Server=db.example.com;Database=shop;Encrypt=yes

# Python's sqlite3 and other embedded databases: just a file path
shop.db

The parts are always the same: host, port (PostgreSQL 5432, MySQL 3306, SQL Server 1433), database name, user, password and options such as TLS mode, timeouts and application name.

Keep credentials out of code. Read the connection string or its password from an environment variable or a secrets manager, never hard-code it or commit it to Git. Give the application its own database account with only the permissions it needs, and require TLS with certificate checks, as described in Encrypting Data in the Database.

A query from code, step by step

Here's the same task, "find a customer's recent orders", in several languages. Notice the shared shape: get a connection, send SQL with placeholders, bind the values separately, read the rows, and release everything (read-only):

Python
# Python: DB-API with the built-in sqlite3 module
import sqlite3

with sqlite3.connect("shop.db") as conn:          # the block commits or rolls back
    conn.row_factory = sqlite3.Row                # rows behave like dicts
    rows = conn.execute(
        "SELECT id, ordered_at, status FROM orders "
        "WHERE customer_id = ? ORDER BY ordered_at DESC LIMIT 5",
        (customer_id,),
    ).fetchall()
    for row in rows:
        print(row["id"], row["status"])
Java
// Java: JDBC with try-with-resources (everything closes automatically)
String sql = """
    SELECT id, ordered_at, status FROM orders
    WHERE customer_id = ? ORDER BY ordered_at DESC LIMIT 5
    """;
try (Connection conn = dataSource.getConnection();
     PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setLong(1, customerId);
    try (ResultSet rs = stmt.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("status"));
        }
    }
}
PHP
// PHP: PDO with exceptions and real prepared statements
$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);
$stmt = $pdo->prepare(
    'SELECT id, ordered_at, status FROM orders
     WHERE customer_id = :customer ORDER BY ordered_at DESC LIMIT 5'
);
$stmt->execute(['customer' => $customerId]);
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $order) {
    echo $order['id'], ' ', $order['status'], PHP_EOL;
}
Go
// Go: database/sql (the *sql.DB value is itself a connection pool)
rows, err := db.QueryContext(ctx,
    `SELECT id, ordered_at, status FROM orders
     WHERE customer_id = $1 ORDER BY ordered_at DESC LIMIT 5`, customerID)
if err != nil {
    return err
}
defer rows.Close()
for rows.Next() {
    var id int64
    var orderedAt time.Time
    var status string
    if err := rows.Scan(&id, &orderedAt, &status); err != nil {
        return err
    }
}
return rows.Err()

Placeholder styles differ (? in JDBC, SQLite and MySQL drivers, $1 in PostgreSQL drivers, :name in PDO and Oracle, @name in SQL Server, %s in psycopg), but the rule doesn't: values always travel as parameters, never pasted into the SQL string. That's what makes code immune to SQL injection, and the SQL Injection lesson shows the attacks it prevents. Parameters also let the server reuse the plan of a statement it has already prepared.

What the driver sends is ordinary SQL. With customer_id bound to 8, the database runs exactly this:

SQL The query the code above sends ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Customer 8 has four orders, so you get four rows even with LIMIT 5. When something works in a database client but not in your application, logging the SQL and the bound values and running them by hand like this is the fastest way to find out why.

Per-connection settings

Some settings belong to the connection, not the database, so the application has to set them every time it connects. The classic SQLite example: foreign key enforcement is off by default on every new connection, for backward compatibility. This example fails on purpose at the end:

SQL A setting that every connection must turn on ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first INSERT reports "OK, 1 row affected." and quietly stores an orphan order. After PRAGMA foreign_keys = ON, the second stops with Error: FOREIGN KEY constraint failed. Server databases have similar session settings: time zone (SET TIME ZONE 'UTC'), MySQL's sql_mode, the PostgreSQL search_path, statement timeouts and the application name that shows up in monitoring. Pools and ORMs let you configure an "init SQL" or connection hook for exactly this.

Connection pooling

Opening a connection is expensive: a network round trip, a TLS handshake, authentication, and on PostgreSQL, a whole new server process. That can take 20 to 100 milliseconds, often longer than the query itself. A connection pool opens a set of connections once and lends them out:

  1. The code asks the pool for a connection (instead of opening one).
  2. It runs its queries.
  3. It "closes" the connection, which really returns it to the pool for the next request.

Most stacks include one: HikariCP for Java, the pool built into Go's *sql.DB and ADO.NET, SQLAlchemy's pool in Python, pg.Pool in Node.js. Settings that matter:

  • Maximum size. More isn't better. A database server with 8 CPU cores can only run a handful of queries truly in parallel; hundreds of active connections just fight for CPU, memory and locks. Start small (often 10 to 20 per application instance) and measure. Remember the total: 20 app servers ร— 20 connections each = 400 connections on one database.
  • Timeouts. How long to wait for a free connection, how long an idle connection lives, and a maximum lifetime so connections are recycled before the server or a firewall kills them.
  • Leaks. Code that borrows a connection and never returns it (a missing close(), finally or with block) slowly drains the pool until every request hangs. Use the language's automatic cleanup constructs, as in the examples above.

When many application processes share one database (serverless functions, PHP workers, dozens of microservices), an external pooler sits in front of the server: PgBouncer or Pgpool-II for PostgreSQL, ProxySQL for MySQL, or a managed proxy like Amazon RDS Proxy. The Tuning the Database Server lesson shows how the server's own connection limits fit in.

Transactions in application code

Drivers start in autocommit mode: each statement is its own transaction. When several statements must succeed or fail together, the code opens a transaction explicitly and makes sure every error path rolls it back (read-only):

Python
# Python (psycopg 3): the block commits on success and rolls back on any exception
with conn.transaction():
    conn.execute("UPDATE products SET stock = stock - %s WHERE id = %s", (qty, product_id))
    conn.execute(
        "INSERT INTO order_items (order_id, product_id, quantity, unit_price) "
        "VALUES (%s, %s, %s, %s)",
        (order_id, product_id, qty, price),
    )
Java
// Java (JDBC)
conn.setAutoCommit(false);
try {
    // ... several executeUpdate() calls ...
    conn.commit();
} catch (SQLException e) {
    conn.rollback();
    throw e;
}

Batching matters too. Inserting 10,000 rows with 10,000 separate autocommitted statements means 10,000 round trips and 10,000 commits. Wrapping them in one transaction, using the driver's batch API (executemany() in Python, addBatch()/executeBatch() in JDBC) or a multi-row INSERT makes the same work dramatically faster. The Importing and Exporting Data lesson has more bulk-loading tips.

ORMs and ODMs

An ORM (object-relational mapper) maps tables to classes and rows to objects, so you write customer.orders instead of SQL. Popular ones include Hibernate/JPA (Java), Entity Framework (C#), SQLAlchemy and the Django ORM (Python), ActiveRecord (Ruby on Rails), Eloquent (Laravel) and Prisma, TypeORM and Sequelize (JavaScript/TypeScript). An ODM (object-document mapper) does the same for document databases, for example Mongoose for MongoDB (see SQL vs NoSQL).

Python
# SQLAlchemy 2.0 (Python)
stmt = select(Customer).where(Customer.country == "Poland").order_by(Customer.id)
for customer in session.scalars(stmt):
    print(customer.first_name)
PHP
// Eloquent (Laravel)
$customers = Customer::where('country', 'Poland')->orderBy('id')->get();

Behind the scenes, the ORM generates ordinary parameterized SQL, typically with every column listed and every name quoted:

SQL What an ORM sends for a simple filter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Six Polish customers come back. ORMs pay off: less repetitive code, parameters by default, portable queries across databases, migrations, and objects that fit naturally into the rest of the program. The costs: they hide the SQL, so it's easy to write code that sends far more queries than you think, and complex reports (window functions, CTEs, pivot tables) are often clearer in plain SQL. Every mature ORM has an escape hatch for raw, still-parameterized SQL, and good teams use it without guilt.

Turn on query logging in development. Every ORM can print the SQL it runs (for example Django's connection.queries, Laravel's DB::listen(), Hibernate's show_sql or SQLAlchemy's echo=True). Watching the log for a single page load is the quickest way to catch the problem in the next section.

The N+1 query problem

Suppose a page lists the German customers with their orders. With lazy loading, the natural ORM code looks harmless:

Python
customers = session.scalars(select(Customer).where(Customer.country == "Germany"))
for customer in customers:
    print(customer.first_name, len(customer.orders))   # each .orders runs a query!

The first line runs 1 query for the customers. Then each access to customer.orders runs another query for that one customer. With N customers, that's N + 1 queries:

SQL The N+1 pattern, spelled out ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Three German customers mean four round trips. Each query is fast, but a page with 200 customers sends 201 queries, and with 1 ms of network latency per round trip, the page spends 0.2 seconds just waiting, before the database does any work. In loops nested two levels deep (customers, their orders, each order's items), it gets far worse.

The fix is to fetch related rows in bulk. ORMs call it eager loading (selectinload() or joinedload() in SQLAlchemy, with() in Eloquent, select_related()/prefetch_related() in Django, Include() in Entity Framework, JOIN FETCH in JPA). Under the hood it sends one of these:

SQL Eager loading: two queries or one join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Both strategies return the same data in a fixed number of queries, no matter how many customers there are. The IN approach sends two queries and no duplicated customer data; the JOIN sends one query but repeats each customer's columns on every order row. ORMs pick one or let you choose. And if the page only needs the count of orders, ask the database for exactly that with GROUP BY instead of loading every order object.

Choosing your level of abstraction

Approach Good for Watch out for
Driver + plain SQL Full control, reports, performance-critical paths, small scripts Repetitive mapping code; easy to forget parameters if you're careless
Query builder (jOOQ, Knex, Laravel's DB, SQLAlchemy Core) Composable, parameterized SQL that still looks like SQL Learning its API; still need to know SQL
Full ORM CRUD-heavy apps, domain objects, rapid development Hidden N+1 queries, over-fetching, surprising generated SQL

Most real applications mix all three: the ORM for everyday object work, a query builder for dynamic filters, and hand-written SQL for the reports and hot paths. Whatever the layer, the SQL in this course is what actually runs, which is why knowing it makes you better at using every tool on top of it.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the main job of a database driver?

    The driver speaks the server's wire protocol (login, queries, results). Standard APIs such as JDBC, DB-API and PDO give drivers for different databases the same interface.
  2. 2. A page lists 50 orders and, for each one, accesses order.customer in a loop with lazy loading. How many queries run?

    One query loads the orders, then each of the 50 accesses loads one customer. Eager loading reduces this to one or two queries.
  3. 3. Why do applications use a connection pool?

    Pools amortize the connection cost. They should stay fairly small, because a server can only run a limited number of queries truly in parallel.
  4. 4. A SQLite app inserts an order for a customer id that doesn't exist and gets no error, although the table has a REFERENCES clause. What is the most likely cause?

    Foreign key enforcement is a per-connection setting in SQLite. Drivers, pools or ORMs should switch it on right after connecting.
  5. 5. Where should a production database password live?

    Secrets in source code end up in Git history, logs and copies of the repository. Load them from the environment or a secrets manager.

Summary

  • A driver implements a database's wire protocol; standard APIs such as JDBC, DB-API, PDO, ADO.NET and Go's database/sql give all drivers the same shape. ODBC is the language-neutral universal adapter used by many tools.
  • A connection string holds host, port, database, user, password and options (TLS, timeouts). Keep secrets out of code.
  • Code follows one pattern: connection โ†’ statement with placeholders โ†’ bound values โ†’ rows โ†’ release resources (with, try-with-resources, defer).
  • Some settings are per connection (SQLite's PRAGMA foreign_keys, time zone, search_path); set them in a connection hook.
  • Connection pools reuse open connections; keep them small, set timeouts and avoid leaks. External poolers (PgBouncer, ProxySQL) help when many processes share a server.
  • Group related writes in transactions and batch bulk inserts.
  • ORMs (and ODMs for document stores) generate parameterized SQL from objects. Watch for the N+1 problem and fix it with eager loading (an IN query or a JOIN).

Next: Project: Sales Analysis, where you'll analyze a year of the shop's sales from revenue trends to a cohort table.