Examples for

DuckDB

24 examples, in learning order. DuckDB's driver needs cgo, so the stock sgl generates code only: migrate from your own program or a custom sgl build.

Start at 001: Your first query
Parameters
$1 or :name
RETURNING
Supported
Go code for
database/sql
Migrations
Built in (DuckDB)
All about the DuckDB dialect

Getting started

3 examples
  1. 001

    Your first query

    A tasks table and three queries; a sequence supplies the ids, RETURNING hands back the new row, and BOOLEAN and TIMESTAMP arrive as bool and time.Time.

Types and nullability

4 examples
  1. 005

    From SQL types to Go types

    One landing of fish read back column by column, printing the Go type each DuckDB column type became, from unsigned and 128-bit integers to UUIDs.

  2. 006

    JSON columns

    Chip shop orders kept as JSON documents, read into the generated JSON type and searched with -> and ->>.

  4. 008

    Arrays and lists as typed slices

    Fishing voyages with VARCHAR[] and INTEGER[] list columns read back as typed Go slices, searched with ANY and grouped with array_agg.

Dynamic queries

5 examples
  1. 010

    IN lists from Go slices

    Beach huts found and booked by passing Go slices to IN lists, including an empty slice and a slice after a scalar.

  2. 011

    Optional filters

    One boat trip search run four ways, where each optional filter is a pointer field and a nil pointer switches its filter off.

  3. 012

    Validated ORDER BY

    Regatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.

  5. 014

    The runtime query builder

    Unclaimed lost property searched with filters, an order, a limit and a count chosen at run time, with unsafe columns, operators and directions refused.

Shaping results

5 examples
  1. 015

    Nested structs with sgl.embed

    Boats read with their harbour as a nested value through an INNER JOIN and their skipper as a nil-able pointer through a LEFT JOIN.

  2. 016

    Parents with child slices

    Chip shop orders collapsed from a LEFT JOIN into one row each, with their lines as a slice, by group_by.

  3. 017

    Range over rows

    Tide gauge readings ranged over one row at a time with a range-over-func iterator, once to the end and once stopping early.

  4. 018

    Missing rows without errors

    Gulls ringed with INSERT ... ON CONFLICT DO NOTHING RETURNING and looked up by ring, both reporting a missing row as ok false.

Writing data

3 examples
  2. 021

    Insert or update

    INSERT ... ON CONFLICT DO UPDATE adds to an existing row through excluded and returns the result; DO NOTHING with execrows reports whether the insert happened.

  3. 022

    Batch and copyfrom

    A batch query takes a slice of Params and writes it as multi-row INSERTs, 16383 four-column rows a statement to stay under 65535 bind variables.

Connections and transactions

1 examples
  1. 023

    Transactions

    RunInTx commits a transfer when its function returns nil and rolls it back on an error, and WithTx binds the queries to a transaction you begin yourself.

Migrations and seeds

1 examples
  1. 027

    Migrations at start-up

    The migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the DuckDB backend and Up, which applies only what the database lacks.

Engine features

2 examples
  1. 037

    Nested types in DuckDB

    Marina boats with STRUCT, MAP, LIST and UNION columns, written from typed Go structs, maps and slices, read back whole, and reached into with subscripts and map_keys for typed results.

  2. 038

    Analytics in DuckDB

    A week of fish landings analysed with QUALIFY, FILTER, PIVOT, a scalar macro, a table macro and read_parquet over a price list the program writes with COPY TO.

Show 29 not available for DuckDB

Some ideas belong to other engines, and some need a feature DuckDB does not have. Each one says why, and where to read it instead.