Your first query
A tasks table on MergeTree and four queries; parameters are {name:Type} placeholders, the caller picks each id, and completing a task is an ALTER TABLE UPDATE mutation.
database/sql and native
Examples for
ClickHouse
19 examples, in learning order. Typed {name:Type} parameters, asyncexec for ALTER UPDATE and DELETE, and a native-protocol emitter.
- Parameters
- {name:Type}
- RETURNING
- Not supported
- Go code for
- database/sql, clickhouse native
- Migrations
- Built in (ClickHouse)
- Driver
- clickhouse-go
Getting started
3 examples001 003 Placeholders, inferred names and Params structs
The ferry timetable again, where every Params field takes its name and Go type from a {name:Type} placeholder and a placeholder used twice binds once.
database/sql and native004 Nullability through joins, COALESCE and aggregates
Ringed gulls and their sightings, where Nullable(String) gives a pointer and join_use_nulls decides whether a LEFT JOIN's unmatched row is NULL or each column's default.
Types and nullability
4 examples005 From SQL types to Go types
One landing of fish read back column by column, printing the Go type each ClickHouse type became, from unsigned integers to UUID, Decimal and DateTime('UTC').
006 JSON columns
Chip shop orders kept as JSON text in String columns, encoded with json.Marshal and searched with JSONExtractString and JSONExtractRaw.
007 Enums as generated Go types, and your own types with go_type
Harbour boats with an Enum8 status, read as the store.BoatsStatus type Seagull generates and then as the harbour's own domain.Status through a go_type override.
008 Arrays and lists as typed slices
Hauls of fish with Array(String) and Array(UInt32) columns, landed from Go slices and read back as []string and []uint32, filtered on the server with has().
Dynamic queries
5 examples010 IN lists from Go slices
Beach huts found and booked by passing Go slices as {name:Array(T)} parameters, including an empty slice and a slice after a scalar.
database/sql and native011 Optional filters
One boat trip search run four ways, where each optional filter is a Nullable placeholder, its field a pointer, and a nil pointer switches the filter off.
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.
013 Pagination with defaults and limits
Rock pool finds read a page at a time, with a default and a maximum page size, then searched with an optional filter and a page.
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
1 examples019 Types through CTEs, window functions and views
A gull colony read through a recursive CTE, a CTE feeding rank() and sum() window functions, and a view, with types and nullability carried through each.
Writing data
1 examples022 Batch and copyfrom
A day of tide readings written with one batch call, which the native driver sends as a single INSERT through PrepareBatch, landing as one part.
Migrations and seeds
1 examples027 Migrations at start-up
The migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the ClickHouse backend and Up, which applies only what the database lacks.
Engine features
4 examples030 Functions in migrations
A chip shop menu priced by a lambda SQL function that a migration creates with CREATE OR REPLACE FUNCTION, called like a built-in from a query.
034 MergeTree engines
A harbour's berths, daily catch and species totals kept in ReplacingMergeTree, SummingMergeTree and AggregatingMergeTree tables, read with FINAL, -Merge combinators and PREWHERE.
035 Arrays, maps and tuples in ClickHouse
Promenade kiosks with LowCardinality, Array, Map, Tuple and IPv4 columns, stored from Go values, read back and unrolled with ARRAY JOIN.
036 Asynchronous mutations
Fishing licences renewed and purged with ALTER TABLE UPDATE and DELETE declared asyncexec, made to wait with mutations_sync, beside a lightweight DELETE declared exec.
Show 34 not available for ClickHouse
Some ideas belong to other engines, and some need a feature ClickHouse does not have. Each one says why, and where to read it instead.
- 002
Choosing a command: one, many, exec, execrows, execresult
Not written for ClickHouse yet.
See it on SQLite → - 009
- 015
- 016
- 017
- 018
- 020
- 021
Insert or update
ClickHouse has no upsert statement; ReplacingMergeTree collapses rows instead.
See it on SQLite → - 023
- 024
- 025
- 026
- 028
- 029
- 031
- 032
- 033
- 037
- 038
- 039
- 040
- 041
- 042
- 043
- 044
- 045
- 046
- 047
- 048
- 049
- 050
- 051
- 052
- 053