Go Integration

Connect to Universql using Snowflake's official Go driver, providing high-performance database access with Go's concurrency features.

Installation

go get github.com/snowflakedb/gosnowflake

Quick Start

Basic usage example:

package main
 
import (
    "database/sql"
    "fmt"
    "log"
    
    sf "github.com/snowflakedb/gosnowflake"
)
 
func main() {
    // Configure the connection
    config := &sf.Config{
        Host:   "{universql_server}",  // Universql server
        User:      "your_username",
        Password:  "your_password",
        Database:  "your_database",
        Schema:    "your_schema",
        Warehouse: "your_warehouse",
    }
    
    dsn, err := sf.DSN(config)
    if err != nil {
        log.Fatal(err)
    }
    
    // Open connection
    db, err := sql.Open("snowflake", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // Execute a query
    rows, err := db.Query(`
        SELECT 
            DATE_TRUNC('month', created_at) as month,
            COUNT(*) as user_count,
            COUNT(DISTINCT country) as countries
        FROM users
        GROUP BY 1
        ORDER BY 1 DESC
        LIMIT 12
    `)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
    
    for rows.Next() {
        var month string
        var userCount, countries int
        if err := rows.Scan(&month, &userCount, &countries); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("Month: %s, Users: %d, Countries: %d\n", 
            month, userCount, countries)
    }
}

Connection Pool Configuration

Configure connection pooling for better performance:

import (
    "time"
    "context"
)
 
func setupDB() *sql.DB {
    config := &sf.Config{
        Host:   "{universql_server}",  // Universql server
        User:      "your_username",
        Password:  "your_password",
        Database:  "your_database",
        Schema:    "your_schema",
        Warehouse: "your_warehouse",
    }
    
    dsn, err := sf.DSN(config)
    if err != nil {
        log.Fatal(err)
    }
    
    db, err := sql.Open("snowflake", dsn)
    if err != nil {
        log.Fatal(err)
    }
 
    // Set connection pool settings
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
 
    // Verify connection
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    
    if err := db.PingContext(ctx); err != nil {
        log.Fatal(err)
    }
 
    return db
}

Structured Data with Types

Use Go structs with Snowflake:

type Product struct {
    ID        int64     `db:"id"`
    Name      string    `db:"name"`
    Price     float64   `db:"price"`
    Category  string    `db:"category"`
    CreatedAt time.Time `db:"created_at"`
}
 
func getTopProducts(db *sql.DB) ([]Product, error) {
    rows, err := db.Query(`
        SELECT id, name, price, category, created_at
        FROM products
        WHERE price > 100
        ORDER BY price DESC
        LIMIT 10
    `)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
 
    var products []Product
    for rows.Next() {
        var p Product
        if err := rows.Scan(&p.ID, &p.Name, &p.Price, &p.Category, &p.CreatedAt); err != nil {
            return nil, err
        }
        products = append(products, p)
    }
    return products, nil
}

Transactions

Handle transactions in Go:

func transferMoney(db *sql.DB, fromID, toID int, amount float64) error {
    ctx := context.Background()
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()
 
    // Deduct from source account
    if _, err := tx.ExecContext(ctx, `
        UPDATE accounts 
        SET balance = balance - ?
        WHERE id = ?
    `, amount, fromID); err != nil {
        return err
    }
 
    // Add to destination account
    if _, err := tx.ExecContext(ctx, `
        UPDATE accounts 
        SET balance = balance + ?
        WHERE id = ?
    `, amount, toID); err != nil {
        return err
    }
 
    return tx.Commit()
}

Batch Operations

Perform batch operations efficiently:

func batchInsertProducts(db *sql.DB, products []Product) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
 
    stmt, err := tx.Prepare(`
        INSERT INTO products (name, price, category)
        VALUES (?, ?, ?)
    `)
    if err != nil {
        return err
    }
    defer stmt.Close()
 
    for _, p := range products {
        if _, err := stmt.Exec(p.Name, p.Price, p.Category); err != nil {
            return err
        }
    }
 
    return tx.Commit()
}

Context Support

Use context for timeouts and cancellation:

func queryWithTimeout(db *sql.DB) error {
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
 
    var result string
    err := db.QueryRowContext(ctx, `
        SELECT result 
        FROM long_running_query
    `).Scan(&result)
    
    if err != nil {
        return err
    }
    return nil
}

Error Handling

Handle Snowflake-specific errors:

import "github.com/snowflakedb/gosnowflake"
 
func handleError(err error) {
    if snowflakeErr, ok := err.(*sf.SnowflakeError); ok {
        fmt.Printf("Snowflake Error Code: %d\n", snowflakeErr.Number)
        fmt.Printf("SQL State: %s\n", snowflakeErr.SQLState)
        fmt.Printf("Message: %s\n", snowflakeErr.Message)
    } else {
        fmt.Printf("Regular error: %v\n", err)
    }
}

Concurrent Operations

Leverage Go's concurrency features:

func processDataConcurrently(db *sql.DB, batchIDs []int) error {
    results := make(chan error, len(batchIDs))
    
    for _, batchID := range batchIDs {
        go func(id int) {
            _, err := db.Exec(`
                UPDATE batches
                SET processed = true
                WHERE batch_id = ?
            `, id)
            results <- err
        }(batchID)
    }
    
    for range batchIDs {
        if err := <-results; err != nil {
            return err
        }
    }
    
    return nil
}

Best Practices

Use Environment Variables

config := &sf.Config{
    Host: "{universql_server}",  // Universql server
    User:    os.Getenv("SNOWFLAKE_USER"),
    Password: os.Getenv("SNOWFLAKE_PASSWORD"),
}

Handle Connection Lifecycle

func main() {
    db, err := setupDB()
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
}

Use Query Parameters

// Good
db.Query("SELECT * FROM users WHERE id = ?", userID)
 
// Avoid
db.Query(fmt.Sprintf("SELECT * FROM users WHERE id = %d", userID))