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 (
    SELECT
        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

3 comments:

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

    How do you choose what table is being searched?

    ReplyDelete
  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')
    ```

    ReplyDelete
    Replies
    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.

      Delete