Compare · 022

Batch and copyfrom: CockroachDB and SQLite

Every file of the CockroachDB and SQLite versions side by side, with the lines that differ marked.

Compare CockroachDB withSQLite PostgreSQL TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

CockroachDB
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tides
4  - name: tides
5    dialect: postgres
5    dialect: sqlite
6    migrations: db/migrations
6    migrations: db/migrations
7    queries: db/queries
7    queries: db/queries
8    go:
8    go:
9      package: store
9      package: store
10      out: internal/store
10      out: internal/store
11      driver: database/sql
12    migrate:
13      dsn: ${env:DATABASE_URL}

Migrations

db/migrations/0001_readings.up.sqldiffers

CockroachDB
SQLite
1CREATE TABLE readings (
1CREATE TABLE readings (
2    station   TEXT   NOT NULL,
2    id        INTEGER PRIMARY KEY,
3    minute    BIGINT NOT NULL,
3    station   TEXT    NOT NULL,
4    height_cm BIGINT NOT NULL,
4    minute    INTEGER NOT NULL,
5    note      TEXT,
5    height_cm INTEGER NOT NULL,
6    PRIMARY KEY (station, minute)
6    note      TEXT
7);
7);

db/migrations/0001_readings.down.sqlidentical

CockroachDB
SQLite
1DROP TABLE readings;
1DROP TABLE readings;

Queries

RecordReadingsdiffers

CockroachDB · db/queries/readings.sql
SQLite · db/queries/readings.sql
1-- sgl.query(RecordReadings, batch)
1-- sgl.query(RecordReadings, batch)
2INSERT INTO readings (station, minute, height_cm, note) VALUES ($1, $2, $3, $4);
2INSERT INTO readings (station, minute, height_cm, note) VALUES (?, ?, ?, ?);

SummariseReadingsidentical

CockroachDB · db/queries/readings.sql
SQLite · db/queries/readings.sql
1-- sgl.query(SummariseReadings, many)
1-- sgl.query(SummariseReadings, many)
2SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
2SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
3FROM readings
3FROM readings
4GROUP BY station
4GROUP BY station
5ORDER BY station;
5ORDER BY station;

What sgl generates

RecordReadingsidentical

CockroachDB
SQLite
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
2
2
3type RecordReadingsParams struct {
3type RecordReadingsParams struct {
4	Station  string
4	Station  string
5	Minute   int64
5	Minute   int64
6	HeightCm int64
6	HeightCm int64
7	Note     *string
7	Note     *string
8}
8}

SummariseReadingsidentical

CockroachDB
SQLite
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
2
2
3type SummariseReadingsRow struct {
3type SummariseReadingsRow struct {
4	Station  string `json:"station"`
4	Station  string `json:"station"`
5	Readings int64  `json:"readings"`
5	Readings int64  `json:"readings"`
6	Noted    int64  `json:"noted"`
6	Noted    int64  `json:"noted"`
7}
7}

The program

example.godiffers

CockroachDB
SQLite
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"fmt"
6	"fmt"
7	"io"
7	"io"
8
8
9	"seagull.sh/seagull/examples/postgres/022_bulk_writes/internal/store"
9	"seagull.sh/seagull/examples/sqlite/022_bulk_writes/internal/store"
10)
10)
11
11
12const (
12const (
13	// minutesPerWeek is how many readings each tide gauge takes in a week.
13	// minutesPerDay is how many readings each tide gauge takes in a day.
14	minutesPerWeek = 7 * 24 * 60
14	minutesPerDay = 24 * 60
15)
15)
16
16
17// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
17// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
18// QueryContext and QueryRowContext come from the embedded *sql.DB.
18// QueryContext and QueryRowContext come from the embedded *sql.DB.
19type statementCounter struct {
19type statementCounter struct {
20	*sql.DB
20	*sql.DB
21
21
22	// statements is how many times ExecContext has been called.
22	// statements is how many times ExecContext has been called.
23	statements int
23	statements int
24}
24}
25
25
26// ExecContext counts the statement, then runs it on the database.
26// ExecContext counts the statement, then runs it on the database.
27//
27//
28// Takes query (string) which is the SQL to run.
28// Takes query (string) which is the SQL to run.
29// Takes arguments (...any) which are its bind values.
29// Takes arguments (...any) which are its bind values.
30//
30//
31// Returns sql.Result which is the database's result.
31// Returns sql.Result which is the database's result.
32// Returns error when the statement fails.
32// Returns error when the statement fails.
33func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
33func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
34	counter.statements++
34	counter.statements++
35	return counter.DB.ExecContext(ctx, query, arguments...)
35	return counter.DB.ExecContext(ctx, query, arguments...)
36}
36}
37
37
38// run records a week of readings from two tide gauges in one batch call, shows how many
38// run records a day of readings from two tide gauges in one batch call, shows how many
39// INSERT statements that took, and summarises what landed in the table.
39// INSERT statements that took, and summarises what landed in the table.
40//
40//
41// Takes database (*sql.DB) which is a migrated database.
41// Takes database (*sql.DB) which is a migrated database.
42// Takes output (io.Writer) which receives the statement counts and the summary.
42// Takes output (io.Writer) which receives the statement counts and the summary.
43//
43//
44// Returns error when a query fails.
44// Returns error when a query fails.
45func run(ctx context.Context, database *sql.DB, output io.Writer) error {
45func run(ctx context.Context, database *sql.DB, output io.Writer) error {
46	counter := &statementCounter{DB: database}
46	counter := &statementCounter{DB: database}
47	queries := store.New(counter)
47	queries := store.New(counter)
48
48
49	// An empty batch is a no-op: nothing is sent to the database.
49	// An empty batch is a no-op: nothing is sent to the database.
50	if err := queries.RecordReadings(ctx, nil); err != nil {
50	if err := queries.RecordReadings(ctx, nil); err != nil {
51		return fmt.Errorf("recording no readings: %w", err)
51		return fmt.Errorf("recording no readings: %w", err)
52	}
52	}
53	fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)
53	fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)
54
54
55	readings := tideReadings("harbour", "pier")
55	readings := tideReadings("harbour", "pier")
56
56
57	// A batch query takes a slice of Params, one per row. On database/sql the generated
57	// A batch query takes a slice of Params, one per row. On database/sql the generated
58	// method writes them as multi-row INSERTs, renumbering the placeholders $1, $2 and on,
58	// method writes them as multi-row INSERTs, chunked so that no statement binds more than
59	// chunked so that no statement binds more than 65535 variables, the most the PostgreSQL
59	// 999 variables, the default limit of SQLite before 3.32: four columns a row allows 249
60	// protocol can carry: four columns a row allows 16383 rows a statement, so a week from
60	// rows a statement. copyfrom generates the same code on database/sql.
61	// two gauges takes two. copyfrom generates the same code on database/sql; only the pgx
62	// driver uses COPY.
63	//
61	//
64	// Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
62	// Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
65	// every row must land or none.
63	// every row must land or none.
66	if err := queries.RecordReadings(ctx, readings); err != nil {
64	if err := queries.RecordReadings(ctx, readings); err != nil {
67		return fmt.Errorf("recording readings: %w", err)
65		return fmt.Errorf("recording readings: %w", err)
68	}
66	}
69	fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)
67	fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)
70
68
71	summary, err := queries.SummariseReadings(ctx)
69	summary, err := queries.SummariseReadings(ctx)
72	if err != nil {
70	if err != nil {
73		return fmt.Errorf("summarising readings: %w", err)
71		return fmt.Errorf("summarising readings: %w", err)
74	}
72	}
75	for _, row := range summary {
73	for _, row := range summary {
76		fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
74		fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
77	}
75	}
78	return nil
76	return nil
79}
77}
80
78
81// tideReadings makes one reading a minute for a week at each station. The height rises
79// tideReadings makes one reading a minute for a day at each station. The height rises and
82// and falls in a simple saw-tooth, and the reading on the hour carries a note.
80// falls in a simple saw-tooth, and the reading on the hour carries a note.
83//
81//
84// Takes stations (...string) which name the gauges.
82// Takes stations (...string) which name the gauges.
85//
83//
86// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
84// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
87func tideReadings(stations ...string) []store.RecordReadingsParams {
85func tideReadings(stations ...string) []store.RecordReadingsParams {
88	readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerWeek)
86	readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerDay)
89	for _, station := range stations {
87	for _, station := range stations {
90		for minute := range int64(minutesPerWeek) {
88		for minute := range int64(minutesPerDay) {
91			var note *string
89			var note *string
92			if minute%60 == 0 {
90			if minute%60 == 0 {
93				note = new(fmt.Sprintf("hour %d check", minute/60))
91				note = new(fmt.Sprintf("hour %d check", minute/60))
94			}
92			}
95			readings = append(readings, store.RecordReadingsParams{
93			readings = append(readings, store.RecordReadingsParams{
96				Station:  station,
94				Station:  station,
97				Minute:   minute,
95				Minute:   minute,
98				HeightCm: 200 + minute%360,
96				HeightCm: 200 + minute%360,
99				Note:     note,
97				Note:     note,
100			})
98			})
101		}
99		}
102	}
100	}
103	return readings
101	return readings
104}
102}