1

It's super simple to crank up a little service that persists to timescaledb in spring data. But while spring data will connect and create your schema from your model, it obviously doesn't create the hypertables that wrap your tables. What is the standard way to create your hypertables with a spring boot service using spring data?

Arlo Guthrie
  • 862
  • 3
  • 9
  • 25
  • Do you use any database migration framework, like flyway or liquibase? Probably you'll need to add a migration to call the `create_hypertable` function. I see some [hibernate interceptors](https://stackoverflow.com/questions/25283767/how-to-use-spring-managed-hibernate-interceptors-in-spring-boot/25293683#25293683) can maybe help you to rebuild the raw sql. – jonatasdp Mar 03 '22 at 11:10
  • An interceptor operates on request scope, right? I just need something at startup that creates the hypertable after the schema has been created by spring data. Maybe create a special repository with a post construct method? Surely there's a common solution for this. – Arlo Guthrie Mar 03 '22 at 14:11

1 Answers1

1

Would something like this work?

@Slf4j
@Repository
@DependsOn({"readingRepository"})
public class CustomTimescaleRepository {
    @PersistenceContext
    EntityManager entityManager;

    @PostConstruct
    @Transactional
    @Modifying
    void createHypertables() {
        log.info("CREATING HYPERTABLES");
        Query query = entityManager.createNativeQuery("SELECT create_hypertable('reading','timestamp')");
        query.getFirstResult();
    }
}
Arlo Guthrie
  • 862
  • 3
  • 9
  • 25
  • That seems to work. I could create a schema.sql to have spring data do it automatically, but then I would have to maintain BOTH the entity AND the schema.sql file for the entire development lifecycle which seems like a pain. Any suggestions to improve this would be appreciated. – Arlo Guthrie Mar 03 '22 at 22:06
  • Maybe you can introduce a new annotation mechanism that wraps such cases? – jonatasdp Mar 04 '22 at 11:50