Connecting to the database from code

In short: A program talks to a database through a driver: it opens a connection to the database address, sends the query, reads the rows one by one and closes the connection.

Problem

The “Under 5 €” page

The shop's website has a page with the cheap products. You already know the query for it:

SQLdatabase: shop

SELECT name, price FROM products WHERE price < 5 ORDER BY price;

SELECT name, price FROM products WHERE price < 5 ORDER BY price;

▶ RunRestore the queryFresh database

But nobody on the site types queries into a box. When someone opens the page, the shop's server runs a program. The program sends this query to the database, gets the 17 rows back as values in its own language (a string, a number) and puts them into the HTML. How exactly does a program talk to a database?

The idea

The five steps

It is the same in every language:

1. Driver. A library that speaks the language of a particular database (SQLite, PostgreSQL, MySQL).
2. Connection. The program says where the database is (its address) and connects.
3. Query. The SQL text goes to the database.
4. Rows. The result comes back row by row, and the program reads its columns as values.
5. Closing. The connection is released, even if an error happened halfway.

Here is the same query, with a small copy of the shop in memory (:memory:, six products that setup creates at the start):

Java

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    String sql = "SELECT name, price FROM products WHERE price < 5 ORDER BY price";
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        while (rs.next()) {
            System.out.printf("%s: %.2f €%n", rs.getString("name"), rs.getDouble("price"));
        }
    }
}

Output

Butter roll: 0.80 €
White bread: 1.20 €
Fresh milk: 1.60 €
Butter: 3.20 €

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name, price FROM products WHERE price < 5 ORDER BY price";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine($"{reader.GetString(0)}: {reader.GetDouble(1):F2} €");
}

Output

Butter roll: 0.80 €
White bread: 1.20 €
Fresh milk: 1.60 €
Butter: 3.20 €

Python

conn = sqlite3.connect(":memory:")
setup(conn)
sql = "SELECT name, price FROM products WHERE price < 5 ORDER BY price"
for name, price in conn.execute(sql):
    print(f"{name}: {price:.2f} €")
conn.close()

Output

Butter roll: 0.80 €
White bread: 1.20 €
Fresh milk: 1.60 €
Butter: 3.20 €

JavaScript

const db = new DatabaseSync(":memory:");
setup(db);
const sql = "SELECT name, price FROM products WHERE price < 5 ORDER BY price";
for (const row of db.prepare(sql).all()) {
  console.log(`${row.name}: ${row.price.toFixed(2)} €`);
}
db.close();

Output

Butter roll: 0.80 €
White bread: 1.20 €
Fresh milk: 1.60 €
Butter: 3.20 €

The language takes care of the fifth step: try (…) in Java, using in C#, close() in Python and JavaScript. In Python with ends the transaction, not the connection, so close() is written out here.

More precisely, if you are curious

setup runs the SQL text that creates the table with the six products:

Java

static void setup(Connection conn) throws SQLException {
    try (Statement st = conn.createStatement()) {
        st.executeUpdate("""
            CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, discount INTEGER);
            INSERT INTO products VALUES
                (1, 'White bread', 1.20, NULL), (3, 'Butter roll', 0.80, 10), (5, 'Fresh milk', 1.60, NULL),
                (7, 'White cheese', 7.90, 15), (9, 'Butter', 3.20, NULL), (17, 'Coffee beans', 14.90, 0);
            """);
    }
}

C#

static void Setup(SqliteConnection conn)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = """
        CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, discount INTEGER);
        INSERT INTO products VALUES
            (1, 'White bread', 1.20, NULL), (3, 'Butter roll', 0.80, 10), (5, 'Fresh milk', 1.60, NULL),
            (7, 'White cheese', 7.90, 15), (9, 'Butter', 3.20, NULL), (17, 'Coffee beans', 14.90, 0);
        """;
    cmd.ExecuteNonQuery();
}

Python

def setup(conn):
    conn.executescript("""
        CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, discount INTEGER);
        INSERT INTO products VALUES
            (1, 'White bread', 1.20, NULL), (3, 'Butter roll', 0.80, 10), (5, 'Fresh milk', 1.60, NULL),
            (7, 'White cheese', 7.90, 15), (9, 'Butter', 3.20, NULL), (17, 'Coffee beans', 14.90, 0);
    """)

JavaScript

function setup(db) {
  db.exec(`
    CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL, discount INTEGER);
    INSERT INTO products VALUES
        (1, 'White bread', 1.20, NULL), (3, 'Butter roll', 0.80, 10), (5, 'Fresh milk', 1.60, NULL),
        (7, 'White cheese', 7.90, 15), (9, 'Butter', 3.20, NULL), (17, 'Coffee beans', 14.90, 0);
  `);
}

The drivers in the examples: sqlite-jdbc in Java (the package org.xerial:sqlite-jdbc), Microsoft.Data.Sqlite in C# (NuGet), and in Python and JavaScript SQLite is built in (sqlite3, and node:sqlite since Node 22). Every example has been run, and its output is exactly this.

See it

The database address and the password

