PostgreSQL and MySQL: practical database examples

This guide builds the same Notes application with PostgreSQL and MySQL. Each database has eight examples covering connection, table creation, inserting, reading, filtering, updating, transactions, and deleting.

Run each section's examples in order. The code under each numbered example goes inside its database's main() function, after lease is created and before lease.release().

PostgreSQL

Start your PostgreSQL application

The following is the complete program structure. Replace the comment with the code from an example below:

from mojara.dbclient import PooledDatabase

def main() raises:
    var db = PooledDatabase(
        "host=127.0.0.1 port=5432 dbname=notes user=notes password=your_password",
        capacity=3,
    )
    var lease = db.acquire(timeout_ms=3000)

    # Put each PostgreSQL example here, in order.

    lease.release()
    db.close()

The constructor opens a PostgreSQL connection pool. acquire() borrows a connection. Use your own database connection details.

PostgreSQL example 1: Execute a SELECT query

    var greeting = lease.query("SELECT 'Hello from PostgreSQL' AS message")
    print(greeting.cell(0, 0))

query() executes SQL, and cell(0, 0) reads the first column of the first row.

Output: Hello from PostgreSQL

PostgreSQL example 2: Create the Notes table

    var schema = lease.query(
        "CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL, completed BOOLEAN NOT NULL DEFAULT FALSE)"
    )
    print("Notes table is ready")

The table stores a numeric ID, a title, and whether the note is complete. IF NOT EXISTS makes the statement safe to run after the table has already been created.

Output: Notes table is ready

PostgreSQL example 3: Insert a note using parameters

    var inserted = lease.query2(
        "INSERT INTO notes (id, title) VALUES ($1::int, $2::text) ON CONFLICT (id) DO UPDATE SET title = EXCLUDED.title",
        "101", "Learn Mojara",
    )
    print("Note saved")

query2() binds the two values to $1 and $2. PostgreSQL converts the ID to an integer. The conflict clause lets you repeat this example without a duplicate-key error.

Result: the database contains note 101 with the title Learn Mojara.

PostgreSQL example 4: Read all notes

    var notes = lease.query("SELECT id, title FROM notes ORDER BY id")
    for i in range(notes.rows()):
        print(notes.cell(i, 0), notes.cell(i, 1))

rows() gives the result count. cell(i, 0) reads the ID, and cell(i, 1) reads the title.

Output includes: 101 Learn Mojara

PostgreSQL example 5: Find a note by ID

    var selected = lease.query1(
        "SELECT title FROM notes WHERE id = $1::int",
        "101",
    )
    if selected.rows() > 0:
        print(selected.cell(0, 0))

query1() binds one parameter to $1. The row count check prevents reading a missing row.

Output: Learn Mojara

PostgreSQL example 6: Update a note

    var updated = lease.query2(
        "UPDATE notes SET title = $1::text WHERE id = $2::int",
        "Master Mojara routing", "101",
    )
    print("Rows updated:", updated.affected())

The new title is bound to $1; the ID is bound to $2. affected() reports the number of rows changed.

Output: Rows updated: 1

PostgreSQL example 7: Roll back and commit transactions

First insert a note and roll it back:

    lease.begin()
    var temporary = lease.query2(
        "INSERT INTO notes (id, title) VALUES ($1::int, $2::text)",
        "102", "Temporary note",
    )
    lease.rollback()

    var after_rollback = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After rollback:", after_rollback.cell(0, 0))

begin() starts a transaction, and rollback() cancels the insertion.

Output: After rollback: 0

Now make the insertion permanent:

    lease.begin()
    var committed = lease.query2(
        "INSERT INTO notes (id, title) VALUES ($1::int, $2::text)",
        "102", "Saved with a transaction",
    )
    lease.commit()

    var after_commit = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After commit:", after_commit.cell(0, 0))

commit() saves the change.

Output: After commit: 1

PostgreSQL example 8: Delete a note

    var deleted = lease.query1(
        "DELETE FROM notes WHERE id = $1::int",
        "102",
    )
    print("Rows deleted:", deleted.affected())

