yesql

yesql lets you keep raw SQL in plain .sql files instead of scattering query strings through your JavaScript, then exposes each named statement (marked with a -- statementName comment header) as a callable function on a single object. Calling that function with a data object turns named parameters like :id into the positional placeholders each driver expects: $1, $2, ... for node-postgres (pg) and ? for MySQL/MariaDB, alongside the matching values array in call order.

Beyond file loading, yesql also exports standalone pg and mysql transform functions that can be used directly on any query string without a .sql file at all, plus a useNullForMissing option to substitute null instead of throwing when a named parameter isn’t supplied. It has no runtime dependencies and is meant to sit directly between your application code and whichever Postgres or MySQL client library you already use.

What You Get

• A readSqlFiles-style default export that scans a directory for .sql files and returns an object mapping each -- statementName header to its SQL body (raw, or pre-compiled for pg/mysql if options.type is set)
• Standalone yesql.pg(query) and yesql.mysql(query) functions that convert named parameters (:id, ::type casts) in any query string without requiring a file at all
• Automatic conversion of :name parameters into $1, $2, ... (pg) or ?/?? (mysql) placeholders, with a values array built in matching call order
• String-literal-aware parsing that skips over colons inside single- and double-quoted strings, PostgreSQL type casts (::int), and --//* */ comments so they aren’t mistaken for parameters
• A useNullForMissing option to substitute null for an omitted parameter instead of throwing a descriptive error

Common Use Cases

• Keeping application SQL in version-controlled .sql files instead of inline template strings scattered across route handlers
• Converting named-parameter queries (:id, :price) into node-postgres’s positional $1/$2 placeholder syntax before calling pg.query()
• Converting the same named-parameter queries into MySQL/MariaDB’s ? placeholder syntax for the mysql/mysql2 clients
• Building prepared-statement objects from raw SQL that includes PostgreSQL type casts, array ANY(...) clauses, or to_char/date-formatting calls without the parser mangling them

Under The Hood

Architecture yesql is a single-file CommonJS module (yesql.js) exporting one default function plus two named transforms (.pg, .mysql) — there are no classes, no dependency injection, and no plugin system. The default export scans a directory synchronously with fs.readdirSync/readFileSync, splits each file’s content on blank lines to find blocks beginning with a -- name comment, and reduces them into one flat name-to-SQL object; when options.type is set it runs each block through the matching transform at load time instead of returning raw text. Both pg and mysql transforms share the same core approach: strip -- and /* */ comments, split the query string around single- and double-quoted literals so embedded colons are left alone, then regex-replace the remaining :name/::name tokens into driver-specific placeholders while pushing corresponding values onto an array in call order. Because both branches reuse the same quote-splitting logic, any change to that string-splitting regex would affect pg and mysql behavior simultaneously — there is no isolation between the two output paths beyond their final placeholder format.

Tech Stack The package is plain Node.js (CommonJS require/module.exports), has zero runtime dependencies, and ships its source directly as the npm main entry with no build/bundle step. Its only devDependencies are mocha (test runner) and assert-diff (deep-equal assertions) per package.json. It targets pg (node-postgres) and MySQL/MariaDB client libraries as downstream consumers without depending on either directly — it only returns plain objects ({text, values} or {sql, values}) shaped to match each client’s .query() signature. CI historically ran via Travis with a Greenkeeper badge for dependency-update PRs; both are effectively dormant, matching the repo’s inactive status since its last commit in mid-2023.

Code Quality Tests live in a single root-level test.js executed via mocha (npm test), covering parameter substitution, PostgreSQL type casts and array ANY() clauses, missing-parameter error behavior, the useNullForMissing flag, and a range of quoting/comment edge cases (escaped backslashes, double-quoted identifiers containing colons, multi-line comments). Errors are plain Error objects with descriptive messages rather than typed/coded errors. There is no TypeScript, no shipped type definitions, and no linter or formatter configuration in the repo. Naming is short and consistent (pg, mysql, readSqlFiles) but the codebase has no automated static-analysis layer beyond the test suite itself.

API Design The public surface is deliberately small: one factory call (require('yesql')(dir, {type})) returns a plain object whose properties are the statement names themselves, so callers get direct property access to their SQL without any code-generation step. The named-parameter syntax (:name, ::type casts, ::identifier for MySQL table/column binding) mirrors conventions many developers already use when hand-writing parameterized SQL, keeping the learning curve low. The two output shapes are deliberately shaped to match pg and mysql’s own .query() call signatures, making integration close to a drop-in replacement for a raw query string. The tradeoff is no shipped type definitions (no parameter-name autocompletion) and documentation limited to a single README with runnable snippets rather than a dedicated docs site.