This guide builds a small Go application that reserves inventory in Polypheny. It updates the available quantity and creates an order in one transaction. If the order cannot be saved, the quantity stays unchanged.
Prerequisites
- Go 1.23 or newer.
- A running Polypheny instance with a Prism 2.2 interface.
- Permission to create tables and write to the default relational namespace,
public.
Create the tables
Run these SQL statements in the Polypheny UI using the public namespace. Set up the tables once before running the application.
CREATE TABLE shop_stock (
sku VARCHAR(20) PRIMARY KEY,
available INTEGER NOT NULL
);
CREATE TABLE shop_orders (
id INTEGER PRIMARY KEY,
sku VARCHAR(20) NOT NULL,
quantity INTEGER NOT NULL
);
INSERT INTO shop_stock (sku, available) VALUES ('desk-lamp', 5);
Set up the Go module
Create the application directory and install the Polypheny Go driver.
mkdir polypheny-app
cd polypheny-app
go mod init example.com/polypheny-app
go get github.com/polypheny/Polypheny-Go-Driver@v0.1.0
Reserve stock and record the order
Save the following program as main.go. The blank import registers the polypheny driver with Go’s database/sql package. The application shares one *sql.DB connection pool between the write transaction and the verification query.
package main
import (
"context"
"database/sql"
"fmt"
"log"
"os"
"strconv"
"time"
_ "github.com/polypheny/Polypheny-Go-Driver"
)
func reserveStock(ctx context.Context, db *sql.DB, orderID int, sku string, quantity int) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
result, err := tx.ExecContext(ctx,
"UPDATE shop_stock SET available = available - ? WHERE sku = ? AND available >= ?",
quantity, sku, quantity,
)
if err != nil {
return fmt.Errorf("reserve stock: %w", err)
}
updated, err := result.RowsAffected()
if err != nil {
return err
}
if updated != 1 {
return fmt.Errorf("not enough stock for %s", sku)
}
_, err = tx.ExecContext(ctx,
"INSERT INTO shop_orders (id, sku, quantity) VALUES (?, ?, ?)",
orderID, sku, quantity,
)
if err != nil {
return fmt.Errorf("create order: %w", err)
}
return tx.Commit()
}
func run() error {
if len(os.Args) != 3 {
return fmt.Errorf("usage: go run . <order-id> <quantity>")
}
orderID, err := strconv.Atoi(os.Args[1])
if err != nil || orderID <= 0 {
return fmt.Errorf("order ID must be a positive integer")
}
quantity, err := strconv.Atoi(os.Args[2])
if err != nil || quantity <= 0 {
return fmt.Errorf("quantity must be a positive integer")
}
dsn := os.Getenv("POLYPHENY_DSN")
if dsn == "" {
return fmt.Errorf("POLYPHENY_DSN must be set")
}
db, err := sql.Open("polypheny", dsn)
if err != nil {
return err
}
defer db.Close()
ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
defer cancel()
if err := db.PingContext(ctx); err != nil {
return fmt.Errorf("connect to Polypheny: %w", err)
}
const sku = "desk-lamp"
if err := reserveStock(ctx, db, orderID, sku, quantity); err != nil {
return err
}
var available int
err = db.QueryRowContext(ctx, "SELECT available FROM shop_stock WHERE sku = ?", sku).Scan(&available)
if err != nil {
return fmt.Errorf("read inventory: %w", err)
}
fmt.Printf("Order %d reserved %d units. %d units remain.\n", orderID, quantity, available)
return nil
}
func main() {
if err := run(); err != nil {
log.Fatal(err)
}
}
The condition available >= ? lets the update reserve stock only when enough units are available. The ? placeholders bind Go values as SQL parameters. Both writes go through tx, so either both commit or the deferred rollback discards the reservation.
Run and verify
The connection string format is host:port,username:password. Use the Prism TCP port, which defaults to 20590. This command assumes a local instance with the pa user and an empty password.
POLYPHENY_DSN='localhost:20590,pa:' go run . 101 2
The program prints Order 101 reserved 2 units. 3 units remain. Check the saved state from the SQL workspace.
SELECT sku, available FROM shop_stock WHERE sku = 'desk-lamp';
SELECT id, sku, quantity FROM shop_orders WHERE id IN (101, 102);
Try reserving four more units. The application reports that there is not enough stock. Run the verification queries again to confirm that three units remain and order 102 was never created.
POLYPHENY_DSN='localhost:20590,pa:' go run . 102 4
For connection options, MQL, and entity metadata, see the Polypheny Go Driver reference.