SQLite is a file, and the address is the path to it. If you made shop.db in the lesson “SQL and real databases” (sqlite3 shop.db < shop-en.sql), replace :memory: with the path and remove setup: in Java it is jdbc:sqlite:shop.db, in C# Data Source=shop.db, and in Python and JavaScript just "shop.db". The same program will show all 17 products.

A server database (PostgreSQL, MySQL, SQL Server) lives in another program, often on another computer. Its address has a host, a port, a database name, a user and a password:

Java

String url = "jdbc:postgresql://localhost:5432/shop";
String password = System.getenv("SHOP_DB_PASSWORD");
try (Connection conn = DriverManager.getConnection(url, "shop_app", password)) {
    // the same queries
}

C#

var password = Environment.GetEnvironmentVariable("SHOP_DB_PASSWORD");
var url = $"Host=localhost;Port=5432;Database=shop;Username=shop_app;Password={password}";
using var conn = new NpgsqlConnection(url);
conn.Open();

Python

import os
import psycopg

conn = psycopg.connect(host="localhost", port=5432, dbname="shop",
                       user="shop_app", password=os.environ["SHOP_DB_PASSWORD"])

JavaScript

import pg from "pg";

const db = new pg.Client({ host: "localhost", port: 5432, database: "shop",
                           user: "shop_app", password: process.env.SHOP_DB_PASSWORD });
await db.connect();

The password is not in the code. The code goes into git, where everyone with access sees it, and the password for the real site is different from the one on your computer. So it comes from outside: from an environment variable, as here, or from the hosting provider's secrets store.

These four examples are not run in the site's checks, because they need a PostgreSQL server. The drivers are org.postgresql, Npgsql, psycopg and pg.

Try it

The query for another page

The “Sold out” page in the admin panel shows the names of the products with stock 0, in alphabetical order. Write the query the program will send.

SQLdatabase: shop

Check▶ Run

The idea

From column to value

Every column arrives as a value of a type in the language. INTEGER becomes int (or long), REAL becomes double, TEXT becomes String / string / str. The interesting one is NULL. In Python it is None, in JavaScript null, and in Java and C# it depends on how you read it:

Java

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT name, discount FROM products WHERE id IN (1, 3, 17)")) {
        while (rs.next()) {
            int discount = rs.getInt("discount");
            String text = rs.wasNull() ? "no discount" : discount + "%";
            System.out.println(rs.getString("name") + ": getInt returns " + discount + ", so " + text);
        }
    }
}

Output

White bread: getInt returns 0, so no discount
Butter roll: getInt returns 10, so 10%
Coffee beans: getInt returns 0, so 0%

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT name, discount FROM products WHERE id IN (1, 3, 17)";
using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    var text = reader.IsDBNull(1) ? "no discount" : $"{reader.GetInt32(1)}%";
    Console.WriteLine($"{reader.GetString(0)}: {text}");
}

Output

White bread: no discount
Butter roll: 10%
Coffee beans: 0%

Python

conn = sqlite3.connect(":memory:")
setup(conn)
for name, discount in conn.execute("SELECT name, discount FROM products WHERE id IN (1, 3, 17)"):
    text = "no discount" if discount is None else f"{discount}%"
    print(f"{name}: {discount!r}, so {text}")
conn.close()

Output

White bread: None, so no discount
Butter roll: 10, so 10%
Coffee beans: 0, so 0%

JavaScript

const db = new DatabaseSync(":memory:");
setup(db);
for (const row of db.prepare("SELECT name, discount FROM products WHERE id IN (1, 3, 17)").all()) {
  const text = row.discount === null ? "no discount" : `${row.discount}%`;
  console.log(`${row.name}: ${row.discount}, so ${text}`);
}
db.close();

Output

White bread: null, so no discount
Butter roll: 10, so 10%
Coffee beans: 0, so 0%

White bread has no discount (NULL), while the coffee has a discount of 0%. These are different things. In Java getInt returns 0 for both, and only wasNull() tells them apart. In C# GetInt32 on a NULL stops with an error, so you ask IsDBNull first.

Exercise

The new products

The home page shows the new products: the name and price of the products added (added_at) on 1 June 2025 or later, the newest first, and by name when the date is the same.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. The program read a row with a price and showed it. Which of these steps is the one most often forgotten, and later brings the site down?

Reading the rowsSending the queryClosing the connection

2. Where should the password for the shop database be kept?

In the code, next to the addressIn a comment above the connectionIn an environment variable or a secrets store

3. In Java the discount column is NULL, and the program calls rs.getInt("discount"). What does it get?

0nullAn error

4. The query is the same in all four languages. What changes if the shop moves from SQLite to PostgreSQL?

Only the driver and the database address (plus the differences in dialect)NothingEverything in the program

Check

Start the lesson over

Related lessons

• Builds on: SELECT: choosing columns · WHERE: filtering rows · NULL: no value · ORDER BY, LIMIT and DISTINCT
• Continues in: SQL injection and parameters · ORM and the N+1 problem · Schema migrations · Connection pools, backups and permissions
• Tasks on this topic: The database in a program