Database queries #6

New Issue

nick · 2025-06-07T02:31:18Z

nick commented

2025-06-07 02:31:18 +00:00

It would be useful to add a feature to the Database class that allows executing SQL queries from external .sql files. This would enable us to version-control raw SQL queries outside of the PHP codebase, improving maintainability and separation of concerns.

Motivation:

Keeps large SQL statements out of PHP source code for better readability.
Allows SQL files to be version controlled and edited independently (e.g. in app/sql/).
Makes collaboration with DBAs or non-PHP developers easier.

Example Usage:

$db = new Database();
$db->runSqlFile('/path/to/app/sql/my_query.sql');

Suggested Implementation:

Add a method like runSqlFile(string $filePath) to the Database class that:

Loads the SQL file from disk.
Optionally validates or sanitizes it.
Executes it using the existing database connection.

Optional Enhancements:

Support for prepared statements with parameter binding from .sql files.
Error reporting with line number in the SQL file.
Ability to load multiple .sql files from a directory (e.g. for migrations or seeds).

Should gracefully handle file not found / syntax errors.
Could use PDO::exec for non-parameterized queries or PDO::prepare if enhanced.

It would be useful to add a feature to the Database class that allows executing SQL queries from external .sql files. This would enable us to version-control raw SQL queries outside of the PHP codebase, improving maintainability and separation of concerns. ## Motivation: - Keeps large SQL statements out of PHP source code for better readability. - Allows SQL files to be version controlled and edited independently (e.g. in app/sql/). - Makes collaboration with DBAs or non-PHP developers easier. ## Example Usage: ``` $db = new Database(); $db->runSqlFile('/path/to/app/sql/my_query.sql'); ``` ## Suggested Implementation: Add a method like runSqlFile(string $filePath) to the Database class that: - Loads the SQL file from disk. - Optionally validates or sanitizes it. - Executes it using the existing database connection. ## Optional Enhancements: - Support for prepared statements with parameter binding from .sql files. - Error reporting with line number in the SQL file. - Ability to load multiple .sql files from a directory (e.g. for migrations or seeds). ## Related Features / Notes: - Should gracefully handle file not found / syntax errors. - Could use PDO::exec for non-parameterized queries or PDO::prepare if enhanced.

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: 4lt/novaconium#6