Example of github.com/tursodatabase/libsql-client-go/libsql usage

go.mod

module experiments/libsql/libsql-example

go 1.23.0

require (
	github.com/tursodatabase/libsql-client-go v0.0.0-20240902231107-85af5b9d094d
	modernc.org/sqlite v1.33.1
)

require (
	github.com/antlr4-go/antlr/v4 v4.13.0 // indirect
	github.com/coder/websocket v1.8.12 // indirect
	github.com/dustin/go-humanize v1.0.1 // indirect
	github.com/google/uuid v1.6.0 // indirect
	github.com/hashicorp/golang-lru/v2 v2.0.7 // indirect
	github.com/mattn/go-isatty v0.0.20 // indirect
	github.com/ncruces/go-strftime v0.1.9 // indirect
	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
	golang.org/x/exp v0.0.0-20240325151524-a685a6edb6d8 // indirect
	golang.org/x/sys v0.22.0 // indirect
	modernc.org/gc/v3 v3.0.0-20240107210532-573471604cb6 // indirect
	modernc.org/libc v1.55.3 // indirect
	modernc.org/mathutil v1.6.0 // indirect
	modernc.org/memory v1.8.0 // indirect
	modernc.org/strutil v1.2.0 // indirect
	modernc.org/token v1.1.0 // indirect
)

libsql_example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"

	_ "github.com/tursodatabase/libsql-client-go/libsql"
	_ "modernc.org/sqlite"
)

const dbFile = "file:example.db"

const (
	incCounterStatementPositionalArgs            = "INSERT INTO counter(country, city, value) VALUES(?, ?, 1) ON CONFLICT DO UPDATE SET value = IFNULL(value, 0) + 1 WHERE country = ? AND city = ?"
	incCounterStatementPositionalArgsWithIndexes = "INSERT INTO counter(country, city, value) VALUES(?1, ?2, 1) ON CONFLICT DO UPDATE SET value = IFNULL(value, 0) + 1 WHERE country = ?1 AND city = ?2"
	incCounterStatementNamedArgs                 = "INSERT INTO counter(country, city, value) VALUES(:country, :city, 1) ON CONFLICT DO UPDATE SET value = IFNULL(value, 0) + 1 WHERE country = :country AND city = :city"
	incCounterStatementNamedArgs2                = "INSERT INTO counter(country, city, value) VALUES(@country, @city, 1) ON CONFLICT DO UPDATE SET value = IFNULL(value, 0) + 1 WHERE country = @country AND city = @city"
	incCounterStatementNamedArgs3                = "INSERT INTO counter(country, city, value) VALUES($country, $city, 1) ON CONFLICT DO UPDATE SET value = IFNULL(value, 0) + 1 WHERE country = $country AND city = $city"
)

func main() {
	run(dbFile)
}

