Compare · 029

Seeds, namespaces and hooks: SQLite and PostgreSQL

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

Compare SQLite withPostgreSQL

Configuration

sgl.yamldiffers
SQLite
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: chippy
4 - name: chippy
5 dialect: sqlite
5 dialect: postgres
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
12 migrate:
13 migrate:
14 dsn: ${env:DATABASE_URL}
13 namespace: chippy
15 namespace: chippy

Migrations

db/migrations/0001_menu.up.sqldiffers
SQLite
PostgreSQL
1CREATE TABLE menu (
1CREATE TABLE menu (
2 id INTEGER PRIMARY KEY,
2 id BIGINT GENERATED BY DEFAULT AS IDENTITY 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
SQLite
PostgreSQL
1DROP TABLE menu;
1DROP TABLE menu;
db/migrations/0002_write_ahead_log.up.sqldiffers
SQLite
PostgreSQL · db/migrations/0002_menu_price_index.up.sql
1-- sgl.migration(no_transaction: true)
1-- sgl.migration(no_transaction: true)
2PRAGMA journal_mode = WAL;
2CREATE INDEX CONCURRENTLY IF NOT EXISTS menu_price_idx ON menu (price_pence);
db/migrations/0002_write_ahead_log.down.sqldiffers
SQLite
PostgreSQL · db/migrations/0002_menu_price_index.down.sql
1-- sgl.migration(no_transaction: true)
1DROP INDEX CONCURRENTLY IF EXISTS menu_price_idx;
2PRAGMA journal_mode = DELETE;

Queries

ListMenuidentical
SQLite · db/queries/menu.sql
PostgreSQL · 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
SQLite
PostgreSQL
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 int64 `json:"price_pence"`
5 PricePence int32 `json:"price_pence"`
6}
6}

The program

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

QueryFieldSQLitePostgreSQL
ListMenucolumn price_penceint64int32