HTTP SQL endpoint

CrateDB exposes an HTTP endpoint for submitting SQL queries:

• Endpoint URL:

  http://<servername>:<port>/_sql

• Request format: Send a JSON payload with the SQL statement under the stmt key.

Basic Example

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql" \
     -d '{"stmt":"SELECT name, position FROM locations ORDER BY id LIMIT 2"}'

Response:

{
  "cols": ["name", "position"],
  "rows": [
    ["North West Ripple", 1],
    ["Outer Eastern Rim", 2]
  ],
  "rowcount": 2,
  "duration": 0.0123
}

Examples below use basic curl. For readability in examples, we'll use here-documents (EOF syntax).

---

Table of Contents

1. Parameter Substitution

2. Default Schema

3. Column Types

4. Data Types

5. Error Handling

6. Bulk Operations

---

1. Parameter Substitution

Use args to pass parameters:

Numbered Placeholders

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql" -d @- <<-EOF
{
  "stmt": "SELECT date, position FROM locations WHERE date <= \$1 AND position < \$2 ORDER BY position",
  "args": ["1979-10-12", 3]
}
EOF

The backslash before $1 is for shell escaping.

Unnumbered Placeholders

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql" -d @- <<-EOF
{
  "stmt": "SELECT date, position FROM locations WHERE date <= ? AND position < ? ORDER BY position",
  "args": ["1979-10-12", 3]
}
EOF

Parameter substitution cannot be used inside subscript notation (e.g., column[?] is invalid).

---

2. Default Schema

Set the default schema using the Default-Schema HTTP header:

curl -sS -H "Content-Type: application/json" \
     -H "Default-Schema: doc" \
     -X POST "127.0.0.1:4200/_sql" -d @- <<-EOF
{
  "stmt":"SELECT name, position FROM locations ORDER BY id LIMIT 2"
}
EOF

• Omit the header to default to the doc schema.

---

3. Column Types

Use the ?types query parameter to include data type IDs:

curl -sS -H "Content-Type: application/json" \
     -X POST "127.0.0.1:4200/_sql?types" -d @- <<-EOF
{
  "stmt": "SELECT date, position FROM locations WHERE date <= ? AND position < ? ORDER BY position",
  "args": ["1979-10-12", 3]
}
EOF

Sample Response:

{
  "cols": ["date", "position"],
  "col_types": [11, 9],
  "rows": [[308534400000, 1], [308534400000, 2]],
  "rowcount": 2,
  "duration": ...
}

Here, [11, 9] are the internal type IDs for the respective columns. Composite types (e.g., arrays) are represented as nested IDs:

"col_types": [4, [100, 9]]

---

4. Data Types

Below is an updated, cleaner version of CrateDB’s internal data type IDs:

• ID	Type
  0	NULL
  3	BOOLEAN
  4	TEXT
  5	IP
  6	DOUBLE PRECISION
  8	SMALLINT
  9	INTEGER
  10	BIGINT
  11	TIMESTAMP WITH TIME ZONE
  12	OBJECT
  13	GEO_POINT
  14	GEO_SHAPE
  15	TIMESTAMP WITHOUT TZ
  17	INTERVAL
  19	REGPROC
  21	OIDVECTOR
  22	NUMERIC
  23	REGCLASS
  24	DATE
  25	BIT
  26	JSON
  28	FLOAT VECTOR
  100	ARRAY

(Others omitted for brevity; this list highlights common types.)

---

5. Error Handling

Invalid queries return structured error info:

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql" -d '{"stmt":"SELECT * FROM foo.locations"}'

Response:

{
  "error": {
    "message": "SchemaUnknownException[Schema 'foo' unknown]",
    "code": 4045
  }
}

To include a stack trace (for debugging):

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql?error_trace=true" -d '{"stmt":"SELECT * FROM foo.locations"}'

error_trace is meant for developers/support—not for production client libraries.

Common Error Codes

• 4000: Invalid syntax or unsupported SQL

• 4043: Unknown column

• 4045: Unknown schema

• 4091: Duplicate primary key

• 5000: Unhandled server error

(For a full list, refer to the Extended Error Codes section.)

---

6. Bulk Operations

Send a single SQL statement with multiple parameter sets using bulk_args:

Example:

curl -sS -H "Content-Type: application/json" -X POST "127.0.0.1:4200/_sql" -d @- <<-EOF
{
  "stmt": "INSERT INTO locations (id, name, kind, description) VALUES (?, ?, ?, ?)",
  "bulk_args": [
    [1337, "Earth", "Planet", "…"],
    [1338, "Sun", "Star", "…"],
    [1339, "Titan", "Moon", "…"]
  ]
}
EOF

Response:

{
  "cols": [],
  "duration": ...,
  "results": [
    { "rowcount": 1 },
    { "rowcount": 1 },
    { "rowcount": 1 }
  ]
}

Bulk Errors

Analysis Errors (pre-execution failure):

jsonCopierModifier{
  "error": {
    "code": 4043,
    "message": "ColumnUnknownException[Column y unknown]"
  }
}

Runtime Errors (during execution):

jsonCopierModifier{
  "cols": [],
  "duration": ...,
  "results": [
    { "rowcount": 1 },
    {
      "rowcount": -2,
      "error": {
        "code": 4091,
        "message": "DuplicateKeyException[...]"
      }
    },
    { "rowcount": 1 }
  ]
}

Note: Only the first 10 errors per shard include detailed error info. Later errors simply show rowcount: -2.

error_trace is not supported for bulk operations.