{"course":{"slug":"building-apps-with-ai","title":"Building Apps with AI","url":"https://addaly.com/learn/building-apps-with-ai","level":"beginner","track":"build"},"lesson":{"slug":"the-database-and-the-agent","title":"The database and the agent","url":"https://addaly.com/learn/building-apps-with-ai/the-database-and-the-agent","position":56,"of":87,"minutes":8,"answer":"An agent that can read your .env can reach production, so give it the schema and a development database, and never the connection string that holds real people's rows.","keyIdea":"An agent that can read your .env can reach production, so give it the schema and a development database, and never the connection string that holds real people's rows."},"body":{"format":"markdown","text":"## What the agent can reach\n\nAn editor agent runs in your project folder with your permissions. It can read `.env`. If `.env` holds the production connection string, because you tested a migration against production once and never changed it back, then every command the agent runs has the power to drop every table.\n\nNothing in the tool stops this. The agent is not going to decide to destroy production; it is going to decide to \"reset the database to fix the migration error\", which is the same command with a better reason. The protection is not a rule the agent reads but an arrangement where the credential is not there to be used.\n\n## The arrangement\n\n**Local `.env` holds a development database.** SQLite, a Docker Postgres, or a free hosted database that exists for development only. The production string lives on the host's settings page and in your password manager and nowhere on your disk.\n\n**Production gets a branch for the agent.** Neon, Supabase and PlanetScale can create a copy-on-write branch of the production database in seconds: same schema, same data, separate: writes to it never reach the original. An agent working against a branch can run every destructive command it likes and the cost is deleting the branch. For an app with personal data, the branch inherits it, so the anonymising script from the seed lesson applies before an agent reads it.\n\n**Roles that can only read.** Postgres lets you create a user that can `SELECT` and nothing else:\n\n```sql\nCREATE ROLE reporter LOGIN PASSWORD '...';\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;\n```\n\nThat role's connection string is safe to hand to a tool for reports, dashboards or a database MCP server that lets the agent query live data. A `DELETE` from that role is refused by the database, which does not care what the rules file said.\n\n## The forbidden list, database edition\n\nFor the rules file, in addition to the git ones:\n\n```text\nDatabase rules:\n- Never run migrate reset, db push --force-reset, or any command that drops tables.\n- Never run DELETE, UPDATE or TRUNCATE without a WHERE clause, and show me any\n  DELETE or UPDATE before running it.\n- Never edit files under migrations/ that have already been applied.\n- Ask before adding a migration; show me the generated SQL.\n```\n\nThese are a second layer, not the first. They stop the routine cases where the agent would otherwise reach for a reset; the credential arrangement stops the case where the rule was not read.\n\n## Give it the schema\n\nThe agent needs the schema to write anything useful, and the schema is not secret: it is a file in your repository. Point the rules file at it. An agent that has read `schema.ts` writes queries that match the actual columns; one that has not invents `booking.customerName` when the column is `customer_id` and spends twenty minutes on the resulting error.\n\nWhat it does not need is rows. Most database work (a new query, a migration, a fix to a join) is done correctly against a seeded development database with forty rows. Real data is needed only when the question is about real data: why a particular report is wrong, what a slow query is doing at scale. Those are the moments for the read-only role on a branch.\n\n## Reading generated SQL\n\nBefore any query the agent wrote runs against anything that matters, read it, and look for three things.\n\nA `DELETE` or `UPDATE` with no `WHERE`. It affects every row, and an ORM's `.delete()` with no filter chained on is the same statement in disguise.\n\nA query with no user scope. `SELECT * FROM bookings WHERE status = 'pending'` returns every user's pending bookings; the `user_id = ?` from the session is missing, and the reading block taught you how to find the query behind the page.\n\nString concatenation building the SQL. `\"WHERE name = '\" + name + \"'\"` is the injection hole the security block demonstrates; it looks like a shortcut and it is a door.\n\n## The recovery path, again\n\nThis lesson exists because the sequence agent, credential, reset has happened to enough people that it has a shape. The three layers (no production credential on disk, the forbidden list, a rehearsed backup) each catch what the others miss. Set them up in the first week, when there is nothing to lose, because the week when there is something to lose is the week you will not have time."},"check":{"question":"You want an agent to write a report query over real data without any risk to it. Which arrangement achieves that?","options":[{"text":"Give the agent the production connection string and tell it in the rules file to only ever run SELECT statements, never anything that writes or drops.","correct":false,"tempting":"A rule in a text file is a request, not a permission; one mistaken or misread instruction and the same credential runs a DELETE."},{"text":"A database role that can only read, on a branch or replica of production, with that role's connection string in the agent's environment.","correct":true},{"text":"Have the agent write the query against the seed database, then run it yourself in production.","correct":false,"tempting":"Safe, and the right approach for a one-off; it does not let the agent see real data at all, which is what the question asked for."},{"text":"Ask the agent to add a confirmation prompt before any destructive command.","correct":false,"tempting":"The agent is the one answering the prompt; a confirmation it controls is not a control."}]},"prev":{"slug":"backups-and-restore-once","title":"Backups, and restoring one before you need to","url":"https://addaly.com/learn/building-apps-with-ai/backups-and-restore-once"},"next":{"slug":"when-the-data-gets-big-enough-to-notice","title":"When the data gets big enough to notice","url":"https://addaly.com/learn/building-apps-with-ai/when-the-data-gets-big-enough-to-notice"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'The database and the agent', in Building Apps with AI. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/building-apps-with-ai/the-database-and-the-agent (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"The database and the agent","courseTitle":"Building Apps with AI","module":{"index":6,"slug":"the-parts-that-persist","title":"The parts that persist: data, accounts and files"},"answer":null,"keyIdea":"An agent that can read your .env can reach production, so give it the schema and a development database, and never the connection string that holds real people’s rows.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"what-the-agent-can-reach","runs":[{"text":"What the agent can reach"}]},{"t":"p","runs":[{"text":"An editor agent runs in your project folder with your permissions. It can read "},{"text":".env","code":true},{"text":". If "},{"text":".env","code":true},{"text":" holds the production connection string, because you tested a migration against production once and never changed it back, then every command the agent runs has the power to drop every table."}]},{"t":"p","runs":[{"text":"Nothing in the tool stops this. The agent is not going to decide to destroy production; it is going to decide to “reset the database to fix the migration error”, which is the same command with a better reason. The protection is not a rule the agent reads but an arrangement where the credential is not there to be used."}]},{"t":"h2","id":"the-arrangement","runs":[{"text":"The arrangement"}]},{"t":"p","runs":[{"text":"Local ","b":true},{"text":".env","b":true,"code":true},{"text":" holds a development database.","b":true},{"text":" SQLite, a Docker Postgres, or a free hosted database that exists for development only. The production string lives on the host’s settings page and in your password manager and nowhere on your disk."}]},{"t":"p","runs":[{"text":"Production gets a branch for the agent.","b":true},{"text":" Neon, Supabase and PlanetScale can create a copy-on-write branch of the production database in seconds: same schema, same data, separate: writes to it never reach the original. An agent working against a branch can run every destructive command it likes and the cost is deleting the branch. For an app with personal data, the branch inherits it, so the anonymising script from the seed lesson applies before an agent reads it."}]},{"t":"p","runs":[{"text":"Roles that can only read.","b":true},{"text":" Postgres lets you create a user that can "},{"text":"SELECT","code":true},{"text":" and nothing else:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE ROLE reporter LOGIN PASSWORD '...';\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;"},{"t":"p","runs":[{"text":"That role’s connection string is safe to hand to a tool for reports, dashboards or a database MCP server that lets the agent query live data. A "},{"text":"DELETE","code":true},{"text":" from that role is refused by the database, which does not care what the rules file said."}]},{"t":"h2","id":"the-forbidden-list-database-edition","runs":[{"text":"The forbidden list, database edition"}]},{"t":"p","runs":[{"text":"For the rules file, in addition to the git ones:"}]},{"t":"code","lang":"text","label":"Text","text":"Database rules:\n- Never run migrate reset, db push --force-reset, or any command that drops tables.\n- Never run DELETE, UPDATE or TRUNCATE without a WHERE clause, and show me any\n  DELETE or UPDATE before running it.\n- Never edit files under migrations/ that have already been applied.\n- Ask before adding a migration; show me the generated SQL."},{"t":"p","runs":[{"text":"These are a second layer, not the first. They stop the routine cases where the agent would otherwise reach for a reset; the credential arrangement stops the case where the rule was not read."}]},{"t":"h2","id":"give-it-the-schema","runs":[{"text":"Give it the schema"}]},{"t":"p","runs":[{"text":"The agent needs the schema to write anything useful, and the schema is not secret: it is a file in your repository. Point the rules file at it. An agent that has read "},{"text":"schema.ts","code":true},{"text":" writes queries that match the actual columns; one that has not invents "},{"text":"booking.customerName","code":true},{"text":" when the column is "},{"text":"customer_id","code":true},{"text":" and spends twenty minutes on the resulting error."}]},{"t":"p","runs":[{"text":"What it does not need is rows. Most database work (a new query, a migration, a fix to a join) is done correctly against a seeded development database with forty rows. Real data is needed only when the question is about real data: why a particular report is wrong, what a slow query is doing at scale. Those are the moments for the read-only role on a branch."}]},{"t":"h2","id":"reading-generated-sql","runs":[{"text":"Reading generated SQL"}]},{"t":"p","runs":[{"text":"Before any query the agent wrote runs against anything that matters, read it, and look for three things."}]},{"t":"p","runs":[{"text":"A "},{"text":"DELETE","code":true},{"text":" or "},{"text":"UPDATE","code":true},{"text":" with no "},{"text":"WHERE","code":true},{"text":". It affects every row, and an ORM’s "},{"text":".delete()","code":true},{"text":" with no filter chained on is the same statement in disguise."}]},{"t":"p","runs":[{"text":"A query with no user scope. "},{"text":"SELECT * FROM bookings WHERE status = 'pending'","code":true},{"text":" returns every user’s pending bookings; the "},{"text":"user_id = ?","code":true},{"text":" from the session is missing, and the reading block taught you how to find the query behind the page."}]},{"t":"p","runs":[{"text":"String concatenation building the SQL. "},{"text":"\"WHERE name = '\" + name + \"'\"","code":true},{"text":" is the injection hole the security block demonstrates; it looks like a shortcut and it is a door."}]},{"t":"h2","id":"the-recovery-path-again","runs":[{"text":"The recovery path, again"}]},{"t":"p","runs":[{"text":"This lesson exists because the sequence agent, credential, reset has happened to enough people that it has a shape. The three layers (no production credential on disk, the forbidden list, a rehearsed backup) each catch what the others miss. Set them up in the first week, when there is nothing to lose, because the week when there is something to lose is the week you will not have time."}]}],"check":{"question":[{"text":"You want an agent to write a report query over real data without any risk to it. Which arrangement achieves that?"}],"options":[{"text":[{"text":"Give the agent the production connection string and tell it in the rules file to only ever run SELECT statements, never anything that writes or drops."}],"correct":false,"tempting":[{"text":"A rule in a text file is a request, not a permission; one mistaken or misread instruction and the same credential runs a DELETE."}]},{"text":[{"text":"A database role that can only read, on a branch or replica of production, with that role’s connection string in the agent’s environment."}],"correct":true,"tempting":null},{"text":[{"text":"Have the agent write the query against the seed database, then run it yourself in production."}],"correct":false,"tempting":[{"text":"Safe, and the right approach for a one-off; it does not let the agent see real data at all, which is what the question asked for."}]},{"text":[{"text":"Ask the agent to add a confirmation prompt before any destructive command."}],"correct":false,"tempting":[{"text":"The agent is the one answering the prompt; a confirmation it controls is not a control."}]}],"order":[2,3,0,1],"why":null},"prev":{"slug":"backups-and-restore-once","title":"Backups, and restoring one before you need to","minutes":7},"next":{"slug":"when-the-data-gets-big-enough-to-notice","title":"When the data gets big enough to notice","minutes":8}}}