Monday, August 3, 2015

Generate Golang struct (model) from Postgres tables

A handy Postgres SQL statement to generate Golang models (structs) for you.

WITH models AS (
  WITH data AS (
        replace(initcap(table_name::text), '_', '') table_name,
        replace(initcap(column_name::text), '_', '') column_name,
        CASE data_type
        WHEN 'timestamp without time zone' THEN 'time.Time'
        WHEN 'timestamp with time zone' THEN 'time.Time'
        WHEN 'boolean' THEN 'bool'
        -- add your own type converters as needed or it will default to 'string'
        ELSE 'string'
        END AS type_info,
        '`json:"' || column_name ||'"`' AS annotation    
    FROM information_schema.columns
    WHERE table_schema IN ('dvs_app', 'dvs_system')
    ORDER BY table_schema, table_name, ordinal_position
    SELECT table_name, STRING_AGG(E'\t' || column_name || E'\t' || type_info || E'\t' || annotation, E'\n') fields
    FROM data
    GROUP BY table_name
SELECT 'type ' || table_name || E' struct {\n' || fields || E'\n}' models
FROM models ORDER BY 1


  1. I'm running this on my db and getting an empty result.

    How do you choose what table is being searched?

  2. Looks like you need to set the scema on the line

    WHERE table_schema IN ('dvs_app', 'dvs_system')

    In my case

    WHERE table_schema IN ('dvs_app', 'dvs_system', 'public')

    1. Correct ...and although many people will use the default public schema, it's worth mentioning that this is not the recommended thing to do.