SQLAlchemy/DDL: Support custom `crate_autogenerate_uuid` column definition option

[amotl]

Introduction

CrateDB's gen_random_text_uuid() function generates UUID values suitable to be used as auto-generated primary key values, instead of PostgreSQL's SERIAL type.

crash <<EOL
  DROP TABLE IF EXISTS testdrive;
  CREATE TABLE IF NOT EXISTS "doc"."testdrive" (
    "x" TEXT DEFAULT gen_random_text_uuid() NOT NULL,
    "y" INT
  );
  INSERT INTO testdrive (y) VALUES (42);
  INSERT INTO testdrive (y) VALUES (42);
  REFRESH TABLE testdrive;
  SELECT * FROM testdrive;
EOL

Objective

It would be sweet if we could make the CrateDB SQLAlchemy dialect support this feature appropriately.

Proposal

I think the right way is to implement a custom SQLAlchemy column definition like that:

>>> import sqlalchemy as sa
>>> sa.Column("x", sa.String, primary_key=True, crate_autogenerate_uuid=True)

Support custom column DDL within the generation of CREATE TABLE statements, by using the compiler extension documented in Custom SQL Constructs and Compilation Extension to extend CreateColumn.

-- https://docs.sqlalchemy.org/en/20/core/ddl.html#sqlalchemy.schema.CreateColumn

References

• https://github.com/crate/crate-clients-tools/issues/48

[amotl]

crate/crate-python#585 will resolve this by adding a corresponding section to the documentation, to use the gen_random_text_uuid() scalar function [^1], which can also be used within an SQL DDL statement, in order to automatically assign random identifiers to newly inserted records on the server side. In this spirit, it is suitable to be used as a PRIMARY KEY constraint for SQLAlchemy.

[^1]: Available since CrateDB 4.5.0.