sql

package
v0.27.5-beta Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Sep 18, 2024 License: MIT Imports: 21 Imported by: 0

README

---
title: "SQL"
lang: "en-US"
draft: false
description: "Learn about how to set up a VDP SQL component https://github.com/instill-ai/instill-core"
---

The SQL component is a data component that allows users to access the SQL database of your choice.
It can carry out the following tasks:

- [Insert](#insert)
- [Insert Many](#insert-many)
- [Update](#update)
- [Select](#select)
- [Delete](#delete)
- [Create Table](#create-table)
- [Drop Table](#drop-table)



## Release Stage

`Alpha`



## Configuration

The component configuration is defined and maintained [here](https://github.com/instill-ai/component/blob/main/data/sql/v0/config/definition.json).




## Setup




In order to communicate with the
external application, the following connection details need to be
provided. You may specify them directly in a pipeline recipe as key-value pairs
withing the component's `setup` block, or you can create a **Connection** from
the [**Integration Settings**](https://www.instill.tech/docs/vdp/integration)
page and reference the whole `setup` as `setup:
${connection.<my-connection-id>}`.

| Field | Field ID | Type | Note |
| :--- | :--- | :--- | :--- |
| Engine (required) | `engine` | string | Choose the engine of your database.  <br/><details><summary><strong>Enum values</strong></summary><ul><li>`MySQL`</li><li>`PostgreSQL`</li><li>`SQL Server`</li><li>`Oracle`</li><li>`MariaDB`</li><li>`Firebird`</li></ul></details>  |
| Username (required) | `username` | string | Fill in your account username  |
| Password (required) | `password` | string | Fill in your account password  |
| Database Name (required) | `database-name` | string | Fill in the name of your database  |
| Host (required) | `host` | string | Fill in the host of your database  |
| Port (required) | `port` | number | Fill in the port of your database  |
| [SSL/TLS](#ssl-tls) (required) | `ssl-tls` | object | Enable SSL/TLS  |






<details>
<summary>The <code>ssl-tls</code> Object </summary>

#### Ssl Tls

`ssl-tls` must fulfill one of the following schemas:

##### `No SSL/TLS`

| Field | Field ID | Type | Note |
| :--- | :--- | :--- | :--- |
| SSL/TLS Type | `ssl-tls-type` | string |  Must be `"NO TLS"`   |

##### `TLS`

| Field | Field ID | Type | Note |
| :--- | :--- | :--- | :--- |
| CA Certificate | `ssl-tls-ca` | string |  Base64 encoded CA certificate file    |
| SSL/TLS Type | `ssl-tls-type` | string |  Must be `"TLS"`   |

##### `mTLS`

| Field | Field ID | Type | Note |
| :--- | :--- | :--- | :--- |
| CA Certificate | `ssl-tls-ca` | string |  Base64 encoded CA certificate file    |
| Client Certificate | `ssl-tls-cert` | string |  Base64 encoded client certificate file    |
| Client Key | `ssl-tls-key` | string |  Base64 encoded client key file    |
| SSL/TLS Type | `ssl-tls-type` | string |  Must be `"mTLS"`   |

</details>



## Supported Tasks

### Insert

Perform insert operation


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_INSERT` |
| Table Name (required) | `table-name` | string | The table name in the database to insert data into |
| Data (required) | `data` | object | The data to be inserted |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Insert status |









### Insert Many

Perform insert operation with multiple rows


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_INSERT_MANY` |
| Table Name (required) | `table-name` | string | The table name in the database to insert data into |
| [Data](#insert-many-data) (required) | `array-data` | array[object] | The array data to be inserted |



<details>
<summary> Input Objects in Insert Many</summary>





</details>







| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Insert many status |









### Update

Perform update operation


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_UPDATE` |
| Table Name (required) | `table-name` | string | The table name in the database to update data into |
| Filter (required) | `filter` | string | The filter to be applied to the data with SQL syntax, which starts with WHERE clause |
| Update (required) | `update-data` | object | The new data to be updated to |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Update status |









### Select

Perform select operation


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_SELECT` |
| Table Name (required) | `table-name` | string | The table name in the database to be selected |
| Filter | `filter` | string | The filter to be applied to the data with SQL syntax, which starts with WHERE clause, empty for all rows |
| Limit | `limit` | integer | The limit of rows to be selected, empty for all rows |
| Columns | `columns` | array[string] | The columns to return in the rows. If empty then all columns will be returned |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| [Rows](#select-rows) | `rows` | array[object] | The rows returned from the select operation |
| Status | `status` | string | Select status |




<details>
<summary> Output Objects in Select</summary>





</details>






### Delete

Perform delete operation


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_DELETE` |
| Table Name (required) | `table-name` | string | The table name in the database to be deleted |
| Filter (required) | `filter` | string | The filter to be applied to the data with SQL syntax, which starts with WHERE clause |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Delete status |









### Create Table

Create a table in the database


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_CREATE_TABLE` |
| Table Name (required) | `table-name` | string | The table name in the database to be created |
| Columns (required) | `columns-structure` | object | The columns structure to be created in the table, json with value string, e.g \{"name": "VARCHAR(255)", "age": "INT not null"\} |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Create table status |









### Drop Table

Drop a table in the database


| Input | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Task ID (required) | `task` | string | `TASK_DROP_TABLE` |
| Table Name (required) | `table-name` | string | The table name in the database to be dropped |









| Output | ID | Type | Description |
| :--- | :--- | :--- | :--- |
| Status | `status` | string | Drop table status |










Documentation

Index

Constants

View Source
const (
	TaskInsert      = "TASK_INSERT"
	TaskInsertMany  = "TASK_INSERT_MANY"
	TaskUpdate      = "TASK_UPDATE"
	TaskSelect      = "TASK_SELECT"
	TaskDelete      = "TASK_DELETE"
	TaskCreateTable = "TASK_CREATE_TABLE"
	TaskDropTable   = "TASK_DROP_TABLE"
)

Variables

This section is empty.

Functions

func Init

func Init(bc base.Component) *component

Types

type Config

type Config struct {
	DBEngine     string
	DBUsername   string
	DBPassword   string
	DBName       string
	DBHost       string
	DBPort       string
	DBSSLTLSType string
}

func LoadConfig

func LoadConfig(setup *structpb.Struct, ssltls *structpb.Struct) *Config

type CreateTableInput

type CreateTableInput struct {
	TableName        string            `json:"table-name"`
	ColumnsStructure map[string]string `json:"columns-structure"`
}

type CreateTableOutput

type CreateTableOutput struct {
	Status string `json:"status"`
}

type DeleteInput

type DeleteInput struct {
	Filter    string `json:"filter"`
	TableName string `json:"table-name"`
}

type DeleteOutput

type DeleteOutput struct {
	Status string `json:"status"`
}

type DropTableInput

type DropTableInput struct {
	TableName string `json:"table-name"`
}

type DropTableOutput

type DropTableOutput struct {
	Status string `json:"status"`
}

type InsertInput

type InsertInput struct {
	Data      map[string]any `json:"data"`
	TableName string         `json:"table-name"`
}

type InsertManyInput

type InsertManyInput struct {
	ArrayData []map[string]any `json:"array-data"`
	TableName string           `json:"table-name"`
}

type InsertManyOutput

type InsertManyOutput struct {
	Status string `json:"status"`
}

type InsertOutput

type InsertOutput struct {
	Status string `json:"status"`
}

type SQLClient

type SQLClient interface {
	NamedExec(query string, arg interface{}) (sql.Result, error)
	Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
}

type SSLTLSConfig

type SSLTLSConfig struct {
	CA   string `json:"ssl-tls-ca"`
	Cert string `json:"ssl-tls-cert"`
	Key  string `json:"ssl-tls-key"`
}

type SelectInput

type SelectInput struct {
	Filter    string   `json:"filter"`
	TableName string   `json:"table-name"`
	Limit     int      `json:"limit"`
	Columns   []string `json:"columns"`
}

type SelectOutput

type SelectOutput struct {
	Rows   []map[string]any `json:"rows"`
	Status string           `json:"status"`
}

type UpdateInput

type UpdateInput struct {
	UpdateData map[string]any `json:"update-data"`
	Filter     string         `json:"filter"`
	TableName  string         `json:"table-name"`
}

type UpdateOutput

type UpdateOutput struct {
	Status string `json:"status"`
}

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL