Compare · 029

Seeds, namespaces and hooks: PostgreSQL and SQLite

Every file of the PostgreSQL and SQLite versions side by side, with the lines that differ marked. 1 parameter or column types differ in Go.

Compare PostgreSQL withSQLite

Configuration

sgl.yamldiffers
PostgreSQL
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: chippy
4 - name: chippy
5 dialect: postgres
5 dialect: sqlite
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 seeds: db/seeds
8 seeds: db/seeds
9 go:
9 go:
10 package: store
10 package: store
11 out: internal/store
11 out: internal/store
12 driver: database/sql
13 migrate:
12 migrate:
14 dsn: ${env:DATABASE_URL}
15 namespace: chippy
13 namespace: chippy

Migrations

db/migrations/0001_menu.up.sqldiffers
PostgreSQL
SQLite
1CREATE TABLE menu (
1CREATE TABLE menu (
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
2 id INTEGER PRIMARY KEY,
3 dish TEXT NOT NULL UNIQUE,
3 dish TEXT NOT NULL UNIQUE,
4 price_pence INTEGER NOT NULL
4 price_pence INTEGER NOT NULL
5);
5);
db/migrations/0001_menu.down.sqlidentical
PostgreSQL
SQLite
1DROP TABLE menu;
1DROP TABLE menu;
db/migrations/0002_menu_price_index.up.sqldiffers
PostgreSQL
SQLite · db/migrations/0002_write_ahead_log.up.sql
1-- sgl.migration(no_transaction: true)
1-- sgl.migration(no_transaction: true)
2CREATE INDEX CONCURRENTLY IF NOT EXISTS menu_price_idx ON menu (price_pence);
2PRAGMA journal_mode = WAL;
db/migrations/0002_menu_price_index.down.sqldiffers
PostgreSQL
SQLite · db/migrations/0002_write_ahead_log.down.sql
1DROP INDEX CONCURRENTLY IF EXISTS menu_price_idx;
1-- sgl.migration(no_transaction: true)
2PRAGMA journal_mode = DELETE;

Queries

ListMenuidentical
PostgreSQL · db/queries/menu.sql
SQLite · db/queries/menu.sql
1-- sgl.query(ListMenu, many)
1-- sgl.query(ListMenu, many)
2SELECT dish, price_pence FROM menu ORDER BY dish;
2SELECT dish, price_pence FROM menu ORDER BY dish;

What sgl generates

ListMenudiffers
PostgreSQL
SQLite
1func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error)
1func (queries *Queries) ListMenu(ctx context.Context) ([]ListMenuRow, error)
2
2
3type ListMenuRow struct {
3type ListMenuRow struct {
4 Dish string `json:"dish"`
4 Dish string `json:"dish"`
5 PricePence int32 `json:"price_pence"`
5 PricePence int64 `json:"price_pence"`
6}
6}

The program

