Create PostgreSQL Tables from ProtoBuf Descriptors / Definitions [GoLang]

proto-to-table.go

import (
	"fmt"
  "regexp"

	"google.golang.org/protobuf/reflect/protoreflect"
)

func IsValidTableName(tableName string) bool {
	// Table name can only contain alphanumeric characters and underscores
	validName := regexp.MustCompile(`^[a-zA-Z0-9_]+$`).MatchString(tableName)
	// Table name cannot start with a number
	startsWithNumber := regexp.MustCompile(`^[0-9]`).MatchString(tableName)
	return validName && !startsWithNumber
}

func IsValidColumnName(columnName string) bool {
	// Column name can only contain alphanumeric characters and underscores
	validName := regexp.MustCompile(`^[a-zA-Z0-9_]+$`).MatchString(columnName)
	return validName
}

// returns the column type for a given field descriptor type
func protoToPostgres(field protoreflect.FieldDescriptor) (string, error) {
	fieldType := field.Kind()
	switch fieldType {
	case protoreflect.BoolKind:
		return "boolean", nil
	case protoreflect.Int32Kind, protoreflect.Sint32Kind, protoreflect.Uint32Kind:
		return "integer", nil
	case protoreflect.Int64Kind, protoreflect.Sint64Kind, protoreflect.Uint64Kind:
		return "bigint", nil
	case protoreflect.Fixed32Kind:
		return "integer", nil // Note: PostgreSQL does not have an equivalent for uint32 or fixed32
	case protoreflect.Fixed64Kind:
		return "bigint", nil // Note: PostgreSQL does not have an equivalent for uint64 or fixed64
	case protoreflect.FloatKind:
		return "real", nil
	case protoreflect.DoubleKind:
		return "double precision", nil
	case protoreflect.StringKind, protoreflect.BytesKind, protoreflect.EnumKind:
		return "text", nil
	case protoreflect.GroupKind, protoreflect.MessageKind:
		return "jsonb", nil // Note: Assingning a JSONB type to a nested message
	default:
		return "", fmt.Errorf("unsupported field type: %v", fieldType)
	}
}

func getFieldTypeMap(messageDescriptor protoreflect.MessageDescriptor) (map[string]string, error) {
	// Create a map to hold the table schema for this message
	tableSchema := make(map[string]string)

	// Loop through all of the fields in the message descriptor
	for j := 0; j < messageDescriptor.Fields().Len(); j++ {
		fieldDescriptor := messageDescriptor.Fields().Get(j)
		// Get the PostgreSQL type for this field
		fieldType, err := protoToPostgres(fieldDescriptor)
		if err != nil {
			return nil, err
		}
		// Add the field name and type to the table schema
		tableSchema[string(fieldDescriptor.Name())] = fieldType
	}

	return tableSchema, nil
}

// builds a PostgreSQL table schema from a message descriptor
func BuildTableSchema(messageName string, messageDescriptor protoreflect.MessageDescriptor) (string, error) {
	var query string

	// validate the table name
	if !IsValidTableName(messageName) {
		return query, fmt.Errorf("invalid table name: %s", messageName)
	}

	// get table schema from proto for this message
	tableSchema, err := getFieldTypeMap(messageDescriptor)
  
  // here is a good place to add any columns that must be a part of all generated tables
  // eg: timesatamp
  tableSchema["timestamp"] = "TIMESTAMP NOT NULL"
  
	if err != nil {
		return query, fmt.Errorf("error building table schema: %v", err)
	}

	// Build the query to create the table
	lenTableSchema := len(tableSchema)
	i := 0

	query = "CREATE TABLE " + messageName + " ("

	for columnName, columnType := range tableSchema {
		if !IsValidColumnName(columnName) {
			return query, fmt.Errorf("invalid column name: %s", columnName)
		}
		// this is SQL injection safe because columnName is validated
		// and columnType is derived from a map
		query += fmt.Sprintf("\"%s\" %s", columnName, columnType)
		if i < lenTableSchema-1 {
			query += ", "
		}
		i++
	}
	query += ")"

	return query, nil
}

// Usage

// Here : Table name would ideally be the root message name from your protobuf definition
tableQuery, tableErr := BuildTableSchema(tableName, msgDescriptor)