Schema-Guided Prompt Strategies for Text-to-SQL over Relational Databases Using Local LLMs
DOI:
https://doi.org/10.29207/resti.v10i4.6966Keywords:
chat with database, large language models, prompt engineering, schema-guided prompting, SQL relational, Text-to-SQLAbstract
Text-to-SQL systems translate natural language questions into executable SQL queries, allowing users without SQL expertise to access structured data stored in relational databases. Although Large Language Models (LLMs) have substantially improved SQL generation capabilities, many state-of-the-art Text-to-SQL approaches continue to rely on cloud-based models with high computational requirements. Such dependence limits their deployment in environments with limited computing resources. This study addresses this limitation by proposing schema-guided prompting strategies for Text-to-SQL generation using local LLMs. A chat-based application was developed using the Django Web Framework, while model inference was performed through the Ollama platform to enable the deployment of local LLMs. The proposed framework incorporates database schema information, including table structures and column attributes, into structured prompts to improve the alignment between natural language questions and SQL generation. Experiment results across multiple databases demonstrate that schema-guided prompting significantly improves Text-to-SQL performance. The highest accuracy was achieved by LLaMA 3 (8B) with objective-aware prompting, reaching an Exact Matching (EM) accuracy of 71.96%. These findings suggest that structured prompt engineering provides a practical alternative to model fine-tuning for locally deployed LLMs, offering an effective balance between SQL generation accuracy, computational efficiency, and data privacy. Future work will investigate fine-tuning strategies, example selection methods, and cross-domain evaluation to enhance SQL generation.
Downloads
References
J. Sequeda, D. Allemang, and B. Jacob, “A Benchmark to Understand the Role of Knowledge Graphs on Large Language Model’s Accuracy for Question Answering on Enterprise SQL Databases,” in Proceedings of the 7th ACM SIGMOD Joint International Workshop on Graph Data Management Experiences and Systems, (GRADES) and Network Data Analytics, (NDA), GRADES-NDA 2024, 2024. doi: 10.1145/3661304.3661901.
J. Sequeda, D. Allemang, and B. Jacob, “Knowledge Graphs as a source of trust for LLM-powered enterprise question answering,” Journal of Web Semantics, vol. 85, 2025, doi: 10.1016/j.websem.2024.100858.
F. Lei et al., “Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows,” arXiv preprint arXiv:2411.07763, Nov. 2024, doi: 10.48550/arXiv.2411.07763.
H. Yuan, X. Tang, K. Chen, L. Shou, G. Chen, and H. Li, “COGSQL: A Cognitive Framework for Enhancing Large Language Models in Text-to-SQL Translation,” in Proceedings of the AAAI Conference on Artificial Intelligence, 2025, pp. 25778–25786. doi: 10.1609/aaai.v39i24.34770.
A.-M. Popescu, A. Armanasu, O. Etzioni, D. Ko, and A. Yates, “Modern natural language interfaces to databases: composing statistical parsing with semantic tractability,” in Proceedings of the 20th International Conference on Computational Linguistics, in COLING ’04. USA: Association for Computational Linguistics, 2004, pp. 141–es. doi: 10.3115/1220355.1220376.
H. Bast and E. Haussmann, “More Accurate Question Answering on Freebase,” in Proceedings of the 24th ACM International on Conference on Information and Knowledge Management, in CIKM ’15. New York, NY, USA: Association for Computing Machinery, 2015, pp. 1431–1440. doi: 10.1145/2806416.2806472.
W. Zheng, H. Cheng, L. Zou, J. X. Yu, and K. Zhao, “Natural Language Question/Answering: Let Users Talk With The Knowledge Graph,” in Proceedings of the 2017 ACM on Conference on Information and Knowledge Management, in CIKM ’17. New York, NY, USA: Association for Computing Machinery, 2017, pp. 217–226. doi: 10.1145/3132847.3132977.
M. and C. H. Damljanovic Danica and Agatonovic, “Natural Language Interfaces to Ontologies: Combining Syntactic Analysis and Ontology-Based Lookup through the User Interaction,” in The Semantic Web: Research and Applications, G. and H. E. and ten T. A. and S. H. and C. L. and T. T. Aroyo Lora and Antoniou, Ed., Berlin, Heidelberg: Springer Berlin Heidelberg, 2010, pp. 106–120. doi: 10.1007/978-3-642-13486-9_8.
F. and S. C. and B. C. and Z. T. and B. H. and M. R. and D. C. and D. J. and M. T. and H. J. Song Dezhao and Schilder, “TR Discover: A Natural Language Interface for Querying and Analyzing Interlinked Datasets,” in The Semantic Web - ISWC 2015, O. and S. E. and S. M. and d’Aquin M. and S. K. and G. P. and D. M. and H. J. and T. K. and S. S. Arenas Marcelo and Corcho, Ed., Cham: Springer International Publishing, 2015, pp. 21–37. doi: 10.1007/978-3-319-25010-62.
V. Zhong, C. Xiong, and R. Socher, “Seq2SQL: Generating Structured Queries from Natural Language using Reinforcement Learning,” arXiv preprint arXiv:1709.00103, Aug. 2017, doi: 10.48550/arXiv.1709.00103.
W. Hwang, J. Yim, S. Park, and M. Seo, “A Comprehensive Exploration on WikiSQL with Table-Aware Word Contextualization,” CoRR, vol. abs/1902.01069, 2019, doi: 10.48550/arXiv.1902.01069.
J. Guo et al., “Towards complex text-to-SQL in cross-domain database with intermediate representation,” in Acl 2019 57th Annual Meeting of the Association for Computational Linguistics Proceedings of the Conference, 2020, pp. 4524–4535. doi: 10.18653/v1/P19-1444.
B. Wang, R. Shin, X. Liu, O. Polozov, and M. Richardson, “RAT-SQL: Relation-Aware Schema Encoding and Linking for Text-to-SQL Parsers,” in Proceedings of the 58th Annual Meeting of the Association for Computational Linguistics, Stroudsburg, PA, USA: Association for Computational Linguistics, 2020, pp. 7567–7578. doi: 10.18653/v1/2020.acl-main.677.
T. Yu et al., “Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-SQL task,” in Proceedings of the 2018 Conference on Empirical Methods in Natural Language Processing, EMNLP 2018, 2018, pp. 3911–3921. doi: 10.18653/v1/D18-1425.
J. Li et al., “Can LLM Already Serve as A Database Interface? A BIg Bench for Large-Scale Database Grounded Text-to-SQLs,” in Advances in Neural Information Processing Systems, 2023. doi: 10.48550/arXiv.2305.03111.
C.-H. Lee, O. Polozov, and M. Richardson, “KaggleDBQA: Realistic evaluation of text-to-SQL parsers,” in ACL-IJCNLP 2021 - 59th Annual Meeting of the Association for Computational Linguistics and the 11th International Joint Conference on Natural Language Processing, Proceedings of the Conference, 2021, pp. 2261–2273. doi: 10.18653/v1/2021.acl-long.176.
C. Zhang et al., “FinSQL: Model-Agnostic LLMs-based Text-to-SQL Framework for Financial Analysis,” in Proceedings of the ACM SIGMOD International Conference on Management of Data, Santiago: Association for Computing Machinery, Jun. 2024, pp. 93–105. doi: 10.1145/3626246.3653375.
A. Marshan, A. N. Almutairi, A. Ioannou, D. Bell, M. Arzoky, and A. Monaghan, “MedT5SQL: a transformers-based large language model for text-to-SQL conversion in the healthcare domain,” Front. Big Data, vol. 7, 2024, doi: 10.3389/fdata.2024.1371680.
OpenAI et al., “GPT-4 Technical Report,” Mar. 2024, doi: 10.48550/arXiv.2303.08774.
H. Touvron et al., “LLaMA: Open and Efficient Foundation Language Models,” Feb. 2023, doi: 10.48550/arXiv.2302.13971.
B. Li, Y. Luo, C. Chai, G. Li, and N. Tang, “The Dawn of Natural Language to SQL: Are We Fully Ready?,” Proceedings of the VLDB Endowment, vol. 17, no. 11, pp. 3318–3331, Jul. 2024, doi: 10.14778/3681954.3682003.
D. Gao et al., “Text-to-SQL Empowered by Large Language Models: A Benchmark Evaluation,” Proceedings of the VLDB Endowment, vol. 17, no. 5, pp. 1132–1145, 2024, doi: 10.14778/3641204.3641221.
Z. Li et al., “PET-SQL: A Prompt-Enhanced Two-Round Refinement of Text-to-SQL with Cross-consistency,” arXiv preprint arXiv:2403.09732, Mar. 2024, doi: 10.48550/arXiv.2403.09732.
S. Chang and E. Fosler-Lussier, “How to Prompt LLMs for Text-to-SQL: A Study in Zero-shot, Single-domain, and Cross-domain Settings,” Nov. 2023, doi: 10.48550/arXiv.2305.11853.
M. Pourreza and D. Rafiei, “DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction,” in Advances in Neural Information Processing Systems, 2023. doi: 10.5555/3666122.3667699.
L. Nan et al., “Enhancing Text-to-SQL Capabilities of Large Language Models: A Study on Prompt Design Strategies,” 2023. doi: 10.18653/v1/2023.findings-emnlp.996.
J. Liu, D. Shen, Y. Zhang, B. Dolan, L. Carin, and W. Chen, “What Makes Good In-Context Examples for GPT-3?,” in Proceedings of Deep Learning Inside Out (DeeLIO 2022): The 3rd Workshop on Knowledge Extraction and Integration for Deep Learning Architectures, Stroudsburg, PA, USA: Association for Computational Linguistics, 2022, pp. 100–114. doi: 10.18653/v1/2022.deelio-1.10.
I. Trummer, “Generating Succinct Descriptions of Database Schemata for Cost-Efficient Prompting of Large Language Models,” Proceedings of the VLDB Endowment, vol. 17, no. 11, pp. 3511–3523, 2024, doi: 10.14778/3681954.3682017.
J. Byun, B. Kim, K.-A. Cha, and E. Lee, “Design and Implementation of an Interactive Question-Answering System with Retrieval-Augmented Generation for Personalized Databases,” Applied Sciences (Switzerland), vol. 14, no. 17, 2024, doi: 10.3390/app14177995.
A. Mansurova, A. Mansurova, and A. Nugumanova, “QA-RAG: Exploring LLM Reliance on External Knowledge,” Big Data and Cognitive Computing, vol. 8, no. 9, 2024, doi: 10.3390/bdcc8090115.
P. Wang, T. Shi, and C. K. Reddy, “Text-to-SQL Generation for Question Answering on Electronic Medical Records,” in The Web Conference 2020 - Proceedings of the World Wide Web Conference, WWW 2020, 2020, pp. 350–361. doi: 10.1145/3366423.3380120.
H. Li, J. Zhang, C. Li, and H. Chen, “RESDSQL: Decoupling Schema Linking and Skeleton Parsing for Text-to-SQL,” in Proceedings of the 37th Aaai Conference on Artificial Intelligence Aaai 2023, 2023, pp. 13067–13075. doi: 10.1609/aaai.v37i11.26535.
Z. Zhan, E. Haihong, and M. Song, “Leveraging Large Language Model for Enhanced Text-to-SQL Parsing,” IEEE Access, vol. 13, pp. 30497–30504, 2025, doi: 10.1109/ACCESS.2025.3540072.
Downloads
Published
Issue
Section
License
Copyright in each article belongs to the author
- The author acknowledges that the RESTI Journal (System Engineering and Information Technology) is the first publisher to publish with a license Creative Commons Attribution 4.0 International License.
- Authors can enter writing separately, arrange the non-exclusive distribution of manuscripts that have been published in this journal into other versions (eg sent to the author's institutional repository, publication in a book, etc.), by acknowledging that the manuscript has been published for the first time in the RESTI (Rekayasa Sistem dan Teknologi Informasi) journal ;
