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?

Answer 1

Arlo Guthrie's answer cannot work, but based on that, here is a better verion. It's really Spring style by using annotation.

import java.lang.annotation.Target;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface TimescaleTable {
    String tableName();

    String timeColumnName();
}

import java.util.Set;

import lombok.RequiredArgsConstructor;
import jakarta.annotation.PostConstruct;
import jakarta.persistence.EntityManager;
import jakarta.persistence.metamodel.EntityType;
import org.springframework.context.annotation.Configuration;

@Configuration
@RequiredArgsConstructor
public class TimescaleTableInitializer {
    private final EntityManager entityManager;

    private void createHypertable(String tableName, String timeColumnName) {
        entityManager
                .createNativeQuery(String.format(
                        "SELECT create_hypertable('%s','%s', if_not_exists => TRUE);",
                        tableName,
                        timeColumnName
                ))
                .getResultList();
    }

    @PostConstruct
    public void init() {
        // get all entities
        Set<EntityType<?>> entities = entityManager.getMetamodel().getEntities();

        // for each entity
        for (EntityType<?> entity : entities) {
            // get entity class
            Class<?> javaType = entity.getJavaType();

            // check of TimescaleTable annotation
            if (javaType.isAnnotationPresent(TimescaleTable.class)) {
                // get metadata from annotation
                TimescaleTable annotation = javaType.getAnnotation(TimescaleTable.class);
                String tableName = annotation.tableName();
                String timeColumnName = annotation.timeColumnName();

                // create hypertable
                createHypertable(tableName, timeColumnName);
            }
        }
    }
}

To use on data entity:

import java.io.Serializable;
import java.time.LocalDateTime;

import lombok.*;
import jakarta.persistence.*;

import ***.app.config.timescaledb.TimescaleTable;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = SensorData.TABLE_NAME)
@TimescaleTable(tableName = SensorData.TABLE_NAME, timeColumnName = SensorData.TIME_COLUMN_NAME)
@IdClass(SensorData.SensorDataID.class)
public class SensorData {
    public static final String TABLE_NAME = "sensor_data";
    public static final String TIME_COLUMN_NAME = "time";

    @Data
    public static class SensorDataID implements Serializable {
        private Integer id;
        private LocalDateTime time;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Id
    @Column(name = TIME_COLUMN_NAME, nullable = false)
    private LocalDateTime time;

    @Column(nullable = false)
    private String sensor;

    @Column(nullable = false)
    private String data;
}