sql

Module sql provides a generic interface for connecting to various SQL databases.

This module uses the Go standard library's database/sql package with the following drivers:

• PostgreSQL (via github.com/lib/pq)
• MySQL (via github.com/go-sql-driver/mysql)
• SQLite (via github.com/mattn/go-sqlite3)
• SQL Server (via github.com/microsoft/go-mssqldb)

Connection strings are handled by the dburl (opens in a new tab) library.

Functions

connect

connect(dsn string, opts object) conn

Connect to the database specified by the DSN string. An optional options object can be provided to configure the connection.

Connection Options

>>> conn := sql.connect("postgres://user:pass@localhost:5432/db", {"stream": true})
>>> rows := conn.query("SELECT * FROM users")
>>> defer rows.close()
>>> for i, row := range rows {
...     print(i, row.name)
... }

Types

conn

The conn object is a connection to a SQL database.

Methods

exec

exec(sql string, args ...object) nil

Execute a SQL statement that doesn't return rows (like INSERT, UPDATE, DELETE). Arguments may be referenced positionally ($1, $2, etc.) or with placeholders depending on the database type.

>>> conn.exec("INSERT INTO users (name) VALUES ($1)", "Alice")

query

query(sql string, args ...object) row_iterator|list

Runs a query on the server and returns the results.

Recommended Usage (Streaming Mode)

When the connection is created with {"stream": true}, returns a row_iterator that can be used with range to iterate over the results one row at a time. This is memory-efficient for large result sets and the recommended approach.

>>> rows := conn.query("SELECT id, name FROM users")
>>> defer rows.close()
>>> for i, row := range rows {
...     print(i, row.id, row.name)
... }

The row_iterator object supports the following methods:

• next(): Advances the iterator and returns the next row, or nil if there are no more rows.
• close(): Closes the iterator and releases resources.
• entry(): Returns an iterator entry for the current row or nil if there are no more rows.

close

close()

Close the connection.

>>> conn.close()

Legacy Usage (Non-Streaming Mode)

The following documentation describes the legacy non-streaming mode, which loads all results into memory at once. This approach is simple but not recommended for large result sets.

connect (Legacy)

>>> conn := sql.connect("postgres://user:pass@localhost:5432/db")
>>> conn.query("SELECT * FROM users")
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]
>>> conn.close()

query (Legacy)

When streaming is not enabled (default), the query method returns a list of all records generated by the query. This loads all results into memory at once, which can be inefficient for large result sets.

>>> conn.query("SELECT * FROM users")
[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]

Connection String Examples

Below are some examples of connection strings for different database types:

PostgreSQL

sql.connect("postgres://username:password@localhost:5432/database")

MySQL

sql.connect("mysql://username:password@localhost:3306/database")

SQLite

sql.connect("sqlite:/path/to/database.db")
sql.connect("sqlite://:memory:") // In-memory database

SQL Server

sql.connect("sqlserver://username:password@localhost:1433/database")