> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/graphprotocol/graph-node/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Interface

> Experimental SQL query interface for direct database access to subgraph data

<Note>
  **This interface is extremely experimental.** There is no guarantee that this interface will ever be brought to production use. It exists solely to evaluate the utility of direct SQL access.

  The interface is only available when the environment variable `GRAPH_ENABLE_SQL_QUERIES` is set to `true`.
</Note>

## Overview

The SQL interface allows issuing direct SQL queries against subgraph data by posting JSON requests to the `/subgraphs/sql` endpoint. The server responds with query results in JSON format.

## Request Format

Post a JSON document to `/subgraphs/sql` with the following keys:

<Accordion title="Request Parameters">
  | Parameter    | Type   | Required | Description                                            |
  | ------------ | ------ | -------- | ------------------------------------------------------ |
  | `deployment` | string | Yes      | IPFS hash of the deployment to query                   |
  | `query`      | string | Yes      | SQL query to execute                                   |
  | `mode`       | string | Yes      | Either `info` (metadata only) or `data` (full results) |
</Accordion>

### Mode Options

* **`info`**: Returns only metadata about the query execution (no actual data)
* **`data`**: Returns the full query result set

## Table and Column Naming

### Entity Types

<Note>
  Table and attribute names for `@entity` types are snake-cased from their GraphQL schema form.
