pgson - PostgreSQL Schema Generator

Generate a PostgreSQL schema from a defined JSON schema.

⭐️ Only supports PostgreSQL 16+

Developed by SecNex and Björn Benouarets

Features

• DDL generation
• DML generation
• Data validation
• Hashing support for sensitive data
• SQL injection protection

Supported Data Types

• string
• int
• float
• bool
• date
• time
• datetime
• uuid
• json
• hash

Supported Hashing Algorithms

• argon2
• bcrypt
• md5
• sha256
• sha512

Example

{
    "table": "users",
    "schema": [
        {
            "name": "id",
            "type": "uuid",
            "primary": true,
            "comment": "The unique identifier for the user"
        },
        {
            "name": "email",
            "type": "string",
            "nullable": false,
            "unique": true,
            "comment": "The email address for the user"
        },
        {
            "name": "password",
            "type": "hash",
            "nullable": false,
            "comment": "The password for the user",
            "algorithm": "argon2"
        },
        {
            "name": "organization_id",
            "type": "uuid",
            "nullable": false,
            "comment": "The organization ID for the user",
            "references": {
                "table": "organizations",
                "column": "id",
                "onDelete": "CASCADE",
                "onUpdate": "CASCADE"
            }
        }
    ]
}

Output

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE ON UPDATE CASCADE
)

Installation

go install git.secnex.io/secnex/pgson

Usage

package main

import (
	"git.secnex.io/secnex/pgson"
)

func main() {
	schema, err := pgson.NewSchemaFromFile("schema.json")
	if err != nil {
		log.Fatalf("Failed to load schema: %v", err)
	}
    createSQL, err := schema.CreateSQL()
	if err != nil {
		log.Fatalf("Failed to create SQL: %v", err)
	}
	fmt.Println(createSQL)
}