Support external file embedding in seed/script SQL (for binary/text asset inlining) (redo) #6

New Issue

2026-07-15T14:10:28Z

warkanum commented

2026-07-15 14:10:28 +00:00

Problem

Seed/script SQL files executed via relspec scripts execute (pkg/writers/sqlexec + pkg/readers/sqldir) are read and executed verbatim — there is no mechanism to pull the content of an external file (e.g. a .md or .docx asset sitting alongside the SQL) into the script at execution time.

Concrete use case (origin project): we want to store report template source files as real files on disk — sql/vault/borg/data/templates/finance/*.md and *.docx — and have a seed script (sql/vault/borg/seeds/300_2_template_seed_data.sql) insert their content into a bytea/text column (via org.save_filepointer) during make migrate-apply. Today the only way to do this is to hand-encode the file content as a SQL literal (dollar-quoted text, or decode('<base64>', 'base64') for binary) and commit that generated SQL — which means the actual template file and the SQL literal can drift out of sync, and binary files bloat/mangle badly as hand-maintained SQL.

Requested feature

Support a directive in seed/script .sql files that tells the reader to inline an external file's content before sending the script to Postgres, e.g. a leading comment pragma:

-- @embed: path=../data/templates/finance/invoice.md var=:tpl_invoice_md mode=text
-- @embed: path=../data/templates/finance/invoice.docx var=:tpl_invoice_docx mode=base64

INSERT INTO org.filepointer (..., localpath, filename, jsonstore)
VALUES (..., 'templates/finance', 'invoice.md', jsonb_build_object('content', :tpl_invoice_md));

mode=text — file read as UTF-8 text, safely escaped/dollar-quoted into the substituted value.
mode=base64 — file read as bytes, base64-encoded, suitable for decode(:var, 'base64')::bytea.
Path resolution relative to the .sql file's own directory (so seeds can reference a sibling data/ folder without hardcoding absolute paths).
Should work with both pkg/readers/sqldir and the relspec scripts execute codepath (pkg/writers/sqlexec).

Alternative considered

pg_read_file/pg_read_binary_file inside the SQL itself was considered, but requires the asset to exist on the Postgres server's filesystem (not the migration client's), needs elevated privileges, and doesn't work against managed/remote Postgres — not viable for this use case.

Workaround in the meantime

We'll generate the seed .sql file's literals from the source assets via a small script invoked before make migrate-apply, and commit the generated file. Native support in relspec would let us drop that generation step and keep the seed script and the source asset as a single source of truth.

## Problem Seed/script SQL files executed via `relspec scripts execute` (pkg/writers/sqlexec + pkg/readers/sqldir) are read and executed verbatim — there is no mechanism to pull the content of an external file (e.g. a `.md` or `.docx` asset sitting alongside the SQL) into the script at execution time. Concrete use case (origin project): we want to store report template source files as real files on disk — `sql/vault/borg/data/templates/finance/*.md` and `*.docx` — and have a seed script (`sql/vault/borg/seeds/300_2_template_seed_data.sql`) insert their content into a `bytea`/`text` column (via `org.save_filepointer`) during `make migrate-apply`. Today the only way to do this is to hand-encode the file content as a SQL literal (dollar-quoted text, or `decode('<base64>', 'base64')` for binary) and commit that generated SQL — which means the actual template file and the SQL literal can drift out of sync, and binary files bloat/mangle badly as hand-maintained SQL. ## Requested feature Support a directive in seed/script `.sql` files that tells the reader to inline an external file's content before sending the script to Postgres, e.g. a leading comment pragma: ```sql -- @embed: path=../data/templates/finance/invoice.md var=:tpl_invoice_md mode=text -- @embed: path=../data/templates/finance/invoice.docx var=:tpl_invoice_docx mode=base64 INSERT INTO org.filepointer (..., localpath, filename, jsonstore) VALUES (..., 'templates/finance', 'invoice.md', jsonb_build_object('content', :tpl_invoice_md)); ``` - `mode=text` — file read as UTF-8 text, safely escaped/dollar-quoted into the substituted value. - `mode=base64` — file read as bytes, base64-encoded, suitable for `decode(:var, 'base64')::bytea`. - Path resolution relative to the `.sql` file's own directory (so seeds can reference a sibling `data/` folder without hardcoding absolute paths). - Should work with both `pkg/readers/sqldir` and the `relspec scripts execute` codepath (`pkg/writers/sqlexec`). ## Alternative considered `pg_read_file`/`pg_read_binary_file` inside the SQL itself was considered, but requires the asset to exist on the *Postgres server's* filesystem (not the migration client's), needs elevated privileges, and doesn't work against managed/remote Postgres — not viable for this use case. ## Workaround in the meantime We'll generate the seed `.sql` file's literals from the source assets via a small script invoked before `make migrate-apply`, and commit the generated file. Native support in relspec would let us drop that generation step and keep the seed script and the source asset as a single source of truth.

warkanum referenced this issue

2026-07-15 14:19:09 +00:00

Add native asset/file loader for binding local files as query parameters during migrate-apply #7

~~warkanum referenced this issue 2026-07-16 19:45:01 +00:00~~

Add native asset/file loader for binding local files as query parameters during migrate-apply #7

warkanum commented

2026-07-16 19:45:43 +00:00

Rather go with: #7
Maybe we can add this later.

Rather go with: https://git.warky.dev/wdevs/relspecgo/issues/7 Maybe we can add this later.

warkanum closed this issue

2026-07-16 19:45:43 +00:00

warkanum reopened this issue

2026-07-18 20:36:13 +00:00

warkanum commented

2026-07-18 20:36:34 +00:00

Changed my mind. We can combine this with the asset loaded

warkanum changed title from ~~Support external file embedding in seed/script SQL (for binary/text asset inlining)~~ to Support external file embedding in seed/script SQL (for binary/text asset inlining) (redo)

2026-07-19 19:25:16 +00:00

sgcommand referenced a pull request that will close this issue

2026-07-19 22:52:18 +00:00

feat(scripts): support external file embedding #12

sgcommand commented

2026-07-19 22:52:42 +00:00

Implementation complete for issue #6.

PR: #12
Branch: issue-6-external-file-embedding
Commit: 1bcdf29206

Summary:

Added -- @embed: directives for seed/script SQL.
Resolves paths relative to the SQL file containing the directive.
Supports mode=text as escaped SQL text literals and mode=base64 as base64 SQL literals for decode(..., 'base64').
Integrated embed processing into sqldir script reading and sqlexec execution via script source metadata.
Combined with the existing native asset-loader implementation from #7.

Verification:

git diff --check -> passed, no output
go test ./pkg/assetloader ./pkg/readers/sqldir ./pkg/writers/sqlexec -> passed
- ok git.warky.dev/wdevs/relspecgo/pkg/assetloader 0.011s
- ok git.warky.dev/wdevs/relspecgo/pkg/readers/sqldir 0.011s
- ok git.warky.dev/wdevs/relspecgo/pkg/writers/sqlexec 0.016s
go test ./... -> passed

Implementation complete for issue #6. PR: https://git.warky.dev/wdevs/relspecgo/pulls/12 Branch: issue-6-external-file-embedding Commit: 1bcdf29206f0927374b0f315e8a3ac58e66bd4ae Summary: - Added `-- @embed:` directives for seed/script SQL. - Resolves paths relative to the SQL file containing the directive. - Supports `mode=text` as escaped SQL text literals and `mode=base64` as base64 SQL literals for `decode(..., 'base64')`. - Integrated embed processing into sqldir script reading and sqlexec execution via script source metadata. - Combined with the existing native asset-loader implementation from #7. Verification: - `git diff --check` -> passed, no output - `go test ./pkg/assetloader ./pkg/readers/sqldir ./pkg/writers/sqlexec` -> passed - ok git.warky.dev/wdevs/relspecgo/pkg/assetloader 0.011s - ok git.warky.dev/wdevs/relspecgo/pkg/readers/sqldir 0.011s - ok git.warky.dev/wdevs/relspecgo/pkg/writers/sqlexec 0.016s - `go test ./...` -> passed

warkanum closed this issue

2026-07-20 11:09:40 +00:00

Sign in to join this conversation.

2 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: wdevs/relspecgo#6