I product the opposite day, which I feel could also be excellent for AI-related use circumstances. Ghost, from ghost.construct, describes itself as “the primary database constructed for brokers.”
Ghost is an “agent-first” Postgres database platform that lets builders and AI brokers create, fork, examine, question, manipulate and delete complete databases with ease.
It’s additionally utterly free to make use of.
The builders who constructed it had a easy thought: if brokers are going to construct software program, take a look at migrations, examine schemas, run SQL, and experiment with knowledge, then they want databases which can be as disposable and programmable as code sandboxes. That’s the place Ghost matches.
Once you create a Ghost database or fork present ones, these databases reside on Ghost’s Cloud infrastructure, not in your native system. Ghost is very helpful for testing, prototyping, agent workflows, department databases, migration experiments, and disposable database environments.
Conventional managed databases are designed round long-lived manufacturing infrastructure. You create an occasion, configure networking, handle credentials, join functions, after which deal with the database as one thing precious and fragile. Ghost retains the ability of Postgres, however provides a workflow that feels a lot nearer to trendy agentic improvement: create a database on demand, fork it whenever you want an remoted copy, run SQL in opposition to it, examine the schema, strive completely different desk configurations, and throw it away when you find yourself accomplished.
This makes Ghost particularly well-suited to AI instruments like Codex and Claude Code. These instruments can motive about code, write migrations, debug queries, generate seed knowledge, examine logs, and use MCP instruments. Ghost’s built-in MCP server offers Codex direct database administration capabilities, moderately than forcing the agent to depend on imprecise directions, copied connection strings, or guide dashboard work.
In the remainder of this text, I’ll take you thru tips on how to set up Ghost in your native system. We’ll then have a look at 4 concrete examples of utilizing Ghost with the Codex agent and its personal CLI.
N.B. I’ve no affiliation or affiliation with the corporate or crew behind Ghost or the Ghost product itself.
Stipulations
I’m assuming you have already got one among Ghost’s supported coding brokers put in in your system. These embrace:
Claude Code
Codex
Cursor
Gemini CLI
Google Antigravity
Kiro CLI
VS Code
WindsurfAdditionally, you will want a GitHub account, which you will want to grant Ghost entry to when logging in.
Putting in Ghost
On Linux, WSL on Home windows, or macOS, you need to use the next curl command.
$ curl -fsSL https://set up.ghost.construct | shIf, like me, you’re on Home windows, you need to use this command from a PowerShell terminal.
PS C:Usersthoma> irm https://set up.ghost.construct/set up.ps1 | iexThen run,
PS C:Usersthoma> ghost login
Opening browser for authentication...
Discovered house: nj5scy2orp
Efficiently logged in as [email protected]You’ll be offered with a display screen like this. Authorise Ghost to proceed.
After you’re logged in appropriately, the following factor it’s best to do is make the Ghost MCP server obtainable to your favorite coding agent. In my case, I’m utilizing Codex.
Kind the next into the command line and select your agent from the displayed checklist.
PS C:Usersthoma> ghost mcp set up
Choose an MCP consumer to configure:
1. Claude Code
> 2. Codex
3. Cursor
4. Gemini CLI
5. Google Antigravity
6. Kiro CLI
7. VS Code
8. Windsurf
Typing: 2
Efficiently put in Ghost MCP server configuration for codex
Configuration file: C:Usersthoma.codexconfig.toml
Subsequent steps:
1. Restart codex to load the brand new configuration
2. The Ghost MCP server can be obtainable as 'ghost'
PS C:Usersthoma>Test that your setup is OK
- Kind within the following to verify that the Ghost MCP is put in appropriately.
PS C:Usersthoma> ghost mcp checklist
TYPE NAME
immediate design-postgis-tables
immediate design-postgres-tables
immediate find-hypertable-candidates
immediate migrate-postgres-tables-to-hypertables
immediate pgvector-semantic-search
immediate postgres
immediate postgres-hybrid-text-search
immediate setup-timescaledb-hypertables
instrument ghost_connect
instrument ghost_create
instrument ghost_create_dedicated
instrument ghost_delete
instrument ghost_feedback
instrument ghost_fork
instrument ghost_fork_dedicated
instrument ghost_list
instrument ghost_login
instrument ghost_logs
instrument ghost_password
instrument ghost_pause
instrument ghost_rename
instrument ghost_resume
instrument ghost_schema
instrument ghost_sql
instrument ghost_status
instrument search_docs
instrument view_skillTest that your agent can use the Ghost MCP server. You must see output just like this (for Codex).
PS C:Usersthoma> codex mcp checklist
Title Command Args Env Cwd Standing Auth
ghost C:UsersthomaAppDataLocalProgramsGhostghost.exe mcp begin - - enabled UnsupportedAssuming every part is OK, we are able to transfer on to extra examples of utilizing Ghost through its CLI and brokers by way of its MCP server.
Instance 1 — Making a gross sales knowledge database
Open up your coding agent and sort the next into it.
Codex> Create a ghost database known as sales_data that shops 10,000 dummy gross sales
information. Within the database, create whichever tables are appropriate for such
knowledge. Don't embrace any indexes, major or overseas keys for now.I acquired this output from Codex.
Labored for 1m 32s
Created a contemporary Ghost database named sales_data.
It comprises 10,000 dummy gross sales order information and supporting tables:
clients 1,000
merchandise 150
sales_reps 30
sales_orders 10,000
sales_order_items 30,000
Per your instruction, I didn't create any:
major keys
overseas keys
indexes
Verification confirmed there aren't any major key constraints, no overseas key
constraints, and no indexes on the gross sales tables.As you possibly can see, on my system, this took simply over a minute and a half, which is a fairly spectacular statistic for what was achieved.
Instance 2 — Utilizing the Ghost CLI
Now that we have now a database to play with, let’s check out among the Ghost CLI instructions. Word that these instructions will usually not have to be used since we are able to do all that I’m about to indicate you through Codex and the Ghost MCP, but it surely’s helpful to know the way in any case.
PS C:Usersthoma> ghost checklist
ID NAME STATUS STORAGE
sip3qcs40a sales_data operating 211MiB- See the database tables and construction
PS C:Usersthoma> ghost schema sales_data
DATABASE: sales_data (sip3qcs40a)
TABLE: clients
customer_id BIGINT GENERATED ALWAYS AS IDENTITY
customer_name TEXT NOT NULL
e mail TEXT NOT NULL
country_code TEXT NOT NULL CHECK ((size(country_code) = 2))
section TEXT NOT NULL CHECK ((section = ANY (ARRAY['consumer'::text, 'small_business'::text, 'enterprise'::text])))
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
TABLE: merchandise
product_id BIGINT GENERATED ALWAYS AS IDENTITY
sku TEXT NOT NULL
product_name TEXT NOT NULL
class TEXT NOT NULL CHECK ((class = ANY (ARRAY['software'::text, 'hardware'::text, 'services'::text, 'training'::text, 'support'::text])))
base_price NUMERIC(10,2) NOT NULL CHECK ((base_price > (0)::numeric))
energetic BOOLEAN NOT NULL DEFAULT true
TABLE: sales_order_items
sales_order_item_id BIGINT GENERATED ALWAYS AS IDENTITY
sales_order_id BIGINT NOT NULL
product_id BIGINT NOT NULL
amount INTEGER NOT NULL CHECK (((amount >= 1) AND (amount <= 6)))
unit_price NUMERIC(10,2) NOT NULL CHECK ((unit_price > (0)::numeric))
discount_amount NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK ((discount_amount >= (0)::numeric))
line_total NUMERIC(12,2) NOT NULL CHECK ((line_total >= (0)::numeric))
TABLE: sales_orders
sales_order_id BIGINT GENERATED ALWAYS AS IDENTITY
customer_id BIGINT NOT NULL
sales_rep_id BIGINT NOT NULL
order_status TEXT NOT NULL CHECK ((order_status = ANY (ARRAY['pending'::text, 'completed'::text, 'shipped'::text, 'cancelled'::text, 'refunded'::text])))
ordered_at TIMESTAMP WITH TIME ZONE NOT NULL
payment_method TEXT NOT NULL CHECK ((payment_method = ANY (ARRAY['card'::text, 'bank_transfer'::text, 'paypal'::text, 'invoice'::text])))
currency_code TEXT NOT NULL DEFAULT 'USD'::textual content CHECK ((currency_code = 'USD'::textual content))
subtotal_amount NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK ((subtotal_amount >= (0)::numeric))
tax_amount NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK ((tax_amount >= (0)::numeric))
shipping_amount NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK ((shipping_amount >= (0)::numeric))
total_amount NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK ((total_amount >= (0)::numeric))
TABLE: sales_reps
sales_rep_id BIGINT GENERATED ALWAYS AS IDENTITY
rep_name TEXT NOT NULL
area TEXT NOT NULL CHECK ((area = ANY (ARRAY['north_america'::text, 'europe'::text, 'asia_pacific'::text, 'latin_america'::text])))
hired_at DATE NOT NULL
VIEW: pg_buffercache
bufferid INTEGER
relfilenode OID
reltablespace OID
reldatabase OID
relforknumber SMALLINT
relblocknumber BIGINT
isdirty BOOLEAN
usagecount SMALLINT
pinning_backends INTEGER
VIEW: pg_buffercache_numa
bufferid INTEGER
os_page_num BIGINT
numa_node INTEGER- Choose some knowledge from the sales_order desk
PS C:Usersthoma> ghost sql sales_data "SELECT * FROM sales_orders LIMIT 10;"
sales_order_id │ customer_id │ sales_rep_id │ order_status │ ordered_at │ payment_method │ currency_code │ subtotal_amount │ tax_amount │ shipping_amount │ total_amount
────────────────┼─────────────┼──────────────┼──────────────┼───────────────────────────────┼────────────────┼───────────────┼─────────────────┼────────────┼─────────────────┼──────────────
1 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1796.88 │ 143.75 │ 0.00 │ 1940.63
2 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1622.63 │ 129.81 │ 0.00 │ 1752.44
3 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1867.25 │ 149.38 │ 0.00 │ 2016.63
4 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 606.02 │ 48.48 │ 0.00 │ 654.50
5 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1450.11 │ 116.01 │ 0.00 │ 1566.12
6 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1227.85 │ 98.23 │ 0.00 │ 1326.08
7 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 7086.55 │ 566.92 │ 0.00 │ 7653.47
8 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 696.92 │ 55.75 │ 0.00 │ 752.67
9 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 1063.73 │ 85.10 │ 0.00 │ 1148.83
10 │ 8 │ 9 │ accomplished │ 2024-05-21 05:08:34.332998+00 │ paypal │ USD │ 2489.06 │ 199.12 │ 0.00 │ 2688.18
(10 rows)- Anxious about your agent trashing your database? Straightforward, simply set your system to read-only
PS C:Usersthoma> ghost config set read_only true
Now in Codex,
Codex> delete from sales_order_items the place sales_order_item_id = 12
Labored for 5s
The DELETE was blocked, as anticipated:
ERROR: can not execute DELETE in a read-only transaction (SQLSTATE 25006)There’s an entire bunch of helpful instructions you need to use with the ghost CLI. For an entire checklist, sort in ghost — assist into your command line.
However, as I mentioned earlier than, one of many important causes to make use of Ghost is that we are able to manipulate databases utilizing our AI agent. Let’s see that in motion.
Instance 3 — Utilizing the Ghost MCP with our agent.
We noticed already in instance 1 how fast and straightforward it was to create a database utilizing Codex, however let’s present how straightforward it’s to experiment with databases utilizing an agent.
- Add 500,000 information to the sales_order_items desk
Codex> Add 500,000 dummy gross sales order information to the sales_order_items desk
Added 500,000 dummy rows to sales_data.sales_order_items.
Remaining counts:
sales_orders 10,000
merchandise 150
sales_order_items 530,000
I additionally recalculated the ten,000 order totals from the expanded line-item desk.
Totals now:
Subtotal sum: 1,530,141,808.73
Tax sum: 124,981,128.08
Whole gross sales: 1,639,903,892.69
Avg order worth: 163,990.39
Confirmed the tables nonetheless have 0 indexes.- Choose some knowledge from the sales_order_id desk
Codex> Retrieve the sales_order_items report for
sales_order_items_id = 519932 and inform me how lengthy it took to retrieve
Report discovered:
sales_order_item_id: 519932
sales_order_id: 9932
product_id: 15
amount: 3
unit_price: 91.47
discount_amount: 0.00
line_total: 274.41
Timing:
Database execution time: 25.833 ms
Ghost MCP end-to-end time: 146.733 ms
As a result of there aren't any indexes, Postgres used a sequential scan and filtered by way of 529,999 rows to search out the report.- Now for some efficiency tuning
Codex> Fork 3 duplicate sales-data databases in parallel, naming them
sales_data-1, and so forth.... Utilizing differering methods in every fork, tune the
database in order that the above question runs extra shortly. Output your ends in
an ascii desk with database identify, tuning technique and database execution time.
I am going to create three forks concurrently, then apply a special lookup technique
to every fork: a plain btree index, a masking btree index, and a bodily
clustered desk plus index. Then I am going to measure the identical EXPLAIN ANALYZE
execution time on every.
Used Ghost
Solely the supply sales_data exists, so there aren't any fork-name collisions.
I am launching the three fork/tune/measure employees now; every employee waits solely
for its personal fork earlier than tuning it.
Labored for 2m 40s
Created and tuned three forked databases in parallel.
Baseline on untuned sales_data was beforehand 25.833 ms with a sequential scan.
+--------------+---------------------------------------------------------------+--------------------------+
| Database | Tuning technique | Database execution time |
+--------------+---------------------------------------------------------------+--------------------------+
| sales_data-1 | Btree index on sales_order_item_id | 0.131 ms |
| sales_data-2 | Protecting btree index together with all projected question columns | 0.143 ms |
| sales_data-3 | Hash index on sales_order_item_id for equality lookup | 0.158 ms |
+--------------+---------------------------------------------------------------+--------------------------+
Finest outcome: sales_data-1 with a typical btree index at 0.131 ms.As you possibly can see, the speedup after making use of the tuning was fairly vital, as we’d anticipate. Even so, a +200x enchancment within the question runtime will not be too shabby.
Instance 4 — Constructing a dashboard app for our database
This instance exhibits how straightforward it’s to mix an app constructed with coding brokers with a database constructed with coding brokers.
Codex> Use your efficiency tuning findings to improve the sales_data
database. Then assemble a dashboard utilizing javascript and HTML for the
database. The dashboard must be dynamic. I would like to have the ability to filter on knowledge
and the dashboard ought to solely present knowledge that's contained in the database.
I am going to flip this right into a small reside dashboard app: an Specific backend with
read-only Postgres queries, and a browser UI that fetches filtered knowledge
from /api/dashboard. I am going to maintain the database URL in an surroundings file
moderately than placing credentials within the HTML.
Ran 3 instructions
The workspace already has pg; I am going to add specific and dotenv, then fetch a
read-only Ghost connection string for the app.
Ran 1 command, used Ghost
I am going to keep away from placing the database password into the HTML. The server will get a
read-only connection from the Ghost CLI at startup if DATABASE_URL will not be
set, and all browser requests will undergo parameterized API endpoints.
Ran & 'C:UsersthomaAppDataLocalProgramsGhostghost.exe' join --read-only sales_data
The read-only Ghost connection string works and consists of sslmode=require
plus Ghost's protected read-only choice. I am including the backend and changing
the HTML with a reside API-driven dashboard.
The backend API is in place. I am changing the dashboard HTML with a
frontend that calls /api/choices and /api/dashboard every time filters change.
Created 1 file
The reside frontend and backend are written. I am beginning the server now and
checking the API endpoints earlier than I hand you the URL.
The server is now operating on port 5101. I am verifying the browser HTML and a
filtered API name in opposition to the reside database.
Labored for 7m 28s
Finished. The dashboard is now a reside browser app backed by the Ghost sales_data
database.
Run/open it right here:
http://localhost:5101/sales_dashboard.html
What modified:
Added server.js with an Specific API.
Up to date sales_dashboard.html so filters name the API and redraw charts/tables.
Up to date package deal.json with npm begin.
The backend will get a Ghost read-only connection through ghost join --read-only sales_data.
Browser JavaScript by no means sees the database password.
Once I clicked the HTML hyperlink, I acquired a dynamic dashboard that responded precisely to completely different filter values.
Abstract
Hopefully, I’ve proven you sufficient of Ghost’s database’s capabilities to whet your urge for food to search out out extra about this handy product.
The convenience and velocity with which it’s attainable to create, destroy, copy and manipulate Postgres databases utilizing this instrument is exceptional.
My favorite side of Ghost is how straightforward it’s to run what-if situations with actual databases. Want some new copies of an present database to strive issues out? It’s a snap. You possibly can chop and alter indexes and first keys, or create materialised views, simply by describing what you need in plain English utilizing your favorite agentic coding instruments, with a fallback to a CLI if you really want to.
As I confirmed in my remaining instance, as a result of Ghost works so nicely with agentic coding instruments its fairly simple to have these brokers create code that integrates nicely with Ghost databases to supply helpful apps like dashboards and different forms of SaaS functions.
Now, though you would possibly need to pause earlier than storing any manufacturing knowledge on Ghost, it’s a fantastic platform for Proofs of Idea and common experimentation. And, after getting a database construction you’re proud of utilizing Ghost, replicating that in your manufacturing database must be simple.
For extra info on utilizing Ghost and full documentation, please go to the officlal dwelling web page under.
