concinno-skills-sql 0.1.0

Remote SQL database agent skills (Postgres + MySQL + SQLite) for Concinno — SQLAlchemy + psycopg + pymysql. DuckDbQuery (local files) in concinno core remains separate.

concinno-skills-sql

Remote SQL database skills for Concinno. Postgres, MySQL, and local SQLite via SQLAlchemy — no ORM overhead, parameterised :name bind placeholders, DDL firewall, readonly-URL toggle.

Status

MVP (0.1.0) — three tools covering the canonical "connect to a real DB" agent need. Concinno core already ships DuckDbQuery for in-process analytical SQL over local CSV / Parquet / JSON files; this sub-package is the orthogonal "talk to a running DB server" slot.

Tool	Library	Purpose
PostgresQuery	psycopg[binary] + sqlalchemy	SELECT / INSERT / UPDATE / DELETE against remote Postgres
MysqlQuery	pymysql + sqlalchemy	Same, against MySQL / MariaDB
SqliteQuery	stdlib sqlite3 + sqlalchemy	Same, against a local .db file or :memory:

Install

pip install concinno-skills-sql

All three drivers come in as hard dependencies. If you know you only need one, follow the optional-install path below.

License notes — psycopg[binary] is LGPL-3.0

This package itself is Apache-2.0. However psycopg (the Postgres driver) is licensed under LGPL-3.0. We depend on it as a pip dependency — a dynamic link — so the re-link rights the LGPL requires are preserved for any downstream consumer that pip-installs SQL skills the normal way.

If you statically bundle this package into a single-file binary (e.g. PyInstaller --onefile) the bundle's redistribution terms must satisfy LGPL-3.0 §6 for the psycopg code path specifically. The upstream psycopg project lives at https://github.com/psycopg/psycopg.

Consumers who need a pure-MIT install path can skip the Postgres driver entirely:

pip install --no-deps concinno-skills-sql
pip install 'sqlalchemy>=2.0' 'pymysql>=1.1' 'concinno>=2.15.1'
# Only SqliteQuery and MysqlQuery work; PostgresQuery.call returns a
# friendly "psycopg not installed" error.

Safety

Every tool routes through a shared _safety module before touching the database:

1. Connection URL scheme allow-list — only postgresql[+driver], mysql[+driver], and sqlite are accepted. ftp:// / file:// / bare strings / foreign dialects are rejected up-front.
2. SQLite path guard — sqlite:///... paths must resolve under Path.home() or Path.cwd() and may not live under system roots (/etc, /Windows, /System, /Program Files, …). :memory: is allowed unconditionally.
3. DDL firewall — DROP, TRUNCATE, ALTER, CREATE, GRANT, REVOKE are regex-rejected in both query and exec paths. Use your DB's migration tool (Alembic / Flyway / Liquibase / raw psql) for schema changes — agent tooling is the wrong surface.
4. Readonly toggle — append ?readonly=1 to the connection URL to make exec refuse DML, even if the connecting DB user has write privileges. Defense-in-depth.
5. Row limit — query caps at limit=100 by default, hard max 10_000. Larger pagination must use OFFSET / LIMIT inside the SQL statement.

Parameter binding (:name placeholders with a params dict) is the real defense against SQL injection. Guards above are additional belt-and-braces for the common LLM mistake of f-stringing a user input into the SQL body.

Credentials

No CredentialStore integration inside this package — connection URLs are supplied per call, which is the realistic shape for agents that juggle multiple databases in a single session.

Callers who prefer a credential indirection:

import os
from concinno_skills_sql import PostgresQuery

PostgresQuery().call(
    action="query",
    connection_url=os.environ["DATABASE_URL"],
    sql="SELECT * FROM users WHERE id = :id",
    params={"id": 42},
)

Or, via Concinno's CredentialStore:

from concinno.core.credentials import CredentialStore

cs = CredentialStore()
url = cs.resolve({"$ref": "env:DATABASE_URL"})
PostgresQuery().call(action="query", connection_url=url, sql="...")

Keeping URL resolution outside the tool means the audit trail of "which call touched which DB" lives in the caller's log, not hidden inside the Tool.

Usage via Concinno ToolRegistry

When the consumer sets CONCINNO_LOAD_PLUGINS=1, the default registry auto-mounts all three tools:

import os
os.environ["CONCINNO_LOAD_PLUGINS"] = "1"

from concinno.tools.registry import get_default_registry

reg = get_default_registry()
for name in ("PostgresQuery", "MysqlQuery", "SqliteQuery"):
    assert name in reg.list_deferred()

Direct Python usage

from concinno_skills_sql import PostgresQuery, MysqlQuery, SqliteQuery

# Postgres — SELECT with a bind parameter.
PostgresQuery().call(
    action="query",
    connection_url="postgresql+psycopg://user:[e-mail]:5432/app",
    sql="SELECT id, name, email FROM users WHERE signup_date > :since",
    params={"since": "2026-04-01"},
    limit=500,
)
# → {"rows": [...], "count": ..., "columns": ["id", "name", "email"]}

# MySQL — INSERT with a bind parameter; readonly URL blocks DML.
MysqlQuery().call(
    action="exec",
    connection_url="mysql+pymysql://u:pw@db:3306/app",
    sql="INSERT INTO audit_log (user_id, event) VALUES (:uid, :ev)",
    params={"uid": 42, "ev": "login"},
)
# → {"ok": True, "rowcount": 1}

# SQLite — a local application DB under ~/.myapp/state.db.
SqliteQuery().call(
    action="query",
    connection_url="sqlite:///~/myapp/state.db?readonly=1",
    sql="SELECT key, value FROM kv WHERE key LIKE :pat",
    params={"pat": "user:%"},
)

All tools return either {"ok": True, ...} / {"rows": ..., "count": ..., "columns": ...} on success or {"error": "..."} on any validation or library failure — matching the shape of other Concinno built-in tools.

What this package is NOT

• Not a replacement for concinno.DuckDbQuery — that one targets analytical queries over CSV / Parquet / JSON files via read_csv_auto-style pseudo-tables. Use DuckDbQuery when the data is in files; use these tools when the data is in a running DB server.
• Not a migration tool — DDL is rejected by design. Use Alembic / Flyway / Liquibase / hand-written psql for schema changes.
• Not a full ORM surface — intentionally SQL-in, rows-out. Agents needing a mapped model should call SQLAlchemy Core / ORM directly in a hand-written Tool.
• Not a secrets store — connection_url is per-call.

License

Apache-2.0. See LICENSE in the Concinno monorepo.