Recently I have interest to MCP. Because MCP can engage task and API with natural language so we don’t need code (BTW, I love writing code!!!!).
Today I tried to make SQL query with MCP.
I used Claude desktop. At first I modfied config file shown below. postgres part is added.
Then I launched claud desktop and asked following question.
Then LLM make query and seach information.
リクエスト
{
`sql`: `SELECT * FROM information_schema.tables WHERE table_name LIKE '%chembl%' OR table_name LIKE '%compound%' OR table_name LIKE '%activity%' OR table_name LIKE '%bioactivity%';`
}
レスポンス
[
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activity_stds_lookup",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activity_properties",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activity_smid",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activity_supp_map",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activity_supp",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_properties",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_structural_alerts",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "chembl_id_lookup",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "chembl_release",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_structures",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "pg_catalog",
"table_name": "pg_stat_activity",
"table_type": "VIEW",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "NO",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_records",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
}
]
Great! I can see this is a ChEMBL database. Let me look for the main activities table and compounds table:
リクエスト
{
`sql`: `SELECT * FROM information_schema.tables WHERE table_name LIKE '%activities%' OR table_name LIKE '%compound%' OR table_name LIKE '%molecule%';`
}
レスポンス
[
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "activities",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_properties",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_structural_alerts",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_structures",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_atc_classification",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_hierarchy",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_synonyms",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_dictionary",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_frac_classification",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_hrac_classification",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "compound_records",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
},
{
"table_catalog": "chembl_35",
"table_schema": "public",
"table_name": "molecule_irac_classification",
"table_type": "BASE TABLE",
"self_referencing_column_name": null,
"reference_generation": null,
"user_defined_type_catalog": null,
"user_defined_type_schema": null,
"user_defined_type_name": null,
"is_insertable_into": "YES",
"is_typed": "NO",
"commit_action": null
}
]
...
Finally I could get following summary.
It was really amazing for me because LLM made SQL query from my request.
And MCP ccould communicate local postgresql chembl_35 db.
It was failed when I used SMILES as a query. So there is still room for improvement of the technolgy or my prompt.