Your first query
A tasks table and three queries; every INTEGER column becomes int64 and RETURNING hands back the new row.
Runs in browser
Examples for
SQLite
41 examples, in learning order. 23 run fully in your browser. Type affinity is mapped to Go types; the stock driver is pure Go.
- Parameters
- ?1 or :name, @name, $name
- RETURNING
- Supported
- Go code for
- database/sql
- Migrations
- Built in (SQLite)
- Driver
- modernc.org/sqlite
Getting started
4 examples001 002 Choosing a command: one, many, exec, execrows, execresult
One query per command kind on a harbour's boats, from execresult's LastInsertId to exec's bare error.
Runs in browser003 Placeholders, inferred names and Params structs
A ferry timetable whose Params structs take their field names from inserted and compared columns, binding lines and named placeholders.
Runs in browser004 Nullability through joins, COALESCE and aggregates
Ringed gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.
Runs in browser
Types and nullability
3 examples005 From SQL types to Go types
One landing of fish read back column by column, printing the Go type each declared SQLite type became.
Runs in browser006 JSON columns
Chip shop orders kept as JSON documents, encoded into the generated JSON type and searched with -> and ->>.
Runs in browser009 Type overrides
A chip shop menu read back as its own Status and Pence types, through a column override in sgl.yaml and sgl.column in a migration and in queries.
Runs in browser
Dynamic queries
5 examples010 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.
Runs in browser011 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.
Runs in browser012 Validated ORDER BY
Regatta entries listed in the order a caller picks from generated constants, with raw text adding no ORDER BY at all.
Runs in browser013 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.
Runs in browser014 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.
Runs in browser
Shaping results
5 examples015 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.
Runs in browser016 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.
Runs in browser017 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.
Runs in browser018 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.
Runs in browser019 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.
Runs in browser
Writing data
3 examples020 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.
Runs in browser021 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.
Runs in browser022 Batch and copyfrom
A batch query takes a slice of Params and writes it as multi-row INSERTs, 249 four-column rows a statement to stay under 999 bind variables.
Runs in browser
Connections and transactions
4 examples023 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.
Runs in browser024 Read replicas
NewWithReplica sends each query to the writer or the reader by Seagull's read-only detection; a mode=ro connection to the same file stands in for the replica.
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.
Runs in browser026 Query names for tracing
go.query_names generates QueryName, and a DBTX that wraps the connection and implements WrapDBTX names every query in its trace, inside RunInTx as well as outside.
Runs in browser
Migrations and seeds
3 examples027 Migrations at start-up
The migration files are embedded in the binary, and each start-up runs migrate.NewMigrator with the SQLite backend and Up, which applies only what the database lacks.
028 The migration lifecycle
UpTo, Up, Down, Status and Validate on an embedded set of migrations, with a migration that has no down file and an applied file edited afterwards.
029 Seeds, namespaces and hooks
A namespaced migrator with before-run and after-migration hooks, a migration that must run outside a transaction, and a Seeder that applies each seed file once.
Engine features
1 examples040 SQLite extensions
A harbour guide searched by FTS5 full text, by R*Tree point-in-box and by json_each over a JSON array, with a generated column SQLite keeps up to date.
Tooling
5 examples041 sgl vet and diagnostics
sgl vet reports a warning in the project and an error with a caret and a suggestion in broken/, --strict fails on the warning, sgl generate writes every query but the broken one, and sgl explain describes a code.
042 Seagull in CI
A CI job catches a query edited without regenerating, prints the diff, reports the change's warning as a GitHub annotation and as SARIF, and validates the migrations against a throwaway SQLite file.
043 Describe queries and diff schemas
sgl describe prints a query's parameters, columns and Go signature, schema dump prints the schema the migrations build, and schema diff finds a hand-made change in a SQLite file.
044 Formatting directives and prefixes
sgl fmt lists, checks and rewrites directives written with the seagull. alias, require_canonical turns the alias into Q050 hints, a directive with another tool's prefix draws Q051 until a one-off configuration adopts it, and --rewrite-prefix switches spelling.
045 Migrations from the command line
sgl init writes a starter project, then sgl migrates a SQLite file up, down and to a version, validates it, applies seeds once each, and numbers the next migration with migrate new.
Embedding Seagull
8 examples046 Generating from Go
A Go program builds a generator with dialects.New("sqlite") and a type override, generates the store package, writes it with WriteFiles, and has a check mode that reports what WriteFiles would change.
047 Vet and the catalogue from Go
A small linter vets the queries from Go, prints each one's parameters, columns, nullability and whether it only reads, adds a rule of its own, prints the catalogue's tables, and shows which error sentinels each kind of failure matches.
048 Custom functions
slugify is a Go function registered with modernc.org/sqlite; declared through custom_functions or WithCustomFunctions, Seagull types the queries that call it, and the generated methods run it inside SQLite.
049 White-label presets
A tool that embeds Seagull white-labels it: FormatDirectives converts the queries to the acme. prefix, WithPreset generates with Acme's header, helper and error names and query-names file, nothing generated says sgl, and WithLegacyHeaders prunes the file the stock header marked.
050 Schema from a live database
With no migration files, WithSchemaSource(schema_source_sqlite) reads the tables of a live SQLite database through PRAGMA, and the generator types the queries against them; schema_from in sgl.yaml does the same for sgl.
051 Observing the generator
One observe.ObserverPort prints every stage of a generation and a migration run as an indented tree, and an slog logger at Seagull's trace level shows the generator's and migrator's log, with a fixed clock so nothing changes between runs.
052 A custom emitter
An emit.EmitterPort wraps the database/sql emitter and adds query_index.go, a list of every query with its command and whether it only reads, written with the style's header so WriteFiles prunes it like any generated file.
053 Your own sgl
A main package calls cli.Main with WithDialect, WithEmitter and WithDriver to build an sgl whose SQLite has tighter parser limits, whose database/sql emitter also writes query_index.go, and which links the pure-Go SQLite driver.
Show 12 not available for SQLite
Some ideas belong to other engines, and some need a feature SQLite does not have. Each one says why, and where to read it instead.
- 007
Enums as generated Go types, and your own types with go_type
SQLite has no enum type. Use a CHECK constraint and a go_type override.
See it on PostgreSQL → - 008
- 030
Functions in migrations
SQLite has no CREATE FUNCTION; register a Go function instead.
See it on PostgreSQL → - 031
- 032
- 033
- 034
- 035
- 036
- 037
- 038
- 039