Learn how to write your own external data connectors for budibase.
What are External Data Connectors?
External data connectors are modules that allow you to connect your budibase application to a whole host of database and API technologies. You can connect your budibase apps to relational databases, HTTP APIs and more. You can find a list of all the currently supported budibase data connectors here.
Data Connector Structure
To see real world examples of data connectors in budibase, have a look through the existing ones here.
Data connectors are made up of datasources and queries.
A datasource usually points to a single database instance or API.
Queries are the different data fetching operations against a datasource.
For example, your local postgres instance would be a datasource, and you can create a whole host of different queries against that postgres instance. You can bind budibase UI components to queries - such as grids, tables, repeaters and more.
When writing a data connector, you need to define two things in your code:
Schema - This defines the options for the data connector.
Integration Code - A JavaScript class containing the actual code that will be executed by budibase when executing operations against the datasource. The integration class must contain create, read, update and delete methods.
Tutorial: Creating a MySQL Data Connector
Let's walk through an example. We are going to create a budibase data connector for MySQL, the popular RDBMS**.**
Steps
Create schema and integration code
Add an icon for the integration
Test our integration
Setup
If you already have MySQL setup, feel free to skip this section and use your own existing MySQL database for this tutorial.
If you haven't installed MySQL, you will need to install it (docs here). Alternatively, you can run MySQL inside docker, which is the approach we are going to take. Install docker, and run the following command to run MySQL on your machine.
docker run --name budi-mysql -p3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql
(Optional) Seed your database
You may want to set up a test database and some tables for testing your integration.
Open up the MySQL command line client, or use your favourite MySQL GUI client to connect to your MySQL database. If you are using docker, you can run the MySQL command line client inside docker by running the following command:
docker exec -it budi-mysql mysql -p my-secret-pw
Create a database
Once connected, run the following **** commands to set up your database.
create database budibase;
use budibase;
Create a Test Table
create table people (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30),
email VARCHAR(50),
age INT
);
Create data
INSERT INTO `people` (firstname, email, age) VALUES ('test', 'test@test.com', 25);
Ok, we are now ready to write our data connector!
Finding a Library
To implement our data connector, we need a library that will allow the budibase server (Built on NodeJS) to connect to our database. The simplest choice here would be to just use the official MySQL node connector, a hugely popular and mature library.
Let's install this libary in the server directory.
cd packages/server
yarn add mysql
We have now installed MySQL and we are ready to write our integration. Create a new file in packages/server/src/integrations/mysql.js with the following contents:
const mysql = require("mysql")
const SCHEMA = {
// Optional link to docs, which gets shown in the UI.
docs: "https://github.com/mysqljs/mysql",
datasource: {},
query: {},
}
class MySQLIntegration {}
module.exports = {
schema: SCHEMA,
integration: MySQLIntegration,
}
Now it's time to start setting up our schema. You will notice that we've created the main building blocks of a budibase data connector (schema, integration class), and exported them. This means we can now just focus on our implementation by filling them out. Let's start by creating our datasource configuration. When you look at the docs for the node MySQL connector, we are told how to configure the client, which looks a little like this:
In order to allow users to add these values through the budibase builder, we will need to update our code to look like below. Notice how we have created schema values to match the ones the MySQL client expects. The user fills these values in from the builder and they get passed to our integration.
const mysql = require("mysql")
const SCHEMA = {
docs: "https://github.com/mysqljs/mysql",
datasource: {
host: {
type: "string",
default: "localhost",
required: true,
},
user: {
type: "string",
default: "root",
required: true,
},
password: {
type: "password",
default: "root",
required: true,
},
database: {
type: "string",
required: true,
},
},
query: {},
}
class MySQLIntegration {
constructor(config) {
/**
* This is the config passed in from the schema. It should look something like this, with your own values:
* {
* host: "localhost",
* user: "myuser",
* password: "somepassword",
* database: "mydatabase"
* }
* */
this.config = config
this.client = mysql.createConnection(config)
}
}
module.exports = {
schema: SCHEMA,
integration: MySQLIntegration,
}
Following our schema configuration, the next part is to create a query. We are going to create a simple query to allow us to read data from our MySQL instance. As mentioned in the Data Connector Structure **** section, integration methods must be one of the following:
create()
read()
update()
delete()
Let's write the code for our read() method.
const mysql = require("mysql")
const SCHEMA = {
docs: "https://github.com/mysqljs/mysql",
datasource: {
host: {
type: "string",
default: "localhost",
required: true,
},
user: {
type: "string",
default: "root",
required: true,
},
password: {
type: "password",
default: "root",
required: true,
},
database: {
type: "string",
required: true,
},
},
query: {
// The read query takes SQL as a parameter
read: {
type: "sql",
},
},
}
class MySQLIntegration {
constructor(config) {
/**
* This is the config passed in from the schema. It should look something like this, with your own values:
* {
* host: "localhost",
* user: "myuser",
* password: "somepassword",
* database: "mydatabase"
* }
* */
this.config = config
this.client = mysql.createConnection(config)
}
read(query) {
// Node MySQL is callback based, so we must wrap our call in a promise
return new Promise((resolve, reject) => {
this.client.connect()
return this.client.query(query.sql, (error, results) => {
if (error) return reject(error)
resolve(results)
this.client.end()
})
})
}
}
module.exports = {
schema: SCHEMA,
integration: MySQLIntegration,
}
Next we need to add our integration to packages/server/src/integrations/index.js. This exports our integration and makes it available to the builder.
We can now test our new integration from the builder frontend! Open up the budibase builder and create a new application. Go to Data Sources and click the plus icon to add a new datasource. Choose our new MySQL integration. There is no icon there for MySQL, but we will add that later on. Click MySQL and you will now see your integration options in the UI! Fill in your integration options and click Create.
You will be presented with the datasource configuration screen. You can use this screen to edit your datasource configuration should it change. Let's create a query now so we can fetch our data. Click the Create Query button.
Write your query and click the Run Query button. This will execute your query and determine the schema so budibase knows what columns you have in your table.
It works! We can now save our query and use it in the design section of the builder. This concludes the end to end for our datasource and query configuration. Completing the integration is a matter of filling out the remaining methods in the integration class. **** We are not going to go through all of the separate CRUD methods in the scope of this tutorial - you can see the fully completed MySQL integration here. You also do not need to implement all of the CRUD methods for an integration. It's your choice how many you want your integration to have.
Adding Our MySQL Icon
To add an icon for your integration, you must add an SVG file with the icon to packages/builder/src/components/backend/DatasourceNavigator/icons.
Create `packages/builder/src/components/backend/DatasourceNavigator/icons/MySQL.svelte` with the following contents:
Now we need to update packages/builder/src/components/backend/DatasourceNavigator/icons/index.js to include our new icon.
import Postgres from "./Postgres.svelte"
import DynamoDB from "./DynamoDB.svelte"
import Elasticsearch from "./Elasticsearch.svelte"
import MongoDB from "./MongoDB.svelte"
import CouchDB from "./CouchDB.svelte"
import S3 from "./S3.svelte"
import Airtable from "./Airtable.svelte"
import SqlServer from "./SQLServer.svelte"
import MySQL from "./MySQL.svelte"
export default {
POSTGRES: Postgres,
DYNAMODB: DynamoDB,
MONGODB: MongoDB,
ELASTICSEARCH: Elasticsearch,
COUCHDB: CouchDB,
SQL_SERVER: SqlServer,
S3: S3,
AIRTABLE: Airtable,
MYSQL: MySQL,
}
Our icon has been added! Now when we open the datasource creation menu, the builder will contain our new icon.
Data Connector Schema Reference
Here is the total schema for budibase data connectors. This is written using the TypeScript type notation.
type QueryType = "string" | "fields" | "sql" | "json"
type FieldType = "string" | "password" | "number"
interface DatasourceKey = {
type: QueryType;
default: string | number | boolean;
required: boolean
}
interface QueryFieldConfig = {
type: FieldType;
required: boolean;
default: string | number | boolean;
}
interface QueryConfig = {
type: QueryType;
// Allows you to pass extra data as JSON. Good for Create/Update Queries
customisable?: boolean;
fields: Record<string, QueryFieldConfig>;
}
// The schema that you must export
interface IntegrationSchema = {
// link to documentation that will be displayed in the UI
docs?: string,
// Fields for configuring your datasource
datasource: Record<string, DatasourceKey>;
// Definitions for each of your queries
query: {
create?: QueryConfig,
read?: QueryConfig,
update?: QueryConfig,
delete?: QueryConfig,
},
}
Making a Contribution
We are always excited to hear ideas from the community and to accept contributions for new Budibase data connectors.
