crate / crate/sqlalchemy-cratedb

Types: CLOB, NCHAR, NVARCHAR, DATETIME and DATE compile to DDL CrateDB cannot parse

Open
#304 0 comments 0 reactions 1 assignee Claimed by @bgunebakan View on GitHub
Dominant language
Python
Stars
9
Forks
4
Avg merge
6d 12h
Merged PRs (30d)
3

Description

## Problem

Five core SQLAlchemy types compile to type names CrateDB rejects, so `CREATE TABLE` fails with an opaque server error. Measured against CrateDB 6.4.3 with SQLAlchemy 2.0.50 on `main`:

| SQLAlchemy type | Emitted DDL | Server response |
|---|---|---|
| `sa.CLOB` | `CLOB` | `SQLParseException[Cannot find data type: clob]` |
| `sa.NCHAR(5)` | `NCHAR(5)` | `SQLParseException[Cannot find data type: nchar]` |
| `sa.NVARCHAR(50)` | `NVARCHAR(50)` | `SQLParseException[Cannot find data type: nvarchar]` |
| `sa.DATETIME` | `DATETIME` | `SQLParseException[Cannot find data type: datetime]` |
| `sa.DATE` | `DATE` | `UnsupportedFeatureException[Type 'date' does not support storage]` |

In each case the generic lower-case counterpart already works. `sa.Text`, `sa.String`, `sa.DateTime` and `sa.Date` all compile fine. Only the upper-case / SQL-standard spellings fall through to `GenericTypeCompiler` and emit the ANSI name verbatim.

This affects anyone using the SQL-standard type names directly, and anything generating models from a generic schema.

### One design question: `DATE`

CrateDB gained a **storable** `DATE` column type in **6.5**; on 6.4 and earlier the server rejects it with `Type 'date' does not support storage`.

Mapping `sa.DATE` → `TIMESTAMP` works on every supported server version and keeps `sa.Date` and `sa.DATE` on the same storage type but it means we never adopt the native type. The alternative is a version-gated mapping.

Discovered while working on #12 and moved changes on separate PR.
CI can't currently cannot test these, see the CI issue #303.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.