Skip to main content

Databases

Give an agent a SQL database: upload a SQLite file or connect a PostgreSQL server.

SQLitePostgreSQL
Use it whenYour data fits in one file and changes rarelyYour data lives in a running server and changes often
How you connectUpload the .db file with FileUploader and pass the download link it returnsPass a connection string
Writescommit action; your local file is unchangedquery action; changes the live database
Asset pathaixplain/sqlite/aixplain (Marketplace card)aixplain/postgresql/aixplain (Marketplace card)

Try it with SQLite​

Build a small database, upload it, and query it through the tool.

import sqlite3
from aixplain import Aixplain
from aixplain.v2 import FileUploader

aix = Aixplain(api_key="YOUR_API_KEY")

conn = sqlite3.connect("shop.db")
conn.execute("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
conn.executemany("INSERT INTO products VALUES (?, ?, ?)", [
    (1, "Laptop", 999.99), (2, "Monitor", 249.00), (3, "Keyboard", 79.50),
])
conn.commit()
conn.close()

db_url = FileUploader(api_key="YOUR_API_KEY").upload("shop.db", return_download_link=True)

db = aix.Tool(
    name="Shop database",
    description="Products table: id, name, price.",
    integration="aixplain/sqlite/aixplain",
    config={"url": db_url},
).save()

result = db.run(action="query", data={"query": "SELECT name, price FROM products WHERE price < 300"})
print(result.data)
Show output

Connect PostgreSQL​

Keep the connection string in an environment variable, then create the tool and run a query.

import os

pg = aix.Tool(
    name="Customer database",
    description="Read-only access to the customer database.",
    integration="aixplain/postgresql/aixplain",
    config={"url": os.environ["POSTGRES_URL"]},
).save()

result = pg.run(action="query", data={"query": "SELECT version()"})
print(result.data)
Show output

Rows come back as a string of Python tuples without column names, so select columns in the order you want to read them.

The connection string uses the standard format, postgresql://USER:PASSWORD@HOST:5432/DATABASE?sslmode=require. URL-encode special characters in the password. save() does not test the connection, so a wrong host or password shows up on the first run().

Actions​

ActionSQLitePostgreSQL
queryRuns a read statement and returns the rows as a Markdown table.Runs any statement, including writes, and returns the rows as a string of Python tuples.
commitRuns a statement that changes data, such as INSERT or UPDATE.Not available; use query.
schemaReturns the CREATE TABLE statements for every table.Returns the tables and column names.

Run queries​

Pass the SQL in a dict under query; a bare string is rejected. The schema action returns the table definitions, which you can paste into an agent's instructions.

result = db.run(action="schema")
print(result.data)
Show output

Write data​

In SQLite, run INSERT, UPDATE and DELETE through commit; the query action does not apply changes. Later queries on the same tool see the change, and your local shop.db stays as it was.

db.run(action="commit", data={"query": "INSERT INTO products VALUES (4, 'Mouse', 25.00)"})

result = db.run(action="query", data={"query": "SELECT COUNT(*) AS products FROM products"})
print(result.data)
Show output

In PostgreSQL, query runs writes with the permissions of the user in the connection string. Add RETURNING to see the rows you changed. This example writes to a temporary table, so it leaves the database unchanged:

result = pg.run(action="query", data={"query": """
    CREATE TEMP TABLE notes (id serial, body text);
    INSERT INTO notes (body) VALUES ('hello') RETURNING id, body
"""})
print(result.data)
Show output

Use with an agent​

Agents currently work with the SQLite tool; for PostgreSQL, call pg.run from your code. Put the table schema in instructions so the agent writes correct SQL without guessing column names.

db.allowed_actions = ["query", "schema"]

agent = aix.Agent(
    name="Shop analyst",
    description="Answers questions about the shop's products.",
    instructions="Query the products table (id, name, price) to answer.",
    tools=[db],
).save()

result = agent.run("Which products cost less than $300?")
print(result.data.output)
Show output
warning

An agent's first call to the SQLite tool can arrive without the SQL statement and fail, and the agent does not always retry. Check result.data.steps for a failed query step before you trust an answer.

Use a read-only user​

When a PostgreSQL tool only needs to read, connect it as a user that can only read:

CREATE USER readonly_user WITH PASSWORD 'CHANGE_ME';
GRANT CONNECT ON DATABASE mydb TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Put readonly_user in the connection string, and load the string from an environment variable or a secrets manager, never from version control.

Troubleshooting​

SymptomCauseFix
Could not validate inputs for 'query'data was a string.Pass data={"query": "..."}.
Missing database url in data objectconfig["url"] is empty, for example an unset environment variable.Pass the FileUploader link or set the variable before you create the tool.
Invalid file type. Only .db files are supportedThe SQLite URL does not point to a .db file.Save the database with a .db extension before you upload it.
could not translate host name, connection refused or timeoutThe PostgreSQL host is wrong or does not accept connections from aixplain.Fix the host, open port 5432 to aixplain, then create the tool again.
permission denied for tableThe PostgreSQL user lacks a grant.Grant SELECT, and USAGE on the schema, to the user.
The agent's SQL names the wrong columnsThe agent does not know the schema.Add the output of the schema action to instructions.

Clean up​

agent.delete()
db.delete()
pg.delete()

Next steps​