sql

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 |
| 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 (required) | `ssl-tls` | object | Enable SSL/TLS |




## 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 (required) | `array-data` | array[object] | The array data to be inserted |



| 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 | `rows` | array[object] | The rows returned from the select operation |
| Status | `status` | string | Select status |






### 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
• func Init(bc base.Component) *component
• type Config
  • func LoadConfig(setup *structpb.Struct, ssltls *structpb.Struct) *Config
• type CreateTableInput
• type CreateTableOutput
• type DeleteInput
• type DeleteOutput
• type DropTableInput
• type DropTableOutput
• type InsertInput
• type InsertManyInput
• type InsertManyOutput
• type InsertOutput
• type SQLClient
• type SSLTLSConfig
• type SelectInput
• type SelectOutput
• type UpdateInput
• type UpdateOutput

Constants

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"
)

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"`
}