</Note>

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type SomeDailyStuff @entity {
    id: ID!
    value: BigInt!
    timestamp: Int!
  }
  ```

  ```sql SQL Table Name theme={null}
  -- Accessible as snake_case
  SELECT * FROM some_daily_stuff;
  ```
</CodeGroup>

### Aggregation Types

For `@aggregation` types, use the function syntax: `<aggregation>(<interval>)`

<CodeGroup>
  ```graphql GraphQL Schema theme={null}
  type MyStats @aggregation(intervals: ["hour", "day"]) {
    id: Int8!
    total: BigInt!
  }
  ```

  ```sql SQL Access theme={null}
  -- Hour interval
  SELECT * FROM my_stats('hour');

  -- Day interval
  SELECT * FROM my_stats('day');
  ```
</CodeGroup>

## SQL Capabilities

The interface supports fairly arbitrary SQL, including:

* Aggregations (`COUNT`, `SUM`, `AVG`, etc.)
* Most PostgreSQL built-in functions
* Complex joins and subqueries
* Window functions
* CTEs (Common Table Expressions)

<Note>
  The broad SQL support makes it easy to issue queries that take a very long time. Use `GRAPH_SQL_STATEMENT_TIMEOUT` to set query timeouts.
</Note>

## Example Usage

### Request

<CodeGroup>
  ```json Request Body theme={null}
  {
    "query": "select number, hash, parent_hash, timestamp from block order by number desc limit 2",
    "deployment": "QmSoMeThInG",
    "mode": "data"
  }
  ```

  ```bash cURL theme={null}
  curl -X POST http://localhost:8000/subgraphs/sql \
    -H "Content-Type: application/json" \
    -d '{
      "query": "select number, hash, parent_hash, timestamp from block order by number desc limit 2",
      "deployment": "QmSoMeThInG",
      "mode": "data"
    }'
  ```
</CodeGroup>

### Response

```json theme={null}
{
  "data": [
    {
      "hash": "\\x5f91e535ee4d328725b869dd96f4c42059e3f2728dfc452c32e5597b28ce68d6",
      "number": 5000,
      "parent_hash": "\\x82e95c1ee3a98cd0646225b5ae6afc0b0229367b992df97aeb669c898657a4bb",
      "timestamp": "2015-07-30T20:07:44+00:00"
    },
    {
      "hash": "\\x82e95c1ee3a98cd0646225b5ae6afc0b0229367b992df97aeb669c898657a4bb",
      "number": 4999,
      "parent_hash": "\\x875c9a0f8215258c3b17fd5af5127541121cca1f594515aae4fbe5a7fbef8389",
      "timestamp": "2015-07-30T20:07:36+00:00"
    }
  ]
}
```

<Note>
  Binary data uses PostgreSQL's hex string notation (e.g., `\x5f91e535...`).
</Note>

## Current Limitations

<Accordion title="No Bind Variables">
  **Limitation**: Bind variables and query parameters are not supported.

  **Impact**: All queries must use literal SQL values, which can be a security concern.

  **Mitigation**: Ensure proper input sanitization if exposing this interface.
</Accordion>

<Accordion title="Statement Timeout">
  **Configuration**: `GRAPH_SQL_STATEMENT_TIMEOUT` environment variable

  **Default**: Unlimited

  **Recommendation**: Always set a timeout in production-like environments to prevent resource exhaustion.
</Accordion>

<Accordion title="Query at Head Only">
  **Current Behavior**: Queries always execute at the subgraph head block.

  **Future Enhancement**: Could easily add a parameter to specify a historical block number for time-travel queries.
</Accordion>

<Accordion title="Raw Schema Exposure">
  **Current State**: The interface exposes the raw SQL schema, including implementation details.

  **Hidden Columns**: System columns like `vid` and `block_range` are made inaccessible, but the schema structure is otherwise exposed.

  **Consideration**: This may expose more implementation details than desired for a production API.
</Accordion>

<Accordion title="No Cross-Subgraph Joins">
  **Limitation**: Cannot join across different subgraphs.

  **Reason**: Would require additional plumbing to hide sharding effects.

  **Future Work**: Adding cross-subgraph joins is possible but requires architectural work.
</Accordion>

<Accordion title="JSON Response Format">
  **Current Format**: JSON

  **Issue**: Inefficient for large result sets

  **Future Improvement**: Consider alternative formats like:

  * CSV
  * Apache Arrow
  * Protocol Buffers
  * MessagePack
</Accordion>

## Security Considerations

<Note>
  While significant effort has been made to ensure this interface is safe (particularly preventing writes), there is no guarantee it works without bugs. Use with caution.
</Note>

### Safety Measures

* **Read-only**: Interface is designed to prevent write operations
* **System column protection**: Columns like `vid` and `block_range` are inaccessible
* **Schema isolation**: Queries are scoped to the specified deployment

### Risk Factors

1. **Query Complexity**: Easy to issue expensive queries that consume significant resources
2. **Resource Exhaustion**: Without proper timeouts, long-running queries can impact node performance
3. **Schema Exposure**: Raw schema access may reveal implementation details
4. **No Query Validation**: Arbitrary SQL means potential for unexpected behavior

<CodeGroup>
  ```env Recommended Configuration theme={null}
  # Enable the interface
  GRAPH_ENABLE_SQL_QUERIES=true

  # Set a reasonable timeout (in milliseconds)
  GRAPH_SQL_STATEMENT_TIMEOUT=30000
  ```

  ```sql Dangerous Query Example theme={null}
  -- This could run for a very long time
  SELECT a.*, b.*
  FROM large_table a
  CROSS JOIN large_table b
  WHERE some_expensive_function(a.column) = b.column;
  ```
</CodeGroup>

## Use Cases

### Analytics and Exploration

* Ad-hoc data analysis
* Complex aggregations not easily expressed in GraphQL
* Data export for external tools
* Debugging and development

### Advanced Queries

<CodeGroup>
  ```sql Window Functions theme={null}
  SELECT 
    address,
    balance,
    LAG(balance) OVER (PARTITION BY address ORDER BY timestamp) as previous_balance
  FROM account_balance
  ORDER BY timestamp DESC;
  ```

  ```sql Complex Aggregations theme={null}
  WITH daily_stats AS (
    SELECT 
      DATE(timestamp) as date,
      COUNT(*) as tx_count,
      SUM(value) as total_value
    FROM transfer
    GROUP BY DATE(timestamp)
  )
  SELECT 
    date,
    tx_count,
    AVG(tx_count) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as week_avg
  FROM daily_stats;
  ```
</CodeGroup>

## Future Improvements

<Accordion title="Easy Additions">
  * Bind variable support
  * Historical block queries
  * Better response formats
  * Query result streaming
  * Query plan analysis
</Accordion>

<Accordion title="Complex Additions">
  * Cross-subgraph joins
  * Write operations (extremely careful consideration needed)
  * Query optimization hints
  * Result caching
  * Rate limiting and quotas
</Accordion>