Advice for Implementing a ShortUUID Column Type?

[Colton Allen]

I want to make my UUID's prettier so I've gone about implementing a ShortUUID column based on the shortuuid library[1].  The idea is to store the primary key as a UUID type in postgres (since its optimized for that) and transform the UUID to a shortuuid for presentation and querying.  This is my first attempt at implementing it.  It has some short comings.

I was wondering if you had any advice for fully baking the implementation.  I've pasted the code I have so far.

from sqlalchemy_utils.types.uuid import UUIDType

import uuid

import shortuuid

def _decode_shortuuid(value):

    try:

        return shortuuid.decode(value)

    except ValueError:

        return None

def _encode_shortuuid(value):

    try:

        if value is None:

            return None

        return shortuuid.encode(value)

    except KeyError:

        return None

class ShortUUID(UUIDType):

    """Converts UUIDs to ShortUUIDs for readability's sake."""

    def process_bind_param(self, value, dialect):

        """Process a ShortUUID to a UUID."""

        if value is None:

            return value

        if type(value) != uuid.UUID:

            value = _decode_shortuuid(value)

        return super().process_bind_param(value, dialect)

    def process_result_value(self, value, dialect):

        """Return a ShortUUID encoded UUID."""

        value = super().process_result_value(value, dialect)

        return _encode_shortuuid(value)

1. https://github.com/skorokithakis/shortuuid

[mike bayer]

looks fine to me? what did you have in mind?

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Colton Allen]

I get some issues when appending a model to an "orm.relationship" (UnorderableType error).  Which is annoying but can be worked around.

I also use SQLAlchemy-Continuum which seems to have issues with retrieving "orm.relationship"s.  It will attempt to query using the shortuuid type without transforming it to the uuid type.  Obviously this is third-party stuff but I am curious how I could go about creating my own relationship mapper.  Something that would be resilient enough to understand shortuuids need to be converted to UUIDs.

I thought there might be something obviously wrong with my implementation (since its my first one) which I why I lead off with that.

> <mailto:sqlalchemy+unsub...@googlegroups.com>.

[mike bayer]

On 05/19/2017 04:53 PM, Colton Allen wrote:
> I get some issues when appending a model to an "orm.relationship"
> (UnorderableType error). Which is annoying but can be worked around.

that doesn't sound like something in SQLAlchemy

>
> I also use SQLAlchemy-Continuum which seems to have issues with
> retrieving "orm.relationship"s. It will attempt to query using the
> shortuuid type without transforming it to the uuid type.

this sounds like the type does not have proper expression behavior. The
right-hand side of a comparison should be coerced into your type, this
depends on how the UUID type was constructed in sqlalchemy-utils.

Obviously this
> is third-party stuff but I am curious how I could go about creating my
> own relationship mapper. Something that would be resilient enough to
> understand shortuuids need to be converted to UUIDs.

adding your custom type to an MCVE that illustrates the query that is
failing would make it easy to see where it's going wrong

>
> I thought there might be something obviously wrong with my
> implementation (since its my first one) which I why I lead off with that.

I'm not sure that sqlalchemy-utils follows all the changes we've made to
the typing system over the years so it may have behaviors that no longer
work, namely the type coercion thing changed some years back.

> > an email to sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+...@googlegroups.com <javascript:>>.

> > To post to this group, send email to sqlal...@googlegroups.com

> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.

> > Visit this group at https://groups.google.com/group/sqlalchemy

> <https://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy+...@googlegroups.com>.

[Jonathan Vanasco]

side question - have you done any tests on how the UUID type queries as your database scales?  It may make sense to do the shortuuid as a text field, which you can put a substring index on.  if you don't know that postgresql optimization trick, I can explain it further.

[Colton Allen]

I do not know the optimization trick.  I'd be interested to know!  It would be nice to not have to translate to and from the UUID type.

[Jonathan Vanasco]

the postgres trick is to use partial index on the column to improve the query speed.

1. create a substring index on the table, lets say 5 characters wide:

    CREATE INDEX speed_shortuuid ON table(substr(shortuuid, 0, 5)

2. have all queries include a match against that index, calculating the substring in sql or python

     WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = substr(:input, 0, 5) 

     WHERE shortuuid = :input AND substr(shortuuid, 0, 5) = :input_substring

Postgres will only use the index if it appears exactly in the query - so you can implement that with custom compiler in sqlalchemy, so it automatically upgrades a search by shortuuid to also have the substring.  You can also do the same thing with the postgres primary key.

I use this a lot on uuids and md5s.  it takes a bit more disk space, but queries can run 100x faster.  sqlalchemy's various hooks let you consolidate the implementation and easily tweak it.