Golang Cheatsheet
Materialize is PostgreSQL-compatible, which means that Go applications can use the standard library’s database/sql
package with a PostgreSQL driver to access Materialize as if it were a PostgreSQL database.
The pq
driver was the standard by default, but is no longer in active development. In this guide we’ll use the pgx
driver connect to Materialize and issue PostgreSQL commands.
Connect
You connect to Materialize the same way you connect to PostgreSQL with pgx
.
Local Instance
Connect to a local Materialize instance just as you would connect to a PostgreSQL instance, using either a URI or DSN connection string.
package main
import (
"context"
"github.com/jackc/pgx/v4"
"log"
)
func main() {
ctx := context.Background()
connStr := "postgres://materialize@localhost:6875/materialize?sslmode=disable"
conn, err := pgx.Connect(ctx, connStr)
if err != nil {
log.Fatal(err)
}
defer conn.Close()
}
Materialize Cloud Instance
Download your instance’s certificate files from the Materialize Cloud Connect dialog and specify the path to each file in the ssl
property.
Replace MY_INSTANCE_ID
in the property with a Materialize Cloud instance ID.
package main
import (
"context"
"github.com/jackc/pgx/v4"
"log"
"fmt"
)
func main() {
ctx := context.Background()
connStr := fmt.Sprint(
" host=MY_INSTANCE_ID.materialize.cloud",
" user=materialize",
" port=6875",
" dbname=materialize",
" sslmode=verify-full",
" sslrootcert=ca.crt",
" sslkey=materialize.key",
" sslcert=materialize.crt",
)
conn, err := pgx.Connect(ctx, connStr)
if err != nil {
log.Fatal("Trouble Connecting to the database:", err)
}
defer conn.Close()
}
To create a concurrency-safe connection pool, import github.com/jackc/pgx/v4/pgxpool
and use pgxpool.Connect()
.
The rest of this guide uses the *pgx.Conn
connection handle from the connect section to interact with Materialize.
Stream
To take full advantage of incrementally updated materialized views from a Go application, instead of querying Materialize for the state of a view at a point in time, use a TAIL
statement to request a stream of updates as the view changes.
To read a stream of updates from an existing materialized view, open a long-lived transaction with BEGIN
and use TAIL
with FETCH
to repeatedly fetch all changes to the view since the last query.
tx, err := conn.Begin(ctx)
if err != nil {
log.Fatal(err)
return
}
defer tx.Rollback(ctx)
_, err = tx.Exec(ctx, "DECLARE c CURSOR FOR TAIL my_view")
if err != nil {
log.Fatal(err)
return
}
for {
rows, err := tx.Query(ctx, "FETCH ALL c")
if err != nil {
log.Fatal(err)
tx.Rollback(ctx)
return
}
for rows.Next() {
var r tailResult
if err := rows.Scan(&r.MzTimestamp, &r.MzDiff, ...); err != nil {
log.Fatal(err)
}
fmt.Printf("%+v\n", r)
// operate on tailResult
}
}
err = tx.Commit(ctx)
if err != nil {
log.Fatal(err)
}
The TAIL Output format of tailResult
contains all of the columns of my_view
, prepended with several additional columns that describe the nature of the update. When a row of a tailed view is updated, two objects will show up in our result set:
{MzTimestamp:1646868332570 MzDiff:1 row...}
{MzTimestamp:1646868332570 MzDiff:-1 row...}
An MzDiff
value of -1
indicates Materialize is deleting one row with the included values. An update is just a deletion (MzDiff:-1
) and an insertion (MzDiff:1
) with the same MzTimestamp
.
Query
Querying Materialize is identical to querying a traditional PostgreSQL database using Go: the database object executes the query, and Materialize returns the state of the view, source, or table at that point in time.
Because Materialize maintains materialized views in memory, response times are much faster than traditional database queries, and polling (repeatedly querying) a view doesn’t impact performance.
Query a view my_view
with a select statement:
rows, err := conn.Query(ctx, "SELECT * FROM my_view")
if err != nil {
log.Fatal(err)
}
for rows.Next() {
var r result
err = rows.Scan(&r...)
if err != nil {
log.Fatal(err)
}
// operate on result
}
Insert data into tables
Most data in Materialize will stream in via a SOURCE
, but a TABLE
can be helpful for supplementary data.
For example, use a table to join slower-moving reference or lookup data with a stream.
Basic Example: Insert a row of data into a table named countries
in Materialize.
insertSQL := "INSERT INTO countries (code, name) VALUES ($1, $2)"
_, err := conn.Exec(ctx, insertSQL, "GH", "GHANA")
if err != nil {
log.Fatal(err)
}
Manage sources, views, and indexes
Typically, you create sources, views, and indexes when deploying Materialize, although it is possible to use a Go app to execute common DDL statements.
Create a source from Golang
createSourceSQL := `CREATE SOURCE market_orders_raw FROM PUBNUB
SUBSCRIBE KEY 'sub-c-4377ab04-f100-11e3-bffd-02ee2ddab7fe
CHANNEL 'pubnub-market-orders'`
_, err = conn.Exec(ctx, createSourceSQL)
if err != nil {
log.Fatal(err)
}
For more information, see CREATE SOURCE
.
Create a view from Golang
createViewSQL := `CREATE VIEW market_orders_2 AS
SELECT
val->>'symbol' AS symbol,
(val->'bid_price')::float AS bid_price
FROM (SELECT text::jsonb AS val FROM market_orders_raw)`
_, err = conn.Exec(ctx, createViewSQL)
if err != nil {
log.Fatal(err)
}
For more information, see CREATE VIEW
.
Golang ORMs
Materialize doesn’t currently support the full catalog of PostgreSQL system metadata API endpoints, including the system calls that object relational mapping systems (ORMs) like GORM use to introspect databases and do extra work behind the scenes. This means that ORM system attempts to interact with Materialize will currently fail. Once full pg_catalog
support is implemented, the features that depend on pg_catalog
may work properly.