Generating Code Based on SQL+JSON+ custom templates

Tips

Requires sponge v1.11.0+

Overview

Generate code corresponding to tables (not limited to Go code) by combining custom templates with SQL. Only two main parameters are required: database connection information and template code path. Supports batch code generation for multiple tables.

Applicable Scenarios:

• Backend CRUD related code (e.g., api, router, service, dao, model, etc.).
• Frontend CRUD related page code.
• CRUD code for various ORMs.

Preparation

Sponge supports database types mysql, mongodb, postgresql, sqlite. Taking mysql as an example below, environment requirements:

• sponge installed
• mysql service
• mysql tables

Tips

Code generation relies on the mysql service and database tables. You can start the mysql service using the docker script and then import the example SQL.

Fixed vs. Custom Fields

The code generation feature supports two types of fields:

• Fixed Fields
• Custom Fields

Whether fixed or custom fields, each field corresponds to a placeholder in the template code.

Fixed Fields: These are fields automatically parsed from SQL and cannot be changed. An example of fixed fields corresponding to a 'teacher' table is as follows:

{
    "TableColumnSubMessage": "",
    "TableColumnSubStructure": {},
    "TableColumns": [
        {
            "ColumnComment": "",
            "ColumnName": "id",
            "ColumnNameCamel": "ID",
            "ColumnNameCamelFCL": "id",
            "GoType": "uint64",
            "IsPrimaryKey": true,
            "Tag": ""
        },
        {
            "ColumnComment": "user name",
            "ColumnName": "name",
            "ColumnNameCamel": "Name",
            "ColumnNameCamelFCL": "name",
            "GoType": "string",
            "IsPrimaryKey": false,
            "Tag": ""
        }
    ],
    "DBDriver": "mysql",
    "TablePrimaryKey": {
        "GoType": "uint64",
        "GoTypeFCU": "Uint64",
        "IsStringType": false,
        "Name": "id",
        "NameCamel": "ID",
        "NameCamelFCL": "id",
        "NamePluralCamel": "IDs",
        "NamePluralCamelFCL": "ids"
    },
    "TableComment": "teacher table",
    "TableName": "teacher",
    "TableNameCamel": "Teacher",
    "TableNameCamelFCL": "teacher",
    "TableNamePluralCamel": "Teachers",
    "TableNamePluralCamelFCL": "teachers",
    "TableNamePrefix": ""
}

Custom Fields: Custom fields are optional and are defined in a JSON file only when required by the template. For example, create a file named fields.json with the following content:

{
 "ModuleName": "user",
 "PackageName": "handler",
 "ServerName": "web",
 "Port": 8080
}

Warning

In custom fields, please avoid using the following field names, as they conflict with the names of fixed fields.

DBDriver
TablePrimaryKey

TableNamePrefix
TableName
TableNameCamel
TableNameCamelFCL
TableNamePluralCamel
TableNamePluralCamelFCL
TableNameSnake
TableComment

TableColumns
TableColumnSubStructure
TableColumnSubMessage

Creating Custom Template Code

Template code is the core of code generation, implemented based on Go's text/template library. Therefore, it is recommended to first familiarize yourself with its basic syntax rules. The syntax rules are simple and can be learned in a few minutes. Click to view the chapter: Go text/template Basic Syntax Rules.

It is recommended to use variables for template filenames (e.g., {{.TableNameCamelFCL}}.go.tmpl) to avoid filename conflicts when generating code for multiple tables in batch. Below is an example of a custom CRUD template code:

package {{.PackageName}}

import (
 "net/http"
 "github.com/gorilla/mux"
)

type {{.TableNameCamelFCL}}Handler struct {}

func (h *{{.TableNameCamelFCL}}Handler) Create{{.TableNameCamel}}(w http.ResponseWriter, r *http.Request) {
 w.Write([]byte("{{.TableName}} created"))
}

func (h *{{.TableNameCamelFCL}}Handler) Delete{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}(w http.ResponseWriter, r *http.Request) {
 w.Write([]byte("{{.TableName}} deleted"))
}

func (h *{{.TableNameCamelFCL}}Handler) Update{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}(w http.ResponseWriter, r *http.Request) {
 w.Write([]byte("{{.TableName}} updated"))
}

func (h *{{.TableNameCamelFCL}}Handler) Get{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}(w http.ResponseWriter, r *http.Request) {
 w.Write([]byte("{{.TableName}} found"))
}

func (h *{{.TableNameCamelFCL}}Handler) List{{.TableNameCamel}}(w http.ResponseWriter, r *http.Request) {
 w.Write([]byte("List of {{.TableName}}"))
}

func Register{{.TableNameCamel}}Routes(r *mux.Router) {
 r.HandleFunc("/{{.TableNameCamelFCL}}", Create{{.TableNameCamel}}).Methods("POST")
 r.HandleFunc("/{{.TableNameCamelFCL}}/{ {{.PrimaryKey.Name}} }", Delete{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}).Methods("DELETE")
 r.HandleFunc("/{{.TableNameCamelFCL}}/{ {{.PrimaryKey.Name}} }", Update{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}).Methods("PUT")
 r.HandleFunc("/{{.TableNameCamelFCL}}/{ {{.PrimaryKey.Name}} }", Get{{.TableNameCamel}}By{{.PrimaryKey.NameCamel}}).Methods("GET")
 r.HandleFunc("/{{.TableNameCamelFCL}}", List{{.TableNameCamel}}).Methods("GET")
}

Place the template files in a directory (e.g., template), which can contain multiple template files and subdirectories.

Generating Code

Execute the command sponge run in the terminal to enter the code generation UI interface. The specific steps are as follows:

1. Enter the left menu bar [Generate Custom Code] → [SQL];

2. Fill in the following parameters (hover the mouse over the question mark ? next to the parameter to view the parameter description):

   • Database Type: e.g., mysql
   • Database DSN: e.g., root:123456@(127.0.0.1:3306)/testdb
   • Click Get Table Names, select one or more tables;
   • Template Directory Path: e.g., /home/user/template/api/crud;
   • Custom Fields JSON File (Optional): e.g., fields.json.

3. Click the Download Code button to generate the code, as shown in the figure below:

template-sql

Equivalent Command

sponge template sql --db-driver=mysql --db-dsn=root:123456@(192.168.3.37:3306)/school --db-table=teacher --tpl-dir=/home/user/template/api/crud --fields=fields.json

Tips

Click the button View Template Info to view the field information. This information corresponds to the placeholders in the template, making it easier to write template code.