gdDocumentation0.8.0

Use a database

English | 日本語

This guide builds a small script that stores a to-do list and explains database use one step at a time. It starts with SQLite, which needs no setup, and switches to PostgreSQL at the end.

1. Open it and create a table

Save this as todo.gd:

func main() -> int, Err:
    var db := GD.database.client()
    db.open({"driver": "sqlite", "path": "user://todo.sqlite3"})?
    db.query("CREATE TABLE IF NOT EXISTS todos(id INTEGER PRIMARY KEY, title TEXT NOT NULL UNIQUE, done INTEGER NOT NULL DEFAULT 0)")?
    db.close()
    return 0
gd todo.gd

2. Insert and read

Add these lines before db.close():

    db.query("INSERT INTO todos(title) VALUES($1)", ["Buy milk"])?
    var got := db.query("SELECT id, title, done FROM todos ORDER BY id")?
    for row in got.rows:
        print(row.id, " ", row.title)

Write $1, $2 in the SQL and pass the values in the array given as the second argument. Do not join values into the SQL text. A value passed through $1 is treated as a value whatever characters it contains.

rows in the result is an array of rows, and each row is a dictionary read by column name.

3. Choose how to read

What you wantFunction
The whole resultquery
The first rowquery_row
Many rows, one at a timequery_rows

query_row returns Err.NOT_FOUND when there is no row. Tell that case apart yourself when a missing row is not a failure.

func title_of(db: GDDatabaseClient, id: int) -> String, Err:
    var row, e := db.query_row("SELECT title FROM todos WHERE id=$1", [id])
    if e and e.kind == Err.NOT_FOUND:
        return "(none)"
    if e:
        return "", e
    return row.title

query_rows reads one row at a time without collecting the result in memory. When next() returns false, check err(), then call close().

func print_titles(db: GDDatabaseClient) -> int, Err:
    var rows := db.query_rows("SELECT title FROM todos ORDER BY id")?
    while rows.next():
        print(rows.scan()?.title)
    if rows.err() != null:
        return 1, rows.err()
    rows.close()
    return 0

4. Avoid inserting the same item twice

title is UNIQUE, so inserting the same title twice fails. violation in e.info tells you the kind of failure.

func add(db: GDDatabaseClient, title: String) -> bool, Err:
    var _made, e := db.query("INSERT INTO todos(title) VALUES($1)", [title])
    if e and e.info.get("violation") == "duplicate":
        return false
    if e:
        return false, e
    return true

violation is one of duplicate, not_null (a required value is missing), or foreign_key (the referenced row is missing).

5. Commit several changes together

Put changes that must all be undone on a failure inside transaction. The changes are committed when the function you pass returns success and rolled back when it returns an Err.

func finish_all(db: GDDatabaseClient) -> int, Err:
    return db.transaction(func(tx: GDDatabaseTx) -> int, Err:
        tx.query("UPDATE todos SET done=1")?
        var left := tx.query("SELECT count(*) AS n FROM todos WHERE done=0")?
        return left.rows[0].n
    )

Inside the function, run SQL through the given tx, not db. db.query() is not part of this unit.

6. Switch to PostgreSQL

Only the settings passed to open change. query and transaction are written the same way.

func open_postgres() -> GDDatabaseClient, Err:
    var db := GD.database.client()
    db.open({
        "driver": "postgres",
        "host": "127.0.0.1",
        "database": "app",
        "user": "app",
        "password": GD.cli.env("PGPASSWORD", ""),
    })?
    return db

7. Use it from a web server

In a function that handles a request, pass req.context to GD.async.with_context_pair. A waiting query is cancelled when the client disconnects.

func list_todos(req: GDWebRequest, db: GDDatabaseClient) -> Variant, Err:
    return await GD.async.with_context_pair(req.context, db.query_async("SELECT id, title FROM todos"))

Async operations and cancellation explains how cancellation works.