Skip to main content

SQL Lite

Description#

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

UI#

Access the DataTrucker URL via a browser

Create a credentials#

  1. Go to Credentials in the sidebar > SQLLite 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 Filename attribute is the location of the SQL server DB file as accessed from the API server.

Make an API#

  1. Go to Database API in the Sidebar > SQLLite 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: step3    name: step3    type: DB-Sqllite    credentialname: sqllitedata3    tenant: Admin    restmethod: GET    script: select date(),'[[dbname]]' as current_date;    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": "sqllitedata4",     "type": "DB-Sqllite",     "filename": "/workspace/trucker/test1.db"}
Response: 201 OK

Create the API via REST#

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