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 0gd todo.gd- GD.database.client creates a client, and
openconnects it. user://is a writable location that gd provides.- Every SQL statement runs through query. The trailing
?endsmainthere when the call fails. - Call
close()when you are done.
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 want | Function |
|---|---|
| The whole result | query |
| The first row | query_row |
| Many rows, one at a time | query_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.titlequery_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 04. 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 trueviolation 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- Read the password from an environment variable instead of writing it in the source.
- SQL is not translated. Write SQL that both SQLite and PostgreSQL accept.
- GDPostgresClient lists value types and detailed connection behavior.
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.