Tools

This page describes the tools level, which is always on and which no setting turns off. All it needs is a .picker.json that names a server and says how to connect to it. With nothing more in the file, you get every database the login can reach, and every query goes through the same gate.

pick does three kinds of work, called levels. Each level is a section of .picker.json with the same name, and you add the second and third only when a project needs them.

A tools-only file, and what it gives you

A tools-only file is a connection and nothing else. It names each server under tools and gives each one a connect, which says how to reach the server. Here is the smallest useful file, beside the answer pick catalogs gives for it.

{
  "tools": {
    "servers": {
      "main": {
        "connect": "env:MAIN_SQL"
      }
    }
  }
}

levels
  tools       on, always; settings from C:\github\app\.picker.json
  guardrails  off
  screenings  off

main  (connect: env:MAIN_SQL)
  every database this login can reach; guardrails are off, so none is declared
source: C:\github\app\.picker.json

• The levels block comes first, with one line per level. Each line says whether that level is on and which file turned it on.
• The server line shows where the connection string comes from. It shows the variable's name, or (local file), and never the string itself.
• The next line says the server is open. An open server is one whose databases pick finds by asking the server, because no file declares them.
• catalogs opens no connection, so it cannot name the databases. Run pick objects to see the tables and views inside them.

Where the connection comes from

The best place for the connection string is an environment variable. "connect": "env:MAIN_SQL" tells pick to read the string from the variable MAIN_SQL when a call needs it, so nothing secret sits in any file. Set the variable before you start pick, or before you start the assistant that runs it.

$env:MAIN_SQL = "Server=localhost;Integrated Security=true;TrustServerCertificate=true"
pick catalogs

export MAIN_SQL="Server=localhost;User ID=pick_reader;Password=...;TrustServerCertificate=true"
pick catalogs

• The string can go in a second file instead. .picker.local.json sits beside .picker.json, and you keep it out of source control. The local file explains what it may hold.
• A password in the checked-in file refuses the whole file, because that file ends up in every copy of the project. The message tells you to move it to the local file or to an environment variable.
• A server with no connect anywhere also refuses the file, and the message names the server so you know which one to fix.

Which databases are there

Every online database the login can enter is there. The login is the account pick connects as. pick asks the server for this list each time a call needs it, so a database made a minute ago shows up on the next call.

The list leaves out the four system databases: master, tempdb, model and msdb. They hold the server's own state, not anybody's data. If you need one, declare it under guardrails.

Each database on the list allows all three grant words: list, select and describe. list lets objects show a table, select lets a query read its rows, and describe lets a caller read its columns and keys.

The login decides how long the list is. A login that can enter two databases gives a list of two, which is one reason to connect as a login with only the rights pick needs. The login shows the grants.

The query gate

The query gate reads every statement before it reaches the server. It is the same at every level, because guardrails do not change it and no setting turns it off.

The gate first parses the statement. To parse is to break the text into its parts, such as the tables it names and the columns it asks for. The gate then checks each part against a list of parts it knows. If any part is not on that list, the gate refuses the whole statement.

What passes is rewritten before it runs. Every table name becomes its full three-part name, Database.schema.object. SELECT * becomes the list of columns, and the answer shows that list. The reference lists every refusal.

How a name is found

There is no default database. pick never asks the connection which database it is in, and looks for every name in the databases on the list instead.

• A three-part name, such as Sales.dbo.Customers, names one database. If that database is not on the list, the answer is the same as for a table that does not exist.
• A shorter name, such as Customers, is looked for in every database on the list.
• A name found in two places is refused, and the message names both places. Write the full name to fix it.
• At this level every reachable database is part of that search. A login that reaches many databases meets this refusal more often. Guardrails shorten the list, which shortens the search.
• One statement reaches one server. A query may join databases on the same server, but it may not join two servers.

$ pick select "SELECT TOP 5 * FROM Customers"
pick: 'Customers' is in more than one place: Archive.dbo.Customers, Sales.dbo.Customers. Qualify it (Customers)
$ pick select "SELECT TOP 5 * FROM Sales.dbo.Customers"

How a name resolves has the full rules.

The caps

Every answer has a size limit, and the answer says when it hit one. The limits keep one answer from filling the assistant's context, which is the text it can hold at once.

The five verbs

The verbs are the same on the command line and through the MCP server, which is the part of pick an assistant talks to. Its answer to a verb is the same as the answer to pick VERB --json.

What describe tells you

describe reads the server's catalog and never runs the object. For each column it gives the type and whether the column may be empty (null), and then it lists the indexes and foreign keys.

$ pick describe Sales.dbo.Orders
Sales.dbo.Orders  (table)
  OrderId  int not null
  CustomerId  int not null
  PlacedOn  date not null
  index PK_Orders (primary key): OrderId
  fk FK_Orders_Customers: CustomerId -> Sales.dbo.Customers (CustomerId)
  hash: 6f1c...

• The last line is a shape hash, a short code worked out from the answer. If the table changes, the code changes, so you can tell when to look again.
• A view gives its columns and never its text. The text of a view names the tables under it, which may be the very thing a scope hides.

DESCRIBE, in detail is in the reference.

Check it with doctor

Run pick doctor after you write the file. It opens with the three level lines, then knocks on each server, which means it opens a connection and closes it again.

$ pick doctor
pick X.Y.Z
  ok tools: on, always; settings from C:\github\app\.picker.json
  ok guardrails: off
  ok screenings: off
  ok configuration: C:\github\app\.picker.json
  ok server main: connects (guardrails off: every database the login can reach)

A level that is off is one line, and its checks do not run. The MCP server tells the assistant the same thing, because its opening note says which levels are on for that launch.

To give an assistant these verbs, register pick with it. pick has no setup command, so registering it is one entry you write by hand, as Installing It shows.

A file from an older version

The older shape, with servers or models at the top, is refused. The message holds your own entries, rewritten under the three sections, so to fix the file you paste that part of the message over it.

pick: .picker.json has "servers" at the top, which is the shape this version no longer
reads. Rewrite it with three sections, one per level. Your entries, rewritten:
{"tools":{"servers":{"main":{"connect":"env:MAIN_SQL"}}},"guardrails":{"servers":{...}}}

• An unknown section at the top is refused too. The message names the three sections the file takes: tools, guardrails and screenings.
• The tools section takes servers, and each server takes connect. A databases entry under tools is refused. The message shows where it goes under guardrails.

When every reachable database is too many, turn on guardrails. The next page shows how to declare the databases you want and hide the rest.