example.godiffers
PostgreSQL
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 "io/fs"
8 "io/fs"
9 "log/slog"
10 "testing/fstest"
11
9
12 "seagull.sh/seagull/examples/postgres/029_seeds_namespaces_hooks/internal/store"
10 "seagull.sh/seagull/examples/sqlite/029_seeds_namespaces_hooks/internal/store"
13 "seagull.sh/seagull/sdk/migrate"
11 "seagull.sh/seagull/sdk/migrate"
14 "seagull.sh/seagull/sdk/migrate/migrate_backend_postgres"
12 "seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
15)
13)
16
14
17const (
15const (
18 // namespace prefixes the tables that record this program's migrations and seeds.
16 // namespace prefixes the tables that record this program's migrations and seeds.
19 namespace = "chippy"
17 namespace = "chippy"
20
21 // seedsDirectory is where the seed files sit inside the file system.
22 seedsDirectory = "db/seeds"
23)
18)
24
19
25// run migrates the database under its own namespace with hooks that report progress,
20// run migrates the database under its own namespace with hooks that report progress,
26// applies the seed files twice to show they run once, prints the seeded menu, and shows
21// applies the seed files twice to show they run once, and prints the seeded menu.
27// the warning Seagull logs when a seed file changes after it was applied.
28//
22//
29// Takes database (*sql.DB) which is an empty database.
23// Takes database (*sql.DB) which is an empty database.
30// Takes files (fs.FS) which holds db/migrations and db/seeds, the embed.FS in main.go.
24// Takes files (fs.FS) which holds db/migrations and db/seeds, the embed.FS in main.go.
31// Takes output (io.Writer) which receives each step's result.
25// Takes output (io.Writer) which receives each step's result.
32//
26//
33// Returns error when migrating, seeding or a query fails.
27// Returns error when migrating, seeding or a query fails.
34func run(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
28func run(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
35 // WithNamespace renames every table and lock the migrator and seeder keep, so several
29 // WithNamespace renames every table the migrator and seeder keep, so several programs or
36 // programs or modules can each keep their own history in one database. sgl.yaml sets
30 // modules can each keep their own history in one database. sgl.yaml sets
37 // migrate.namespace to match, so `sgl migrate` uses the same tables.
31 // migrate.namespace to match, so `sgl migrate` uses the same tables.
38 backend := migrate_backend_postgres.New(migrate.WithNamespace(namespace))
32 backend := migrate_backend_sqlite.New(migrate.WithNamespace(namespace))
39 names := backend.Names()
33 names := backend.Names()
40 fmt.Fprintf(output, "history tables: %s and %s\n", names.History, names.Seeds)
34 fmt.Fprintf(output, "history tables: %s and %s\n", names.History, names.Seeds)
41
35
42 // WithLogger sends Seagull's own log records, such as its warnings, to a logger of your
43 // choosing instead of slog.Default(). This one writes to the output and leaves out the
44 // time so the output stays the same from run to run.
45 logger := slog.New(slog.NewTextHandler(output, &slog.HandlerOptions{ReplaceAttr: withoutTime}))
46
47 // Hooks run around the migrations: before the run with the pending versions, and after
36 // Hooks run around the migrations: before the run with the pending versions, and after
48 // each migration. Returning an error from a hook stops the run.
37 // each migration. Returning an error from a hook stops the run.
49 migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations",
38 migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations",
50 migrate.WithLogger(logger),
51 migrate.WithBeforeRun(func(_ context.Context, pending migrate.MigrationRunHookContext) error {
39 migrate.WithBeforeRun(func(_ context.Context, pending migrate.MigrationRunHookContext) error {
52 fmt.Fprintf(output, "migrating: %d pending, versions %v\n", pending.PendingCount, pending.PendingVersions)
40 fmt.Fprintf(output, "migrating: %d pending, versions %v\n", pending.PendingCount, pending.PendingVersions)
53 return nil
41 return nil
54 }),
42 }),
55 migrate.WithAfterMigration(func(_ context.Context, migration migrate.MigrationHookContext) error {
43 migrate.WithAfterMigration(func(_ context.Context, migration migrate.MigrationHookContext) error {
56 fmt.Fprintf(output, " applied %04d %s\n", migration.Version, migration.Name)
44 fmt.Fprintf(output, " applied %04d %s\n", migration.Version, migration.Name)
57 return nil
45 return nil
58 }),
46 }),
59 )
47 )
60 if err != nil {
48 if err != nil {
61 return err
49 return err
62 }
50 }
63 if _, err := migrator.Up(ctx); err != nil {
51 if _, err := migrator.Up(ctx); err != nil {
64 return fmt.Errorf("migrating: %w", err)
52 return fmt.Errorf("migrating: %w", err)
65 }
53 }
66
54
67 // Migration 0002 built its index outside a transaction, as CONCURRENTLY requires.
55 // Migration 0002 ran outside a transaction, as its sgl.migration directive asks.
68 var valid bool
56 var journalMode string
69 if err := database.QueryRowContext(ctx, "SELECT indisvalid FROM pg_index WHERE indexrelid = 'menu_price_idx'::regclass").Scan(&valid); err != nil {
57 if err := database.QueryRowContext(ctx, "PRAGMA journal_mode").Scan(&journalMode); err != nil {
70 return fmt.Errorf("checking the index: %w", err)
58 return fmt.Errorf("reading the journal mode: %w", err)
71 }
59 }
72 fmt.Fprintf(output, "menu_price_idx built concurrently, valid=%t\n", valid)
60 fmt.Fprintf(output, "journal mode: %s\n", journalMode)
73
61
74 // The default namespace is a separate history in the same database, and it has recorded
62 // The default namespace is a separate history in the same database, and it has recorded
75 // nothing.
63 // nothing.
76 if err := printOtherHistory(ctx, database, files, output); err != nil {
64 if err := printOtherHistory(ctx, database, files, output); err != nil {
77 return err
65 return err
78 }
66 }
79
67
80 // A Seeder applies each seed file once and records it in the seed table, so calling
68 // A Seeder applies each seed file once and records it in the seed table, so calling
81 // Apply at every start-up is safe. The seed file also says ON CONFLICT DO NOTHING, which
69 // Apply at every start-up is safe. The seed file also says ON CONFLICT DO NOTHING, which
82 // keeps it harmless if the seed history is ever cleared.
70 // keeps it harmless if the seed history is ever cleared.
83 if err := seed(ctx, database, backend, files, logger, output); err != nil {
71 seeder, err := migrate.NewSeeder(database, backend, files, "db/seeds")
84 return err
85 }
86
87 menu, err := store.New(database).ListMenu(ctx)
88 if err != nil {
89 return fmt.Errorf("listing the menu: %w", err)
90 }
91 for _, dish := range menu {
92 fmt.Fprintf(output, " %-17s %dp\n", dish.Dish, dish.PricePence)
93 }
94 return seedEdited(ctx, database, backend, files, logger, output)
95}
96
97// seed applies the seed files twice; the second time finds nothing to do.
98//
99// Takes database (*sql.DB) which is the migrated database.
100// Takes backend (migrate.Backend) which names the seed table.
101// Takes files (fs.FS) which holds the seed files.
102// Takes logger (*slog.Logger) which receives Seagull's log records.
103// Takes output (io.Writer) which receives the counts.
104//
105// Returns error when a seed fails.
106func seed(ctx context.Context, database *sql.DB, backend migrate.Backend, files fs.FS, logger *slog.Logger, output io.Writer) error {
107 seeder, err := migrate.NewSeeder(database, backend, files, seedsDirectory, migrate.WithLogger(logger))
108 if err != nil {
72 if err != nil {
109 return err
73 return err
110 }
74 }
111 for range 2 {
75 for range 2 {
112 seeded, err := seeder.Apply(ctx)
76 seeded, err := seeder.Apply(ctx)
113 if err != nil {
77 if err != nil {
114 return fmt.Errorf("seeding: %w", err)
78 return fmt.Errorf("seeding: %w", err)
115 }
79 }
116 fmt.Fprintf(output, "seeds applied: %d\n", seeded)
80 fmt.Fprintf(output, "seeds applied: %d\n", seeded)
117 }
81 }
118 return nil
119}
120
82
121// seedEdited applies a copy of the seed files in which the applied seed has gained a
83 menu, err := store.New(database).ListMenu(ctx)
122// dish. A seed runs once, so the change is not applied; the Seeder logs a warning through
123// the logger instead, naming both checksums.
124//
125// Takes database (*sql.DB) which is the seeded database.
126// Takes backend (migrate.Backend) which names the seed table.
127// Takes files (fs.FS) which holds the seed files.
128// Takes logger (*slog.Logger) which receives the warning.
129// Takes output (io.Writer) which receives the count.
130//
131// Returns error when the copy cannot be made or the seeder fails.
132func seedEdited(ctx context.Context, database *sql.DB, backend migrate.Backend, files fs.FS, logger *slog.Logger, output io.Writer) error {
133 original, err := fs.ReadFile(files, seedsDirectory+"/0001_menu.sql")
134 if err != nil {
135 return err
136 }
137 edited := fstest.MapFS{
138 seedsDirectory + "/0001_menu.sql": {Data: append(original, "INSERT INTO menu (dish, price_pence) VALUES ('pickled egg', 120);\n"...)},
139 }
140 seeder, err := migrate.NewSeeder(database, backend, edited, seedsDirectory, migrate.WithLogger(logger))
141 if err != nil {
84 if err != nil {
142 return err
85 return fmt.Errorf("listing the menu: %w", err)
143 }
86 }
144 seeded, err := seeder.Apply(ctx)
87 for _, dish := range menu {
145 if err != nil {
88 fmt.Fprintf(output, " %-17s %dp\n", dish.Dish, dish.PricePence)
146 return fmt.Errorf("seeding the edited file: %w", err)
147 }
89 }
148 fmt.Fprintf(output, "seeds applied after editing 0001_menu.sql: %d\n", seeded)
149 return nil
90 return nil
150}
91}
151
92
152// printOtherHistory reads the same migration files against the default namespace's
93// printOtherHistory reads the same migration files against the default namespace's
153// history, which this program never wrote to.
94// history, which this program never wrote to.
154//
95//
155// Takes database (*sql.DB) which is the migrated database.
96// Takes database (*sql.DB) which is the migrated database.
156// Takes files (fs.FS) which holds the migration files.
97// Takes files (fs.FS) which holds the migration files.
157// Takes output (io.Writer) which receives the count.
98// Takes output (io.Writer) which receives the count.
158//
99//
159// Returns error when the status cannot be read.
100// Returns error when the status cannot be read.
160func printOtherHistory(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
101func printOtherHistory(ctx context.Context, database *sql.DB, files fs.FS, output io.Writer) error {
161 backend := migrate_backend_postgres.New()
102 backend := migrate_backend_sqlite.New()
162 migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations")
103 migrator, err := migrate.NewMigrator(database, backend, files, "db/migrations")
163 if err != nil {
104 if err != nil {
164 return err
105 return err
165 }
106 }
166 statuses, err := migrator.Status(ctx)
107 statuses, err := migrator.Status(ctx)
167 if err != nil {
108 if err != nil {
168 return fmt.Errorf("reading the default history: %w", err)
109 return fmt.Errorf("reading the default history: %w", err)
169 }
110 }
170 applied := 0
111 applied := 0
171 for _, status := range statuses {
112 for _, status := range statuses {
172 if status.Applied {
113 if status.Applied {
173 applied++
114 applied++
174 }
115 }
175 }
116 }
176 fmt.Fprintf(output, "%s (default namespace): %d of %d migrations applied\n", backend.Names().History, applied, len(statuses))
117 fmt.Fprintf(output, "%s (default namespace): %d of %d migrations applied\n", backend.Names().History, applied, len(statuses))
177 return nil
118 return nil
178}
119}
179
180// withoutTime drops the time from each log record, so the output is the same on every
181// run.
182//
183// Takes groups ([]string) which name the groups the attribute is in.
184// Takes attribute (slog.Attr) which is the attribute to keep or drop.
185//
186// Returns slog.Attr which is the attribute, or an empty one for the time.
187func withoutTime(groups []string, attribute slog.Attr) slog.Attr {
188 if len(groups) == 0 && attribute.Key == slog.TimeKey {
189 return slog.Attr{}
190 }
191 return attribute
192}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldPostgreSQLSQLite
ListMenucolumn price_penceint32int64