databaseiotgorm

README

database-iot-gorm

A Go package providing GORM models and database migrations for IoT sensor data storage. This package contains reusable database models for various sensor types including moisture sensors, valves, water level sensors, and room sensors.

Features

• GORM Models: Pre-defined models for:

  • Devices (base model for all sensor types)
  • Battery status
  • Link quality
  • Moisture measurements
  • Valve measurements
  • Water level measurements
  • Room measurements

• Database Migrations: SQL migrations for PostgreSQL using golang-migrate

  • Common tables (devices, batteries, link_qualities)
  • Sensor-specific tables (moisture_measurements, valve_measurements, water_level_measurements, room_measurements)

• Selective Migration: Run migrations only for the sensor types you need

🚀 Getting started

Installation

go get github.com/hauke-cloud/database-iot-gorm

Import the package

import databaseiotgorm "github.com/hauke-cloud/database-iot-gorm"

Run migrations

import (
    "gorm.io/gorm"
    "go.uber.org/zap"
    databaseiotgorm "github.com/hauke-cloud/database-iot-gorm"
)

// Initialize your GORM database connection
var db *gorm.DB
var logger *zap.Logger

// Run migrations for specific sensor types
sensorTypes := []string{"moisture", "valve", "water_level", "room"}
err := databaseiotgorm.RunMigrationsForSensorTypes(db, sensorTypes, logger)
if err != nil {
    // Handle error
}

Use the models

// Create a device
device := databaseiotgorm.Device{
    DeviceID:   "moisture-sensor-01",
    DeviceName: "Garden Moisture Sensor",
    SensorType: "moisture",
    ShortAddr:  "0xBF16",
    IEEEAddr:   "0x00124b001234abcd",
}
db.Create(&device)

// Create a measurement
measurement := databaseiotgorm.MoistureMeasurement{
    DeviceID:    device.ID,
    Timestamp:   time.Now(),
    Temperature: &temperature,
    Humidity:    &humidity,
}
db.Create(&measurement)

Available Models

• Device - Base device information
• Battery - Battery status tracking
• LinkQuality - Zigbee link quality tracking
• MoistureMeasurement - Soil moisture and temperature
• ValveMeasurement - Irrigation valve status and metrics
• WaterLevelMeasurement - Water level readings
• RoomMeasurement - Room temperature and humidity

Migration Types

The package supports selective migration based on sensor types:

• common - Always runs (devices, batteries, link_qualities tables)
• moisture - Moisture sensor measurements
• valve - Valve sensor measurements
• water_level - Water level sensor measurements
• room - Room sensor measurements

📄 License

This Project is licensed under the GNU General Public License v3.0

• see the LICENSE file for details.

☕ Contributing

To become a contributor, please check out the CONTRIBUTING file.

📧 Contact

For any inquiries or support requests, please open an issue in this repository or contact us at contact@hauke.cloud.

Documentation

Index

• func RunMigrationsForSensorTypes(db *gorm.DB, sensorTypes []string, logger *zap.Logger) error
• type Battery
  • func (Battery) TableName() string
• type Device
  • func (Device) TableName() string
• type LinkQuality
  • func (LinkQuality) TableName() string
• type MoistureMeasurement
  • func (MoistureMeasurement) TableName() string
• type RoomMeasurement
  • func (RoomMeasurement) TableName() string
• type ValveMeasurement
  • func (ValveMeasurement) TableName() string
• type WaterLevelMeasurement
  • func (WaterLevelMeasurement) TableName() string

Functions

func RunMigrationsForSensorTypes

func RunMigrationsForSensorTypes(db *gorm.DB, sensorTypes []string, logger *zap.Logger) error

RunMigrationsForSensorTypes runs database migrations for the specified sensor types It always runs common migrations first, then sensor-specific migrations

Types

type Battery

type Battery struct {
	ID                uint      `gorm:"primaryKey"`
	Timestamp         time.Time `gorm:"index;not null"`
	DeviceID          uint      `gorm:"index;not null"` // Foreign key to devices table
	Device            Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	BatteryPercentage *int      // Battery percentage (0-100)
}

Battery represents battery status for a device at a specific time

func (Battery) TableName

func (Battery) TableName() string

TableName overrides the default table name

type Device

