Querying an astronomical database using large language models: the ALeRCE text-to-SQL system

¹ Department of Electrical Engineering, University of Chile, Av. Tupper 2007, Santiago, Chile
² Millennium Institute of Astrophysics (MAS), Nuncio Monseñor Sótero Sanz 100, Providencia, Santiago, Chile
³ Data and Artificial Intelligence Initiative (ID&IA), Universidad de Chile, Chile
⁴ Center for Mathematical Modeling, Universidad de Chile, Beauchef 851, North building, 7th floor, Santiago 8320000, Chile
⁵ Departamento de Astronomía, Universidad de Chile, Casilla 36D, Santiago, Chile
⁶ Department of Computer Science, Universidad de Conceptión Edmundo Larenas 219, Conceptión, Chile
⁷ Center for Data and Artificial Intelligence, Universidad de Conceptión, Edmundo Larenas 310, Conceptión, Chile
⁸ Heidelberg Institute for Theoretical Studies, Heidelberg, Baden-Württemberg, Germany
⁹ Instituto de Alta Investigatión, Universidad de Tarapacá, Casilla 7D, Arica 1010000, Chile
¹⁰ European Southern Observatory, Karl-Schwarzschild-Strasse 2, 85748 Garching bei München, Germany
¹¹ Instituto de Astrofísica, Facultad de Física, Pontificia Universidad Católica de Chile, Casilla 306, Santiago 22, Chile
¹² Centro de Astroingeniería, Pontificia Universidad Católica de Chile, Av. Vicuña Mackenna 4860, 7820436 Macul, Santiago, Chile
¹³ Instituto de Estudios Astrofísicos, Facultad de Ingeniería y Ciencias, Universidad Diego Portales, Av. Ejército Libertador 441, Santiago, Chile
¹⁴ Centro Interdisciplinario de Data Science, Facultad de Ingenieria y Ciencias, Universidad Diego Portales, Av. Ejército Libertador 441, Santiago, Chile

^★ Corresponding author: This email address is being protected from spambots. You need JavaScript enabled to view it.

Received: 22 November 2025
Accepted: 11 June 2026

Abstract

We developed a text-to-structured query language (SQL) system based on large language models (LLMs) using in-context learning and applied it to the Automatic Learning for the Rapid Classification of Events (ALeRCE) astronomical database. ALeRCE is a community broker for the Zwicky Transient Facility and the Vera C. Rubin Observatory. The system enables users to query the database in natural language (NL) and generates executable SQL queries. To develop and evaluate the system, we constructed a set of 110 NL-SQL pairs. We propose a step-by-step generation framework comprising four modules: schema linking, query classification, prompt decomposition, and self-correction. The performance of 13 LLMs was evaluated using in-context learning and prompt engineering techniques. Text-to-SQL performance was assessed using the perfect-match (PM) rate for row identifiers (e.g., object identifiers) and column identifiers (i.e., column names). The proposed step-by-step framework consistently outperforms a direct-inference baseline, while the self-correction module consistently reduces execution errors. For Claude Opus 4.6, PM performance on row (column) identifiers is high for simple queries, reaching 0.97 (0.94), and decreases with query complexity to 0.44 (0.72) for medium queries and 0.59 (0.49) for hard queries. Among the 13 models evaluated, the best-performing LLMs for the text-to-SQL task are Claude Opus 4.6, Gemini 2.5 Pro, Gemini 3 Flash, and GPT-5.2-Codex.