func run(dbPath string) {
	db, err := sql.Open("libsql", dbPath)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to open db %s: %s", dbPath, err)
		os.Exit(1)
	}

	ctx := context.Background()

	pingContext(ctx, db)

	execContext(ctx, db, "CREATE TABLE IF NOT EXISTS counter(country TEXT, city TEXT, value INT, PRIMARY KEY(country, city)) WITHOUT ROWID")
	execContext(ctx, db, incCounterStatementPositionalArgs, "PL", "WAW", "PL", "WAW")
	execContext(ctx, db, incCounterStatementPositionalArgs, "PL", "WAW", "PL", "WAW")
	execContext(ctx, db, incCounterStatementPositionalArgs, "PL", "WAW", "PL", "WAW")
	execContext(ctx, db, incCounterStatementPositionalArgsWithIndexes, "FI", "HEL")
	execContext(ctx, db, incCounterStatementPositionalArgsWithIndexes, "FI", "HEL")
	execContext(ctx, db, incCounterStatementNamedArgs, sql.Named("country", "PL"), sql.Named("city", "WAW"))
	execContext(ctx, db, incCounterStatementNamedArgs, sql.Named("country", "FI"), sql.Named("city", "HEL"))
	execContext(ctx, db, incCounterStatementNamedArgs2, sql.Named("country", "PL"), sql.Named("city", "WAW"))
	execContext(ctx, db, incCounterStatementNamedArgs2, sql.Named("country", "FI"), sql.Named("city", "HEL"))
	execContext(ctx, db, incCounterStatementNamedArgs3, sql.Named("country", "PL"), sql.Named("city", "WAW"))
	execContext(ctx, db, incCounterStatementNamedArgs3, sql.Named("country", "FI"), sql.Named("city", "HEL"))

	// try prepared statements
	{
		stmt, err := db.Prepare("UPDATE counter SET value = value + 1 WHERE country = ? AND city = ?")
		if err != nil {
			fmt.Fprintf(os.Stderr, "failed to prepare statement %s: %s", incCounterStatementPositionalArgs, err)
			os.Exit(1)
		}
		defer stmt.Close()

		_, err = stmt.Exec("FI", "HEL")
		if err != nil {
			fmt.Fprintf(os.Stderr, "failed to execute prepared statement %s for FI: %s", incCounterStatementPositionalArgs, err)
			os.Exit(1)
		}
		_, err = stmt.Exec("PL", "WAW")
		if err != nil {
			fmt.Fprintf(os.Stderr, "failed to execute prepared statement %s for PL: %s", incCounterStatementPositionalArgs, err)
			os.Exit(1)
		}
	}
	{
		stmt, err := db.Prepare("SELECT * FROM counter")
		if err != nil {
			fmt.Fprintf(os.Stderr, "failed to prepare statement %s: %s", "SELECT * FROM counter", err)
			os.Exit(1)
		}
		defer stmt.Close()

		rows, err := stmt.Query()
		if err != nil {
			fmt.Fprintf(os.Stderr, "failed to execute prepared statement %s: %s", "SELECT * FROM counter", err)
			os.Exit(1)
		}

		for rows.Next() {
			var row struct {
				country string
				city    string
				value   int
			}

			if err := rows.Scan(&row.country, &row.city, &row.value); err != nil {
				fmt.Fprintf(os.Stderr, "failed to scan row: %s", err)
				os.Exit(1)
			}

			fmt.Println(row)
		}

		if err := rows.Err(); err != nil {
			fmt.Fprintf(os.Stderr, "errors from query: %s", err)
			os.Exit(1)
		}
	}

	rows := queryContext(ctx, db, "SELECT * FROM counter")
	for rows.Next() {
		var row struct {
			country string
			city    string
			value   int
		}

		if err := rows.Scan(&row.country, &row.city, &row.value); err != nil {
			fmt.Fprintf(os.Stderr, "failed to scan row: %s", err)
			os.Exit(1)
		}

		fmt.Println(row)
	}

	if err := rows.Err(); err != nil {
		fmt.Fprintf(os.Stderr, "errors from query: %s", err)
		os.Exit(1)
	}

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to start a transaction: %s", err)
		os.Exit(1)
	}

	// Defer a rollback in case anything fails.
	defer func() {
		if err != nil {
			err = tx.Rollback()
			if err != nil {
				log.Fatal(err)
			}
		}
	}()

	rows = txQueryContext(ctx, tx, `SELECT * FROM counter WHERE (country = 'PL' AND city = 'WAW') OR (country = 'FI' AND city = 'HEL')`) // fails unless '', number 8: https://www.sqlite.org/quirks.html

	wawValue := -1
	helValue := -1

	for rows.Next() {
		var row struct {
			country string
			city    string
			value   int
		}

		if err = rows.Scan(&row.country, &row.city, &row.value); err != nil {
			fmt.Fprintf(os.Stderr, "failed to scan row: %s", err)
			os.Exit(1)
		}

		if row.country == "PL" && row.city == "WAW" {
			wawValue = row.value
		}

		if row.country == "FI" && row.city == "HEL" {
			helValue = row.value
		}
	}

	if err = rows.Err(); err != nil {
		fmt.Fprintf(os.Stderr, "errors from query: %s", err)
		os.Exit(1)
	}

	if helValue > wawValue {
		txExecContext(ctx, tx, `INSERT INTO counter(country, city, value) VALUES('PL', 'WAW', ?) ON CONFLICT DO UPDATE SET value = ? WHERE country = 'PL' AND city = 'WAW'`, helValue, helValue) // fails unless '', number 8: https://www.sqlite.org/quirks.html
	}

	if err = tx.Commit(); err != nil {
		fmt.Fprintf(os.Stderr, "error commiting the transaction: %s", err)
		os.Exit(1)
	}
}

func pingContext(ctx context.Context, db *sql.DB) {
	if err := db.PingContext(ctx); err != nil {
		fmt.Fprintf(os.Stderr, "failed to ping db: %s", err)
		os.Exit(1)
	}
}

func execContext(ctx context.Context, db *sql.DB, stmt string, args ...any) sql.Result {
	res, err := db.ExecContext(ctx, stmt, args...)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to execute statement %s: %s", stmt, err)
		os.Exit(1)
	}

	return res
}

func queryContext(ctx context.Context, db *sql.DB, stmt string, args ...any) *sql.Rows {
	res, err := db.QueryContext(ctx, stmt, args...)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to execute query %s: %s", stmt, err)
		os.Exit(1)
	}

	return res
}

func txExecContext(ctx context.Context, tx *sql.Tx, stmt string, args ...any) sql.Result {
	res, err := tx.ExecContext(ctx, stmt, args...)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to execute statement %s: %s", stmt, err)
		os.Exit(1)
	}

	return res
}

func txQueryContext(ctx context.Context, tx *sql.Tx, stmt string, args ...any) *sql.Rows {
	res, err := tx.QueryContext(ctx, stmt, args...)
	if err != nil {
		fmt.Fprintf(os.Stderr, "failed to execute query %s: %s", stmt, err)
		os.Exit(1)
	}

	return res
}