Postgres

Description

Create an API to allow Postgres to be accessible via REST.

UI

Access the DataTrucker URL via a browser

Create a credentials

1. Go to Credentials in the sidebar > Postgres Credentials
2. Scroll down to Credential Management
3. Fill the form to create the connection
   1. Credential name – an arbitrary value to identify the credential object
   2. The hostname of the Postgres. Note, make sure the API server(not the UI) has access into Postgres
   3. Username of Postgres,
      1. Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
   4. The password of the Postgres User
   5. Database Name
   6. Port of the Database
   7. Min /Max Pool for connection pooling.

Make an API

1. Go to Database API in the Sidebar > Postgres API’s
2. Fill the form to create the API
   1. Resource Name: an arbitrary name to identify the resource
   2. The Credential linked to the resources
   3. Method: The type of REST Call
   4. Timeout: To ensure query times out after a set period in time
   5. Script: The SQL Query
      1. The SQL query can have parameters identified by ‘[[]]’ Example ‘[[id]]’ [ single quotes recommended instead of double quotes ]”
      2. Parameters are case sensitive in some cases
   6. Validation Regex of Input values, input sanitization before querying the database
      1. Examples provided in the UI when you try to create a new API

Query the resource you created

URL: /api/v1/jobs/<resource name>TYPE: <method defined>HEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>",        "<input_key>": "<value>"}
Response: 200 OK{   jsondata....}

As a CRD in Openshift / Kubernetes

For credentials use the the API below to the management end point

---apiVersion: datatrucker.datatrucker.io/v1kind: DatatruckerFlowmetadata:  name: datatruckerflow-samplespec:  Resources:    requests:      memory: "256Mi"      cpu: "250m"    limits:      memory: "256Mi"      cpu: "500m"  JobDefinitions:  - resourcename: postgres1    name: postgres1    type: DB-Postgres    credentialname: postgresdata1    tenant: Admin    restmethod: POST    script: " select CURRENT_DATE, '[[dbname]]' as db; "    job_timeout: 4354    validations:      type: object      properties:        dbname:          type: string          pattern: "^[a-z0-9]*$"          maxLength: 8  Keys:    configmap: placeholder  Scripts:    configmap: placeholder  Type: Job  DatatruckerConfig: datatruckerconfig-sample  Replicas: 1  API:    name: api    Image:      repository: docker.io      imageName: datatruckerio/datatrucker-api      tagName: latest  

API

Create a credential via REST

URL: /api/v1/credentials/TYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "credentialname": "postgresdata1",        "type": "DB-Postgres",        "hostname": "localhost",        "username": "testuser",        "password": "password",        "database": "userdb",        "port": 5432,        "minpool": 100,        "maxpool": 200}
Response: 201 OK

Create the API via REST

URL: /api/v1/resourcesTYPE: POSTHEADER: Authorization: "Bearer <JWT Token>"BODY (JSON): {        "resourcename": "postgres1",        "type": "DB-Postgres",        "credentialname": "postgresdata1",        "restmethod": "POST",        "script": " select CURRENT_DATE, '[[dbname]]' as db; ",        "job_timeout": 4354,        "validations": {          "type": "object",          "properties": {            "dbname": {              "type": "string",              "pattern": "^[a-z0-9]*$",              "maxLength": 8            }          }        }}
Response: 201 OK