Creating Hypertables through SQL Alchemy

Question

Our current project relies heavily on SQL Alchemy for table creation/data insertion. We would like to switch to timescaledb's hypertables, but it seems the recommended way to create hypertables is by executing a

create_hypertable

command. I need to be able to dynamically create tables, and so manually doing this for every table created is not really an option. One way of handling the conversion is to run a python script sending psycopg2 commands to convert all newly-created tables into hypertables, but this seems a little clumsy. Does timescaledb offer any integration with SQL Alchemy with regards to creating hypertables?

Answer 1

We currently do not offer any specific integrations with SQL Alchemy (broadly or specifically for creating hypertables). We are always interested in hearing new feature requests, so if you wanted to post your issue/use case on our Github it would help us keep better track of it for future work.

One thing that might work for your use case is to create an event trigger that executes on table creation. You'd have to check that it's in the correct schema since TimescaleDB creates its own chunk tables dynamically and you don't want to have them converted to hypertables.

See this answer for more info on event triggers: execute a trigger when I create a table

Answer 2

Here is a practical example of using event trigger to create a hyper table:

from sqlalchemy import Column, Integer, DateTime, event, DDL, orm

Base = orm.declarative_base()

class ExampleModel(Base):
    __tablename__ = 'example_model'

    id = Column(Integer, primary_key=True)
    time = Column(DateTime)


event.listen(
    ExampleModel.__table__,
    'after_create',
    DDL(f"SELECT create_hypertable('{ExampleModel.__tablename__}', 'time');")
)