Postgres
#
DescriptionCreate an API to allow Postgres to be accessible via REST.
#
UIAccess the DataTrucker URL via a browser
#
Create a credentials- Go to Credentials in the sidebar > Postgres Credentials
- Scroll down to Credential Management
- Fill the form to create the connection
- Credential name โ an arbitrary value to identify the credential object
- The hostname of the Postgres. Note, make sure the API server(not the UI) has access into Postgres
- Username of Postgres,
- Limit access to this user to the specific API. Please reach out to a DBA to provide specific grant access to this user
- The password of the Postgres User
- Database Name
- Port of the Database
- Min /Max Pool for connection pooling.
#
Make an API- Go to Database API in the Sidebar > Postgres APIโs
- Fill the form to create the API
- Resource Name: an arbitrary name to identify the resource
- The Credential linked to the resources
- Method: The type of REST Call
- Timeout: To ensure query times out after a set period in time
- Script: The SQL Query
- The SQL query can have parameters identified by โ[[]]โ Example โ[[id]]โ [ single quotes recommended instead of double quotes ]โ
- Parameters are case sensitive in some cases
- Validation Regex of Input values, input sanitization before querying the database
- Examples provided in the UI when you try to create a new API
#
Query the resource you createdURL: /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 / KubernetesFor 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 RESTURL: /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 RESTURL: /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