PostgreSQL: eight examples

Each example includes Mojo code, an explanation, and the expected result. Follow the numbered examples in order, placing each snippet inside the application function shown at the beginning of the guide.

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.