Programmatic Access

❗️
This Page Is for an Old Version of bit.io
Some of the information may be out of date. If you have questions, please get in touch at [email protected]

bit.io is a Postgres-compatible database with a REST API, so programmatic access is as simple as using a Postgres connector or HTTP request library with your preferred programming language.

bit.io also currently offers a shell CLI bit and a Python SDK that further simplify programmatic interaction with bit.io.

Below, we demonstrate programmatic access with the bit CLI, cURL, Python, and Node.js for four core operations:

Connecting and listing repos
Creating a new repo
Uploading data from a file
Importing data from a URL
Querying data

For other SQL-query based operations, see our supported sql section. For other REST API based operations, see our API reference.

Connect

These examples show how to connect to bit.io and list your repos programmatically.

# You can connect the CLI by passing an API key as an environment variable
BITIO_KEY=<YOUR_BITIO_KEY> bit repo list

# You can also pass the API key as an optional argument
bit -k <YOUR_BITIO_KEY> repo list

curl --request GET \
     --url https://api.bit.io/api/v1beta/repos/ \
     --header 'Authorization: Bearer <YOUR_BITIO_KEY>' \
     --header 'Accept: application/json'

import bitdotio

# Create bit.io connection client object b
b = bitdotio.bitdotio(<YOUR_BITIO_KEY>)

# You can use the b object directly, here we list repos
b.list_repos()

# The b object also provides access to a psycopg2 cursor for arbitrary SQL 
conn = b.get_connection()
cur = conn.cursor()

const { Client } = require('pg');

const client = new Client({
    database: 'bitdotio',
    host: 'db.bit.io',
    port: 5432,
    user: '<YOUR_USERNAME>',
    password: '<YOUR_BITIO_KEY>'

});

client.connect();

// List owned repos
let sql = 'SELECT nspname FROM pg_namespace WHERE nspname LIKE $1;'
client.query(sql, ['<YOUR_USERNAME>%'], (err, res) => {
    console.table(res.rows);
    client.end()
});

Create a repo

These examples show how to create repos programmatically on bit.io.

bit -k <YOUR_BITIO_KEY> repo create -r my_new_repo

curl --request POST \
     --url https://api.bit.io/api/v1beta/repos/ \
     --header 'Accept: application/json' \
     --header 'Content-Type: application/json' \
     --header 'Authorization: Bearer <YOUR_BITIO_KEY>' \
     --data '
{
     "name": "my_new_repo",
     "description": My new repo."
}
'

import bitdotio

# Connect to bit.io
b = bitdotio.bitdotio(<YOUR_BITIO_KEY>)

# Construct a repo object
r = bitdotio.model.repo.Repo(name='my_new_repo',
                             description='My new repository.',
                             is_private=True)

# Create the repo
b.create_repo(repo=r)

# Confirm repo creation
b.list_repos()

const fetch = require('node-fetch');

const url = 'https://api.bit.io/api/v1beta/repos/';
const options = {
  method: 'POST',
  headers: {
    Accept: 'application/json',
    'Content-Type': 'application/json',
    Authorization: 'Bearer <YOUR_BITIO_KEY>'
  },
  body: JSON.stringify({
    name: 'my_new_repo',
    is_private: true,
    description: 'My new repository'})
};

fetch(url, options)
  .then(res => res.json())
  .then(json => console.log(json))
  .catch(err => console.error('error:' + err));

Upload data from a file

These examples show how to programmatically upload data from a file to bit.io.

bit -k <YOUR_BITIO_KEY> import file -r my_new_repo -f video-game-sales.csv -t video_game_sales_csv

# Download demo csv here:
# https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv
# Upload to 'my_new_repo' with table name 'video_game_sales_csv'
curl -i -X POST \
--header 'Authorization: Bearer <YOUR_BITIO_KEY>' \
--header "Content-Disposition: attachment;filename='video-game-sales.csv'" \
--data-binary @"<YOUR_PATH>/video-game-sales.csv" \
https://import.bit.io/<YOUR_USERNAME>/my_new_repo/video_game_sales_csv

import bitdotio

# Connect to bit.io
b = bitdotio.bitdotio(<YOUR_BITIO_KEY>)

# Download demo csv here:
# https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv
# Upload to 'my_new_repo' with table name 'video_game_sales_csv'
with open('video-game-sales.csv', 'r', encoding='utf-8') as f:
    b.create_import_file(f, 'my_new_repo', 'video_game_sales_csv')

