timescaledb with spring data jpa

Question

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?

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

score 2 · Answer 1 · answered Mar 03 '22 at 19:12

2

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();
    }
}

answered Mar 03 '22 at 19:12

Arlo Guthrie

1,152
3
12
28

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
This does not work. If you query the hypertables after this executes, nothing was created. We finally threw our hands up and created them with a script in the docker file. – Arlo Guthrie Apr 11 '23 at 23:34

score 1 · Answer 2 · answered Apr 04 '23 at 05:31

I'm not sure whether it's standard and not using Spring Data but one option to consider is using Flyway, db migration tool that supports Spring well.

If your service wants to add a new timescale schema you need to add SQL file on the directory that Flyway uses. Details of how to install Flyway, where to put the SQL file can be found in the link below

Flyway installation: https://flywaydb.org/documentation/usage/plugins/springboot
How to execute Flyway database migrations on startup: https://flywaydb.org/documentation/usage/plugins/springboot

And this is an example of creating a hypertable SQL (the schema that can be found in the Timescale official docs)

-- V1_1__add_stocks_real_time.sql

CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

SELECT create_hypertable('stocks_real_time','time');

CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);

If you place this SQL file in the proper place and start up the Spring server, Flyway creates a hypertable automatically like the screenshot below

timescaledb with spring data jpa

2 Answers2