# What does a data source need to do to support SQL Expressions?

It must be a backend data source since SQL expressions is server side.

SQL Expressions supports two general categories of responses:

- **Tabular data**: A single dataframe, with no labels on any of the fields (columns). In short, a dataframe that can be directly mapped to a SQL table.
- **Labeled Metric Time Data (Timeseries or Numeric)**: Data that meets the [Dataplane](https://grafana.com/developers/dataplane) spec and has the type `Frame.Meta.Type` property set.

Therefore, data source support is really per query type or response type within a data source. So it is often not all or nothing in terms of data source support.

## Tabular Data

For tabular data responses SQL expressions should work out of the box, so the only thing to do is test out a basic select query.

## Labeled Metric Time Data (Timeseries or Numeric)

For labeled metric data, SQL Expressions detects it based on the `Frame.Meta.Type` property of the data frame. This property holds the the dataplane type.

The supported types are:

- `timeseries-multi`
- `timeseries-wide`
- `numeric-multi`
- `numeric-wide`

(Note: the `timeseries-long` and `timeseries-multi` fall into the tabular category).

So if your DS has metric data that matches one of those data types, it should work as long as `Frame.Meta.Type` (serialized into json as `schema.meta.type`) is set to that data type, for example:

```jsonc
[
  {
    "schema": {
      "meta": {
        "type": "numeric-wide", // SQL expressions need this property set for labeled metric data
        "typeVersion": [0, 1], // optional for SQL Expressions (so can be default 0.0)
        // TypeVersion > 0.0 should make other SSE operations more deterministic,
        // but if not a new DS, safest path is to do that as a separate task.
        // ...
      }
    },
    "fields": [
        // ...
    ]
  }
]
```

In go code:

```go
import (
    "github.com/grafana/grafana-plugin-sdk-go/data"
)

func main() {
    frame := data.NewFrame("")
    // ... add data and fields the create "NumericWide" type.
    frame.Meta = &data.FrameMeta{Type: data.FrameTypeNumericWide}
}
```

When SQL expressions receives labeled metric data, it will convert (flatten) the data into the `full-long` format for the corresponding kind (`timeseries` or `numeric`). This happens once the data source query is selected by RefID from a SQL expression. This is because SQL has no notion of labels.

## Manual Testing

In a dashboard, and for each type for response type your query offers:

- Add your data source query
- Add Expression -> Type SQL
- The default should query should be `SELECT * from A LIMIT 10` (assuming your query is RefID `A`). If this works on a few variations of that query type, it should be compatible with SQL expressions.
- If it doesn't work, see section above, if you are sending metadata and the datatypes mentioned above (or tabular data, and it isn't working, there may be an issue with SQL expressions)

# FAQ / Other

## What happens my DS sends labeled data without a frame type and Select from it in a SQL expressions?

You should get an error like:

```text
[sse.sql.input_conversion] failed to convert the results of query [A] (Datasource Type: [grafana-mock-datasource]) into a SQL/Tabular format for sql expression [B]: can not convert because the response is missing the data type (frame.meta.type) and has labels in the response that can not be mapped to a table
```

## What happens if DS returns with multiple frames without a frame type?

You should get an error like:

```text
[sse.sql.input_conversion] failed to convert the results of query [A] (Datasource Type: [grafana-mock-datasource]) into a SQL/Tabular format for sql expression [B]: can not convert because the response is missing the data type (frame.meta.type) and has more than one dataframe that can not be automatically mapped to a single table
```

## What happens if DS returns with multiple frames without different types?

This is not currently supported, SQL expressions will treat the first response as the type of all the frames, and you will like get an odd error that the additional type doesn't have what the first type needs. Or it will work and the results will be odd.

TODO: Should add an error in the case, or discard anything past the first type.

## Do instrument any Datasource specific metrics in relation to SQL expression support?

Yes:

```promql
sum(rate(grafana_sse_sql_command_input_count[$__rate_interval])) by (status,attempted_conversion,datasource_type,input_frame_type)
```

## What does the conversion look like?

<img width="1385" height="1223" alt="image" src="https://gist.github.com/user-attachments/assets/92e5c227-1045-4725-8259-0f91962c6ac0" />

See https://gist.github.com/kylebrandt/ff5fc4061d85fe08fd24f5cee6333414 for dashboard.json of above (uses mock datasource).