Examples for

MariaDB

23 examples, in learning order. MySQL's grammar with MariaDB's RETURNING on INSERT and DELETE.

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

Start at 001: Your first query
Parameters
? or :name
RETURNING
On INSERT and DELETE only
Go code for
database/sql
Migrations
Built in (MySQL)
Driver
go-sql-driver/mysql
All about the MariaDB dialect

Getting started

4 examples
  1. 001

    Your first query

    A tasks table and three queries; MariaDB has INSERT ... RETURNING, so the insert hands back the new row as it would on SQLite or PostgreSQL.

Types and nullability

3 examples
  1. 005

    From SQL types to Go types

    One landing of fish read back column by column, printing the Go type each MySQL column type became, unsigned integers and DECIMAL included.

    Same code as MySQL
  2. 006

    JSON columns

    Chip shop orders kept in JSON columns, encoded into the generated JSON type and searched with JSON_EXTRACT and JSON_UNQUOTE, with MariaDB handing each document back exactly as it was written.

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 MySQL
  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 MySQL
  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 MySQL
  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 MySQL
  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

4 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 MySQL
  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.

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

    Same code as MySQL

Writing data

3 examples
  1. 020

    Rows back from INSERT, UPDATE and DELETE

    INSERT and DELETE with RETURNING, declared as one, hand back the rows they wrote, defaults and computed values included; MariaDB has no UPDATE ... RETURNING, so the update is execrows and a SELECT.

  2. 021

    Insert or update

    INSERT ... ON DUPLICATE KEY UPDATE adds to an existing row through VALUES(), and its affected-row count of 1 or 2 says whether the row was inserted or updated.

    Same code as MySQL
  3. 022

    Batch and copyfrom

    A batch query takes a slice of Params and writes it as multi-row INSERTs, 16,383 four-column rows a statement to stay under MySQL's 65,535 bind variables.

    Same code as MySQL

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.

    Same code as MySQL

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 MySQL backend and Up, which applies only what the database lacks.

    Same code as MySQL

Engine features

2 examples
  1. 030

    Functions in migrations

    Stored functions created in a migration type the queries that call them, and their data-access characteristic, body or a sgl.migration(readonly:) line decides whether each query goes to the writer or the reader.

    Same code as MySQL
  2. 039

    MySQL dialect features

    A harbour register kept with INSERT IGNORE, REPLACE INTO, a SET column read with FIND_IN_SET, unsigned integers and GROUP_CONCAT, printing what each statement did.

    Same code as MySQL
Show 30 not available for MariaDB

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