Hosting sql files directly inside the database

[pchemguy]

What do I need to do to host files inside the database apart from placing them in the table as instructed? I use the official binaries for Windows. Can I use it for this purpose? Can I host some files on disk and some in the database? If yes, what is the search logic?

It makes sense to host small files, which are not actively edited in the database, such as images/icons. Larger files or files being actively edited might be better left on disk directly.

[lovasoa]

Hello !

• You can host files in the database just by creating and populating the following table

CREATE TABLE sqlpage_files(
  path VARCHAR(255) NOT NULL PRIMARY KEY,
  contents BLOB,
  last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• Can I host some files on disk and some in the database? Yes
• If yes, what is the search logic? serve the local file first, if not found, serve from the db

[lovasoa]

If sqlpage cannot access the files table, you should see the following in your logs:

Using local filesystem only, could not initialize on-database filesystem. 

[amrutadotorg]

Thank you. It looks like it is working since I didn't get the message.

I have one more question. If a particular file is not available in the table, will I receive a message about the fallback? I want to ensure that all files are in the table with the correct paths.

[lovasoa]

The next version will have more verbose debug logging about file system resolutions

[amrutadotorg]

Hi, now the logging looks awesome, thank you!
Is it possible to automatically serve files generated by SQLPages from a table?

for example:

[2024-06-25T10:47:10.574Z ERROR sqlpage::webserver::http] Unable to read file "sqlpage.bac98cf4929ec955.js": Unable to read "sqlpage.bac98cf4929ec955.js" from the database: 404 Not Found

[2024-06-25T06:47:21.016Z ERROR sqlpage::webserver::http] Unable to read file "sqlpage.e5b7f4b1dfa3d61c.css": Unable to read "sqlpage.e5b7f4b1dfa3d61c.css" from the database: 404 Not Found

[lovasoa]

Is it possible to automatically serve files generated by SQLPages from a table?

No it's not. SQLPage-generated files are static, they are not meant to be altered, and this allows serving them with a cache policy that allows caching them forever and never requesting them twice. This wouldn't play well with allowing users to overwrite them.