CreateLaravelHyperTable.php

<?php


namespace App;


use Illuminate\Support\Facades\DB;

class CreateLaravelHypertable
{
    /**
     * Check if database type is pgsql, if so, check if timescaledb extension is installed and convert Laravel table to hypertable.
     * @return bool
     * @throws \Exception
     * @var string $table
     */
    public static function table(string $table, string $column = 'created_at'): bool
    {
        if(env('DB_CONNECTION') == 'pgsql')
        {
            $test = DB::select("SELECT * FROM pg_available_extensions where name ='timescaledb'");

            if(!empty($test))
            {
                DB::select("DROP TABLE IF EXISTS {$table}_old CASCADE");
                DB::select("CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE");
                DB::select("ALTER TABLE $table RENAME TO {$table}_old");
                DB::select("CREATE TABLE $table (LIKE {$table}_old INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES)");
                DB::select("ALTER TABLE $table DROP COLUMN id");
                DB::select("ALTER TABLE $table ADD COLUMN id SERIAL");

                DB::select("DROP TABLE IF EXISTS {$table}_old CASCADE");

                DB::select("SELECT create_hypertable('$table', '$column')");

                return true;
            }
            throw new \Exception('timescaledb not installed');
        }
        throw new \Exception('Only pgsql supported');
    }
}

/**
 * For timescaleDB to function with Laravel, we need to make the created_at (or some other timestamp column)
 * The primary key. We need to drop the primary 'id' column and convert it to a SERIAL column to maintain
 * Eloquent relations etc.
 * 
 * Usage: Put CreateLaravelHyperTable::table('name_of_table');
 * in a Laravel migration, after the Schema::create statement.
 * Example:
 * Schema::create('user_login_logs', function(Blueprint $table) {
 *    $table->increments('id');
 *    $table->bigInteger('user_id)->index();
 *    $table->timestamps();
 * });
 *
 * CreateLaravelHypertable::table('user_login_logs');
 *
 **/

Thank you!