How to create or alter a DB schema dynamically (at run time) using Gramex FormHandler

I want to be able to (at run time) create or alter a DB schema dynamically on a particular event (e.g. click of a button) using FormHandler microservice of Gramex.

>Solution :

FormHandler supports defining columns in the spec.

For example, this configuration creates a table called profile with 4 columns: user, password, age and id.

url:
    handler: FormHandler
    kwargs:
      url: 'postgresql://$USER:$PASS@server/db'       # Pick any database
      table: profile              # Pick any table name to create
      id: id                      # The "id" column is primary key
      # Define your table's columns
      columns:
        user: TEXT                # Use any SQL type allowed by DB
        password: VARCHAR(40)     # including customizations
        age:
          type: INTEGER           # You can also specify as a dict
          nullable: true          # Allows NULL values for this field
          default: 0              # that default to zero
        id:
          type: INTEGER           # Define an integer ID column
          primary_key: true       # as a primary key
          autoincrement: true     # that auto-increments

But if this needs to be changed at runtime, e.g. when a user clicks on a button, you can use a FunctionHandler with gramex.data.alter()

For example, add this to your gramex.yaml:

url:
  alter:
    pattern: /alter
    handler: FunctionHandler
    kwargs:
      # You can decide the columns dynamically here
      function: >
        gramex.data.alter(url, table, columns={
           col: 'TEXT' for col in handler.args.get('col', [])
        })

When /alter?col=email is called, the function adds an email column as text.

NOTE: There’s no option to DELETE columns.