type Device struct {
	ID         uint      `gorm:"primaryKey"`
	DeviceID   string    `gorm:"uniqueIndex;size:255;not null"` // Device CR name
	DeviceName string    `gorm:"size:255"`                      // Friendly name from Tasmota
	SensorType string    `gorm:"size:50"`                       // Sensor type (moisture, valve, water_level, room)
	ShortAddr  string    `gorm:"index;size:50"`                 // Zigbee short address (e.g., "0xBF16")
	IEEEAddr   string    `gorm:"index;size:100"`                // IEEE address if available
	CreatedAt  time.Time `gorm:"not null"`
	UpdatedAt  time.Time `gorm:"not null"`
}

Device represents a sensor device in the database

func (Device) TableName

func (Device) TableName() string

TableName overrides the default table name

type LinkQuality

type LinkQuality struct {
	ID          uint      `gorm:"primaryKey"`
	Timestamp   time.Time `gorm:"index;not null"`
	DeviceID    uint      `gorm:"index;not null"` // Foreign key to devices table
	Device      Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	LinkQuality *int      // Link quality (0-255)
}

LinkQuality represents link quality for a device at a specific time

func (LinkQuality) TableName

func (LinkQuality) TableName() string

TableName overrides the default table name

type MoistureMeasurement

type MoistureMeasurement struct {
	ID          uint      `gorm:"primaryKey"`
	Timestamp   time.Time `gorm:"index;not null"`
	DeviceID    uint      `gorm:"index;not null"` // Foreign key to devices table
	Device      Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	Temperature *float64  `gorm:"type:decimal(5,2)"` // Temperature in Celsius
	Humidity    *float64  `gorm:"type:decimal(5,2)"` // Soil humidity/moisture percentage
	Endpoint    *int      // Zigbee endpoint
}

MoistureMeasurement represents a moisture sensor measurement in the database This model is used by GORM to auto-create/manage the moisture_measurements table

func (MoistureMeasurement) TableName

func (MoistureMeasurement) TableName() string

TableName overrides the default table name

type RoomMeasurement

type RoomMeasurement struct {
	ID          uint      `gorm:"primaryKey"`
	Timestamp   time.Time `gorm:"index;not null"`
	DeviceID    uint      `gorm:"index;not null"` // Foreign key to devices table
	Device      Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	Temperature *float64  `gorm:"type:decimal(5,2)"` // Temperature in Celsius
	Humidity    *float64  `gorm:"type:decimal(5,2)"` // Humidity percentage
	Endpoint    *int      // Zigbee endpoint
}

RoomMeasurement represents a room sensor measurement in the database This model is used by GORM to auto-create/manage the room_measurements table

func (RoomMeasurement) TableName

func (RoomMeasurement) TableName() string

TableName overrides the default table name

type ValveMeasurement

type ValveMeasurement struct {
	ID                    uint      `gorm:"primaryKey"`
	Timestamp             time.Time `gorm:"index;not null"`
	DeviceID              uint      `gorm:"index;not null"` // Foreign key to devices table
	Device                Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	Power                 *int      // Power state (0=off, 1=on)
	LastValveOpenDuration *int      // Duration valve was open (seconds)
	IrrigationStartTime   *int64    // Unix timestamp when irrigation started
	IrrigationEndTime     *int64    // Unix timestamp when irrigation ended
	DailyIrrigationVolume *int      // Daily irrigation volume
	Endpoint              *int      // Zigbee endpoint
}

ValveMeasurement represents a valve sensor measurement in the database This model is used by GORM to auto-create/manage the valve_measurements table

func (ValveMeasurement) TableName

func (ValveMeasurement) TableName() string

TableName overrides the default table name

type WaterLevelMeasurement

type WaterLevelMeasurement struct {
	ID        uint      `gorm:"primaryKey"`
	Timestamp time.Time `gorm:"index;not null"`
	DeviceID  uint      `gorm:"index;not null"` // Foreign key to devices table
	Device    Device    `gorm:"foreignKey:DeviceID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`
	Level     *int      // Water level value (e.g., 285)
	Endpoint  *int      // Zigbee endpoint
}

WaterLevelMeasurement represents a water level sensor measurement in the database This model is used by GORM to auto-create/manage the water_level_measurements table

func (WaterLevelMeasurement) TableName

func (WaterLevelMeasurement) TableName() string

TableName overrides the default table name