db header in sql processor

[harshdoesdev]

What Is the Issue

The current SQL processor accepts only a single connection string. This setup is suitable for PostgreSQL and aligns with our previous approach. However, the package-query processor for SQLite previously employed a db header, allowing us to directly specify the path to the SQLite database file. One drawback of the current setup is that it restricts us to connecting to only a single SQLite database. Furthermore, this approach poses an issue with fastn.com, as it currently reads the database path from the db header. To address this, we suggest adding a new environment variable to fastn.com's project on Vercel.

Proposed Solution

We propose reintroducing the db header for the sql processor, with a higher priority. This means that, regardless of whether a FASTN_DB_URL is defined, the processor will attempt to connect to the database defined in the db header (only for SQLite database).

Example:

-- user list users:
$processor$: pr.sql
db: db2.sqlite

-- user-card: $user
for: $user in $users

Please share your thoughts on this matter, or let me know if you have any alternative solutions in mind.

[amitu]

@harshdoesdev I am leaning towards something like this: https://github.com/orgs/fastn-stack/discussions/1451#discussioncomment-7490306

-- import: <package-name>/config

-- person list people:
$processor$: pr.sql
db: $config.main-sqlite

And in FASTN.ftd:

-- import: fastn
-- fastn.package: foo
-- fastn.db-url main-sqlite: sqlite://main.sqlite3

[harshdoesdev]

I suggest reverting the changes for the package-query processor so that it will still accept the database URL from the db header. As for the sql processor, once the configuration unification has been finalized, we can use that and pass the configuration via the db header.

[Arpita-Jaiswal]

@harshdoesdev Lets revert the backward incompatible changes in package-query processor first. Also I think we should rename db header to use as proposed here and give deprecation warning for db header.

[harshdoesdev]

Sure

[amitu]

once the configuration unification has been finalized, we can use that and pass the configuration via the db header.

This is also not correct as since we currently allow db header to be a file name string, we can not change this behaviour as that would be backward incompatible change and we are committed to as much backward compatibility as possible. We will have to use the use keyword, or overload db (if we are planning to use db everywhere, it is a good name after all) we will have to check if it starts with $, in which case it is variable reference and else, only for package-query we will assume it to be file name.

Also we should deprecated package-query itself, so anyone using it should get a warning about switching to the new sql processor.

[harshdoesdev]

Got it.