SQLAlchemy: JSON vs. PickleType vs. raw string for serialised data

[Loris Bennett]

Cameron Simpson <cs at cskk.id.au> writes:

> On 28Feb2022 10:11, Loris Bennett <loris.bennett at fu-berlin.de> wrote:
>>I have an SQLAlchemy class for an event:
>>
>>  class UserEvent(Base):
>>      __tablename__ = "user_events"
>>
>>      id = Column('id', Integer, primary_key=True)
>>      date = Column('date', Date, nullable=False)
>>      uid = Column('gid', String(64), ForeignKey('users.uid'), nullable=False)
>>      info = ??
>>
>>The event may have arbitrary, but dict-like data associated with it,
>>which I want to add in the field 'info'.  This data never needs to be
>>modified, once the event has been inserted into the DB.
>>
>>What type should the info field have?  JSON, PickleType, String, or
>>something else?
>
> I would use JSON, it expresses dicts well provided the dicts contain 
> only basic types (strings, numbers, other dicts/lists of basic types 
> recursively).
>
> I have personal problems with pickle because nonPython code can't read 
> it.
>
> Cheers,
> Cameron Simpson <cs at cskk.id.au>

Thanks for the various suggestions.  The data I need to store is just a
dict with maybe 3 or 4 keys and short string values probably of less
than 32 characters each per event.  The traffic on the DB is going to be
very low, creating maybe a dozen events a day, mainly triggered via a
command-line interface, although I will probably set up one or two cron
jobs, each of which might generate another 0 to maybe 5 records a day.

I could go for JSON (or rather LONGSTRING, as JSON is just an alias for
LONGSTRING, but JSON is not available on the version of MariaDB I am
using).  However, that seems like overkill, since I am never going to
have to store anything near 4 GB in the field.  So I should probably in
fact just use say VARCHAR(255).

WDYT?

Cheers,

Loris