Returns the contents of a file as a UTF-8 text string.
Signature
sqlpage.read_file_as_text(file_path TEXT) -> TEXT
Parameters
Path to the file to read, relative to the web root directory
Return Value
The file contents as text, or NULL if the parameter is NULL
Description
The read_file_as_text() function reads a file from the filesystem and returns its contents as a UTF-8 text string. The file path is relative to the web root directory (configured via web_root in sqlpage.json).
If the file is not found on disk and you’re using a sqlpage_files table, SQLPage will attempt to read from the database.
Examples
Render Markdown File
Display a markdown file as formatted text:
SELECT 'text' as component,
sqlpage.read_file_as_text('README.md') as contents_md;
Include HTML Template
Embed HTML content from a file:
SELECT 'text' as component,
sqlpage.read_file_as_text('templates/header.html') as html;
Load Configuration File
Read a JSON configuration file:
SET config = sqlpage.read_file_as_text('config.json');
SELECT 'text' as component,
'App Name: ' || json_extract($config, '$.app_name') as contents;
Display Code with Syntax Highlighting
Show source code with syntax highlighting:
SELECT 'code' as component;
SELECT
'example.sql' as title,
'sql' as language,
sqlpage.read_file_as_text('examples/example.sql') as contents;
Include Reusable Text Snippets
Load reusable text content:
SELECT 'hero' as component,
'Welcome' as title,
sqlpage.read_file_as_text('content/welcome-message.txt') as description;
Process CSV Data
Read and parse CSV files:
SET csv_content = sqlpage.read_file_as_text('data/users.csv');
-- Process CSV (example for SQLite)
SELECT 'table' as component;
SELECT * FROM (
SELECT value as csv_line
FROM json_each('["' || REPLACE(REPLACE($csv_content, CHAR(10), '","'), ',', '","') || '"]')
);
Load SQL Queries
Read SQL from external files:
SET query = sqlpage.read_file_as_text('queries/complex_report.sql');
-- Execute the loaded SQL
SELECT 'table' as component;
-- Note: You can't directly execute the string, but you can use run_sql
SELECT 'dynamic' as component,
sqlpage.run_sql('queries/complex_report.sql') as properties;
Multi-Language Support
Load localized text:
SET lang = COALESCE(:lang, 'en');
SET welcome_text = sqlpage.read_file_as_text('i18n/' || $lang || '/welcome.txt');
SELECT 'text' as component,
$welcome_text as contents;
Process Uploaded Text File
Read an uploaded text file:
SET uploaded_path = sqlpage.uploaded_file_path('document');
SET file_content = sqlpage.read_file_as_text($uploaded_path);
INSERT INTO documents (name, content)
VALUES (
sqlpage.uploaded_file_name('document'),
$file_content
);
File Path Resolution
Relative Paths
Paths are relative to the web root:
-- If web_root is /var/www/myapp
SELECT sqlpage.read_file_as_text('data.txt');
-- Reads: /var/www/myapp/data.txt
SELECT sqlpage.read_file_as_text('docs/readme.md');
-- Reads: /var/www/myapp/docs/readme.md
Absolute Paths
Absolute paths read from the filesystem:
-- Read from absolute path
SELECT sqlpage.read_file_as_text('/etc/app/config.txt');
-- Reads: /etc/app/config.txt
Database Files
When using sqlpage_files table:
-- First checks filesystem, then database
SELECT sqlpage.read_file_as_text('stored-in-db.txt');
-- Files in database
CREATE TABLE sqlpage_files (
path TEXT PRIMARY KEY,
contents BLOB,
last_modified TIMESTAMP
);
INSERT INTO sqlpage_files (path, contents, last_modified)
VALUES ('stored-in-db.txt', 'File content here', CURRENT_TIMESTAMP);
Error Handling
File Not Found
If the file doesn’t exist, an error is displayed:
-- This will show an error if file doesn't exist
SELECT sqlpage.read_file_as_text('nonexistent.txt');
Graceful Handling
Check file existence first (if needed):
-- You can't check existence directly, but you can handle NULL
SET file_content = sqlpage.read_file_as_text('optional.txt');
SELECT 'text' as component,
COALESCE($file_content, 'File not available') as contents;
Non-UTF8 Files
The file must be valid UTF-8:
-- This will error if file contains invalid UTF-8
SELECT sqlpage.read_file_as_text('binary-data.bin');
-- Use read_file_as_data_url for binary files instead
SELECT sqlpage.read_file_as_data_url('binary-data.bin');
Use Cases
1. Content Management
Manage page content in text files:
SET page = COALESCE(:page, 'home');
SET content = sqlpage.read_file_as_text('content/' || $page || '.md');
SELECT 'text' as component,
$content as contents_md;
2. Email Templates
Load email templates:
SET template = sqlpage.read_file_as_text('emails/welcome.txt');
SET personalized = REPLACE($template, '{{name}}', :user_name);
-- Send email with personalized content
3. API Response Templates
Use template files for API responses:
SET template = sqlpage.read_file_as_text('api/response-template.json');
SET response = json_set(
$template,
'$.user_id', :user_id,
'$.timestamp', datetime('now')
);
SELECT 'json' as component;
SELECT $response as contents;
4. Documentation System
Build a documentation viewer:
SET doc_path = COALESCE(:doc, 'index');
SET doc_content = sqlpage.read_file_as_text('docs/' || $doc_path || '.md');
SELECT 'shell' as component,
'Documentation' as title;
SELECT 'text' as component,
$doc_content as contents_md;
Security Considerations
Path Traversal
Be careful with user-provided paths:
-- DANGEROUS - User can read any file!
SELECT sqlpage.read_file_as_text(:user_provided_path);
-- SAFER - Validate and sanitize
SET safe_filename = REPLACE(REPLACE(:filename, '..', ''), '/', '');
SET content = sqlpage.read_file_as_text('uploads/' || $safe_filename || '.txt');
Whitelist Approach
Only allow specific files:
SET allowed_files = json_array('readme.md', 'license.txt', 'changelog.md');
SELECT 'redirect' as component, '/error.sql' as link
WHERE json_extract($allowed_files, '$[' || :file_index || ']') IS NULL;
SET file_name = json_extract($allowed_files, '$[' || :file_index || ']');
SELECT sqlpage.read_file_as_text('docs/' || $file_name);
- Files are read from disk on each call
- Consider caching frequently-read files in a database table
- Large files (>1MB) may impact response time
- Use
sqlpage.run_sql() for including SQL files
-- Cache file content in database
INSERT INTO file_cache (path, content, cached_at)
VALUES ('large-file.txt', sqlpage.read_file_as_text('large-file.txt'), CURRENT_TIMESTAMP)
ON CONFLICT (path) DO UPDATE SET
content = EXCLUDED.content,
cached_at = EXCLUDED.cached_at
WHERE cached_at < datetime('now', '-1 hour');
-- Use cached version
SET content = (SELECT content FROM file_cache WHERE path = 'large-file.txt');
See Also