pi-mssql

Pi package that adds a mssql_query tool so the AI can query Microsoft SQL Server databases, including Microsoft Dynamics 365 Business Central / Business One databases or any standard SQL Server instance.

Install

Install from npm once published:

pi install npm:pi-mssql

Install directly from Forgejo/Git:

pi install git:https://git.otmatas.com/pi-extensions/pi-mssql.git

Or test without installing permanently:

pi -e git:https://git.otmatas.com/pi-extensions/pi-mssql.git

Local development install from this folder:

npm install
pi install ./

Local development test:

npm install
npm run typecheck
npm test
npm pack --dry-run
pi -e ./

Security first — use a read-only SQL login

Always connect with a SQL Server login that only has db_datareader permissions on the target database. Never use sa or any account with write/admin rights, even with readOnly: true enabled.

The readOnly flag in this extension is a regex-based safety net (it blocks INSERT, UPDATE, DELETE, EXEC, xp_cmdshell, etc.), but it is defense-in-depth — not a guarantee. The strongest protection is a least-privilege database user.

Example:

CREATE LOGIN pi_ro WITH PASSWORD = '...';
USE your_database;
CREATE USER pi_ro FOR LOGIN pi_ro;
ALTER ROLE db_datareader ADD MEMBER pi_ro;

Also keep trustServerCertificate: false (the default) unless you are knowingly using a self-signed certificate on a trusted network.

Configuration

This development version only reads and writes one config file:

~/.pi/agent/pi-mssql/config.json

Config files are created with restrictive file permissions where the OS supports it.

{
  "server": "192.168.1.10",
  "port": 1433,
  "user": "sql_readonly_user",
  "password": "",
  "database": "your_database",
  "encrypt": true,
  "trustServerCertificate": false,
  "readOnly": true,
  "maxRows": 100,
  "timeoutMs": 30000
}

Custom config paths, project-local config files, package-local config files, legacy paths, and environment overrides are not supported in this development version.

Read-only protection

readOnly: true is enabled by default. In this mode the extension rejects queries that do not start with SELECT or WITH, and blocks dangerous SQL such as:

• INSERT, UPDATE, DELETE, MERGE
• DROP, ALTER, CREATE, TRUNCATE
• EXEC / stored procedures
• SELECT INTO
• admin/security commands such as GRANT, REVOKE, DBCC, BACKUP, RESTORE

For best safety, also use a SQL Server login that only has read permissions. The code guard helps prevent accidental writes, but database permissions are the strongest protection.

Tool

• Tool: mssql_query
  • query: SQL query
  • database: optional database override
  • maxRows: optional result row limit, capped at 1000
  • timeoutMs: optional query timeout
  • Returns results as a compact Markdown table, with full structured data kept in tool details.

This extension does not register slash commands.

Example

Ask Pi:

Consulta a SQL Server les 10 últimes factures de venda i mostra client, data i total.

Pi can then call mssql_query with a read-only query such as:

SELECT TOP 10 *
FROM dbo.YourTable
ORDER BY PostingDate DESC;