Examples for

CockroachDB

25 examples, in learning order. PostgreSQL's grammar and types, under its own name.

Seagull generates the same code for CockroachDB as for PostgreSQL, so most pages here show the PostgreSQL files, tested against CockroachDB.

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

Getting started

4 examples
  1. 001

    Your first query

    The SQLite tasks program on CockroachDB; unique_rowid() fills the INT8 key, STRING and BOOL become string and bool, and the CockroachDB migration backend applies the schema.

Types and nullability

4 examples
  1. 006

    JSON columns

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

    Same code as PostgreSQL
  3. 008

    Arrays and lists as typed slices

    Fishing voyages with TEXT[] and BIGINT[] columns read back as typed Go slices, searched with ANY and grouped with array_agg.

    Same code as PostgreSQL
  4. 009

    Type overrides

    A chip shop menu read back as its own Status and Price types, through the types.decimal slot, a column override in sgl.yaml and sgl.column in a migration and in queries.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  4. 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.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL

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.

    Same code as PostgreSQL
  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; the same query and output as SQLite.

    Same code as PostgreSQL
  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, as on SQLite.

    Same code as PostgreSQL
  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; the skipped insert still uses up an id.

    Same code as PostgreSQL
  5. 019

    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; SUM of a bigint arrives as a decimal.

    Same code as PostgreSQL

Writing data

3 examples
  1. 020

    Rows back from INSERT, UPDATE and DELETE

    INSERT, UPDATE and DELETE with RETURNING, declared as one or many, hand back the rows they wrote, defaults and computed values included.

    Same code as PostgreSQL
  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.

    Same code as PostgreSQL
  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 PostgreSQL's 65535 bind variables.

    Same code as PostgreSQL

Connections and transactions

3 examples
  1. 023

    Transactions

    The PostgreSQL transfers on CockroachDB, plus what it adds; a read-then-write transaction that loses a race fails with SQLSTATE 40001, and since RunInTx does not retry, a small helper runs it again.

  2. 024

    Read replicas

    NewWithReplica sends each query to the writer or the reader by Seagull's read-only detection, which on PostgreSQL also catches FOR UPDATE, writing CTEs and calls of functions that change data; a pool with default_transaction_read_only plays the replica.

    Same code as PostgreSQL
  3. 025

    Prepared statements

    go.prepared generates Prepare, which prepares every query once at start-up and returns a PreparedDBTX that store.New runs them through.

    Same code as PostgreSQL

Migrations and seeds

1 examples
  1. 027

    Migrations at start-up

    Three replicas start at once and each runs migrate.NewMigrator with the CockroachDB backend and Up; its lock table lets one apply the migrations while the others wait and find nothing left.

Show 28 not available for CockroachDB

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