The query deletes the note created in example 7, and affected() reports the result.

Output: Rows deleted: 1

After these examples, the application executes:

    lease.release()
    db.close()

The lease is returned to the pool before the pool is closed.

MySQL

The following examples build the same Notes application using MySQL. MySQL uses ? instead of PostgreSQL's $1 and $2 placeholders.

Start your MySQL application

Use this program structure and place each example between the lease acquisition and release:

from mojara.dbclient import PooledDatabase

def main() raises:
    var db = PooledDatabase(
        "127.0.0.1", 3306, "notes", "your_password", "notes",
        capacity=3,
    )
    var lease = db.acquire(timeout_ms=3000)

    # Put each MySQL example here, in order.

    lease.release()
    db.close()

The constructor takes host, port, username, password, database name, and pool capacity.

MySQL example 1: Execute a SELECT query

    var greeting = lease.query("SELECT 'Hello from MySQL' AS message")
    print(greeting.cell(0, 0))

query() runs the SQL. cell(0, 0) returns the first field.

Output: Hello from MySQL

MySQL example 2: Create the Notes table

    var schema = lease.query(
        "CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, completed TINYINT(1) NOT NULL DEFAULT 0) ENGINE=InnoDB"
    )
    print("Notes table is ready")

VARCHAR(255) stores the title. TINYINT(1) holds a completion flag, and InnoDB supports transactions.

Output: Notes table is ready

MySQL example 3: Insert a note using parameters

    var inserted = lease.query3(
        "INSERT INTO notes (id, title) VALUES (?, ?) ON DUPLICATE KEY UPDATE title = ?",
        "101", "Learn Mojara", "Learn Mojara",
    )
    print("Note saved")

query3() binds each value to a ? placeholder. When ID 101 already exists, the statement updates the title instead of failing.

Result: note 101 contains the title Learn Mojara.

MySQL example 4: Read all notes

    var notes = lease.query("SELECT id, title FROM notes ORDER BY id")
    for i in range(notes.rows()):
        print(notes.cell(i, 0), notes.cell(i, 1))

Loop through rows() and read each column using cell(row, column).

Output includes: 101 Learn Mojara

MySQL example 5: Find a note by ID

    var selected = lease.query1(
        "SELECT title FROM notes WHERE id = ?",
        "101",
    )
    if selected.rows() > 0:
        print(selected.cell(0, 0))

query1() safely binds the note ID.

Output: Learn Mojara

MySQL example 6: Update a note

    var updated = lease.query2(
        "UPDATE notes SET title = ? WHERE id = ?",
        "Master Mojara routing", "101",
    )
    print("Rows updated:", updated.affected())

The first parameter is the new title, and the second is the note ID.

Output: Rows updated: 1

MySQL example 7: Roll back and commit transactions

First roll back an insertion:

    lease.begin()
    var temporary = lease.query2(
        "INSERT INTO notes (id, title) VALUES (?, ?)",
        "102", "Temporary note",
    )
    lease.rollback()

    var after_rollback = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After rollback:", after_rollback.cell(0, 0))

rollback() reverses the insertion.

Output: After rollback: 0

Now commit the same ID:

    lease.begin()
    var committed = lease.query2(
        "INSERT INTO notes (id, title) VALUES (?, ?)",
        "102", "Saved with a transaction",
    )
    lease.commit()

    var after_commit = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After commit:", after_commit.cell(0, 0))

commit() saves the insertion.

Output: After commit: 1

MySQL example 8: Delete a note

    var deleted = lease.query1(
        "DELETE FROM notes WHERE id = ?",
        "102",
    )
    print("Rows deleted:", deleted.affected())

affected() reports how many records were deleted.

Output: Rows deleted: 1

Complete the program by returning the connection and closing the pool:

    lease.release()
    db.close()

PostgreSQL versus MySQL syntax

For additional work with query results, see Connection pools, Models, and API reference.