Relevant source files

The following files were used as context for generating this wiki page: - [marimo/_sql/engines/doc.md](https://github.com/marimo-team/marimo/blob/main/marimo/_sql/engines/doc.md) - [marimo/_sql/sql.py](https://github.com/marimo-team/marimo/blob/main/marimo/_sql/sql.py) - [marimo/_sql/engines/duckdb.py](https://github.com/marimo-team/marimo/blob/main/marimo/_sql/engines/duckdb.py) - [packages/openapi/api.yaml](https://github.com/marimo-team/marimo/blob/main/packages/openapi/api.yaml) - [frontend/src/components/datasources/__tests__/utils.test.ts](https://github.com/marimo-team/marimo/blob/main/frontend/src/components/datasources/__tests__/utils.test.ts) - [packages/smart-cells/src/__tests__/sql-parser.test.ts](https://github.com/marimo-team/marimo/blob/main/packages/smart-cells/src/__tests__/sql-parser.test.ts)

# SQL Engine & Databases marimo features a first-class SQL engine that allows users to query dataframes, databases, data warehouses, and local files directly within a reactive notebook environment. The system is designed to be "batteries-included," primarily powered by [DuckDB](https://duckdb.org/) for fast in-process analytical queries, while supporting external connections to various SQL dialects. The SQL implementation integrates deeply with marimo's reactive dataflow. Users can write SQL queries that reference Python variables through f-string interpolation, and the results are automatically returned as Python dataframes (supporting formats like Pandas or Polars). This bridge between SQL and Python allows for seamless data exploration and transformation within a single unified interface. Sources: [README.md](), [marimo/_sql/engines/doc.md](), [examples/sql/README.md]() ## SQL Engine Architecture The architecture of marimo's SQL system is built on a class hierarchy that separates metadata discovery (catalog) from query execution. This abstraction allows marimo to support diverse backends, from local DuckDB instances to remote data warehouses like BigQuery or Snowflake. ### Core Class Hierarchy The system utilizes abstract base classes to define the interface for different engine capabilities. ```mermaid classDiagram BaseEngine <|-- EngineCatalog BaseEngine <|-- QueryEngine EngineCatalog <|-- SQLConnection QueryEngine <|-- SQLConnection class BaseEngine { <> +source: str +dialect: str +is_compatible(var: Any): bool } class EngineCatalog { <> +inference_config: InferenceConfig +get_default_database(): str +get_default_schema(): str +get_databases(): list[Database] +get_tables_in_schema(): list[DataTable] +get_table_details(): DataTable } class QueryEngine { <> +execute(query: str): Any +sql_output_format(): SqlOutputType } class SQLConnection { <> Implements both catalog and query interfaces } ``` The hierarchy distinguishes between engines that only provide metadata and those that execute queries. | Component | Responsibility | | :--- | :--- | | **BaseEngine** | Provides basic identification (source, dialect) and compatibility checks. | | **EngineCatalog** | Handles metadata discovery, including databases, schemas, and table details. | | **QueryEngine** | Handles the execution of SQL strings and determines the output format. | | **SQLConnection** | A combined interface for engines that support both discovery and execution. | Sources: [marimo/_sql/engines/doc.md:3-40]() ### Engine Implementations Different backends implement different parts of this hierarchy: * **Catalog Only**: PyIceberg. * **Query Only**: PEP 249 compliant drivers. * **Full Support (Catalog & Query)**: DuckDB, Clickhouse, Ibis, Redshift, SQLAlchemy/SQLModel, and Arrow Database Connectivity (ADBC) drivers. Sources: [marimo/_sql/engines/doc.md:42-55]() ## SQL Parsing and Transformations marimo uses specialized parsers to bridge the gap between SQL cells in the UI and the underlying Python implementation (`mo.sql`). This allows for ergonomic SQL authoring while maintaining the notebook's pure-Python storage format. ### SQL Parser Logic The `SQLParser` handles the transformation of SQL code into valid Python calls and vice versa. It manages f-string interpolation, variable assignment, and engine configuration. ```mermaid flowchart TD A[SQL Cell UI] -->|transformOut| B[Python Code: mo.sql] B -->|transformIn| A subgraph Metadata Extraction C[DataFrame Name] D[Engine Identifier] E[Output Visibility] end B -.-> C B -.-> D B -.-> E ``` This diagram illustrates the bidirectional transformation between the SQL editor and the Python file. Sources: [packages/smart-cells/src/__tests__/sql-parser.test.ts:1-150]() ### Dialect-Specific Code Generation When generating SQL code for the backend, marimo adjusts the syntax based on the target dialect. This includes handling different quoting characters (backticks vs. double quotes) and pagination syntax (LIMIT vs. TOP). | Dialect | Table/Column Quoting | Pagination | | :--- | :--- | :--- | | **DuckDB / PostgreSQL** | Double quotes (`"table"`) | `LIMIT 100` | | **BigQuery** | Backticks (`` `table` ``) | `LIMIT 100` | | **MSSQL** | No quotes / Brackets | `SELECT TOP 100` | | **Snowflake** | Double quotes | `LIMIT 100` | Sources: [frontend/src/components/datasources/__tests__/utils.test.ts:40-150]() ## API and Communication The communication between the frontend SQL editor and the backend kernel is handled via a series of OpenAPI-defined endpoints. These endpoints support features like query validation, table list retrieval, and dataset previews. ### Key SQL API Endpoints | Endpoint | Request Object | Description | | :--- | :--- | :--- | | `/api/sql/validate` | `ValidateSQLRequest` | Validates SQL syntax against a specific dialect or database connection. | | `/api/datasources/preview_sql_table` | `PreviewSQLTableRequest` | Retrieves metadata and sample data for a specific SQL table. | | `/api/datasources/preview_sql_table_list` | `ListSQLTablesRequest` | Lists all tables and views available in a specific schema. | | `/api/datasources/preview_sql_schema_list` | `ListSQLSchemasRequest` | Lists all schemas available in a database. | Sources: [packages/openapi/api.yaml:2300-2450](), [packages/openapi/src/api.ts]() ### Schema and Metadata Models marimo uses a structured data model to represent database objects across the stack. ```mermaid erDiagram DataSourceConnection ||--o{ Database : contains Database ||--o{ Schema : contains Schema ||--o{ DataTable : contains DataTable ||--o{ DataTableColumn : contains DataTableColumn ||--|| ColumnStats : has ``` The data model allows the frontend to reconstruct the database hierarchy for the Data Explorer. Sources: [packages/openapi/api.yaml:450-650]() ## Runtime Configuration Users can configure the behavior of the SQL engine through the `RuntimeConfig` and `DiagnosticsConfig`. * **Default Output Format**: Configurable via `default_sql_output`. Options include `"auto"`, `"native"`, `"polars"`, `"lazy-polars"`, or `"pandas"`. * **SQL Linting**: Enabled via `sql_linter` in the diagnostics configuration to provide real-time feedback in the SQL editor. * **Auto-Discovery**: The `DatasourcesConfig` allows users to toggle the automatic discovery of schemas, tables, and columns to optimize performance on large databases. Sources: [packages/openapi/api.yaml:1650-1750](), [packages/openapi/api.yaml:700-730]() ## Conclusion The SQL Engine in marimo is a sophisticated abstraction layer that enables high-performance data analysis through DuckDB while maintaining extensibility for enterprise databases. By leveraging a unified `BaseEngine` hierarchy and a robust parsing system, marimo provides a seamless transition between SQL-based data manipulation and Python-based analysis and visualization.