ChatGPT offers almost magical help with coding questions. You can use it to get help with SQL questions, but then you have to adapt that SQL to match your database tables and schemas.
What if ChatGPT could act as an expert on your database, and give you valid SQL that would work for you? Guess what - with a little help from SchemaCrawler, it can. You can use SchemaCrawler to "teach" ChatGPT what your database schema looks like.
SchemaCrawler is a relational database exploration tool. It obtains database schema metadata such as tables, stored procedures, foreign keys, triggers and so on. SchemaCrawler can output this information in a compact format that ChatGPT can consume. Once you have provided this information to ChatGPT, you can ask ChatGPT to generate SQL for you, and more. ChatGPT can even summarize the purpose of your database, tell you the function of each table.
You can try prompts such as the following ones:
- "Get me the SQL statement to find all the tracks and their artists' names"
- "Get me the SQL statement to find the number of tracks for each artist, for artists that have more than 25 tracks, sorted by those who have the most"
- "List all tables"
- "Describe the Tracks table, with its columns and foreign keys"
- "What is the purpose or function of this database?"
To generate the database schema information, you will need Docker installed. Download a SQLite database called "chinook-database-2.0.1.sqlite" into your current directory.
Run this command:
docker run \
--mount type=bind,source="${PWD}",target=/home/schcrwlr \
--rm -it \
schemacrawler/schemacrawler \
/opt/schemacrawler/bin/schemacrawler.sh \
--server=sqlite \
--database=chinook-database-2.0.1.sqlite \
--info-level=standard \
--command=serialize \
--output-format=compact_json \
--output-file=schema.json
(If you are using PowerShell on Windows, replace the trailing backslash on each line with a back-tick, and map the current directory differently.)
Fire up ChatGPT in your browser (even the free one will do if you do not have a subscription). At the prompt, paste in the contents of your "schema.json" file. Then you can try out the prompts (questions) above. Enjoy exploring your database!
After you have got this working, use the SchemaCrawler command-line to connect to your own database, and explore it using a natural language interface courtesy of ChatGPT.
Top comments (0)