// Upload data to a bit.io repo using Node
const fetch = require('node-fetch');
let fs = require('fs');

// Download demo csv here:
// https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv
// Upload to 'my_new_repo' with table name 'video_game_sales_csv'
const url = 'https://import.bit.io/<YOUR_USERNAME>/my_new_repo/video_game_sales_csv';
let stream = fs.readFileSync('video-game-sales.csv');

const options = {
    method: 'POST',
    headers: {
        Authorization: 'Bearer <YOUR_BITIO_KEY>',
        'Content-Disposition': 'attachment;filename="video-game-sales.csv"'
    },
    body: stream
}

fetch(url, options)
  .then(res => res.json())
  .then(json => console.log(json))
  .catch(err => console.error('error:' + err));

Import data from a URL

These examples show how to programmatically import data from a URL to bit.io.

URL=https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv
bit -k <YOUR_BITIO_KEY> import url -r my_new_repo -u $URL -t video_game_sales_url

# Upload to 'my_new_repo' with table name 'video_game_sales_url'
curl --request POST \
     --header 'Authorization: Bearer <YOUR_BITIO_KEY>' \
     --url https://api.bit.io/api/v1beta/import/url/ \
     --header 'Accept: application/json' \
     --header 'Content-Type: application/json' \
     --data '
{
     "table_name": "video_game_sales_url",
     "repo_name": "my_new_repo",
     "url": "https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv"
}
'

# Upload to 'my_new_repo' with table name 'video_game_sales_url'
url = 'https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv'
import_url = bitdotio.model.import_url.ImportUrl(url, 'video_game_sales_url', 'my_new_repo')
b.create_import_url(import_url=import_url)

const fetch = require('node-fetch');

const source_url = 'https://raw.githubusercontent.com/bitdotioinc/connection_snippets/main/video-game-sales.csv'
const url = 'https://api.bit.io/api/v1beta/import/url/';
const options = {
  method: 'POST',
  headers: {
    Accept: 'application/json',
    'Content-Type': 'application/json',
    Authorization: 'Bearer <YOUR_BITIO_KEY>'
  },
  body: JSON.stringify({
    create_table_if_not_exists: true,
    url: source_url,
    table_name: 'video_game_sales_url',
    repo_name: 'my_new_repo'})
};

fetch(url, options)
  .then(res => res.json())
  .then(json => console.log(json))
  .catch(err => console.error('error:' + err));

Query data

These examples show how to query data from bit.io.

SQL="SELECT date, name, local_price FROM \"bitdotio/big_mac_index\".\"big_mac_index\"" 
bit -k <YOUR_BITIO_KEY> query -q $SQL

# Pipe a query to bit
echo  "SELECT date, name, local_price FROM \"bitdotio/big_mac_index\".\"big_mac_index\"" \
| bit -k <YOUR_BITIO_KEY> query -qf -

curl --request POST \
     --url https://api.bit.io/api/v1beta/query/ \
     --header "Authorization: Bearer <YOUR_BITIO_KEY>" \
     --header "Accept: application/json" \
     --header "Content-Type: application/json" \
     --data '
{
     "query_string": "SELECT date, name, local_price FROM \"bitdotio/big_mac_index\".\"big_mac_index\";"
}
'

import bitdotio

# Connect to bit.io
b = bitdotio.bitdotio(<YOUR_BITIO_KEY>)

# Get psycopg2 cursor
with b.get_connection() as conn:
    cur = conn.cursor()
    cur.execute('SELECT date, name, local_price FROM "bitdotio/big_mac_index"."big_mac_index"')
    print(cur.fetchone())

const { Client } = require('pg');

const client = new Client({
    database: 'bitdotio',
    host: 'db.bit.io',
    port: 5432,
    user: '<YOUR_USERNAME>',
    password: '<YOUR_BITIO_KEY>'

});

client.connect();

let sql = 'SELECT date, name, local_price FROM "bitdotio/big_mac_index"."big_mac_index";'
client.query(sql, (err, res) => {
    console.table(res.rows);
    client.end()
});

❗️This Page Is for an Old Version of bit.io

Connect

Create a repo

Upload data from a file

Import data from a URL

Query data

❗️
This Page Is for an Old Version of bit.io