crate / crate/sqlalchemy-cratedb
Types: CLOB, NCHAR, NVARCHAR, DATETIME and DATE compile to DDL CrateDB cannot parse
- 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
Assessment
This issue has not been assessed yet.