CRUD To-Do List App

Suggest Edits

❗️

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 versatile

Bit.io is more than just a static tool for analyzing sets of data. It is a functional Postgres database that has limitless use cases as a lightweight and performant datastore for your applications and projects. In this guide we will explore how to use bit.io in creating a TODO application to track our daily tasks.

300

The Project

You can access the source code for this project here: https://github.com/yash-bitio/bit.io-CRUD-demo

Once you clone the project you'll need to install a few things:

git clone https://github.com/yash-bitio/bit.io-CRUD-demo.git
cd bit.io-CRUD-demo

pip install eel bitdotio uuid

cd src
npm install

The application uses Eel and React. Throughout the course of this guide, we won't cover exactly how these frameworks work. But, we will go through how bit.io manipulates data to Create, Read, Update, and Delete lists and tasks step-by-step.

Connecting to bit.io

When our application starts, the first thing we want to do is authenticate with bit.io and make sure that we have a repo with the correct tables and schema for us to use.

In just a few lines, we can:

  1. Connect to bit.io
  2. Create our repo if it does not exist
  3. Create tables for lists and messages

All together that looks like this:

# 1
b = bitdotio.bitdotio("YOUR API KEY")
username = "YOUR_USERNAME"
conn = b.get_connection()
cur = conn.cursor()

def create_repo():
  # 2
  try:
    r = bitdotio.model.repo.Repo(name=repo_name)
    r.description = "Our to-do application"
    r.is_private = True
    b.create_repo(repo=r)
  except:
      pass

  # 3
  cur.execute(f'CREATE TABLE IF NOT EXISTS "{username}/{repo_name}"."lists" \
      (list_name TEXT, key TEXT, archived BOOLEAN, created DATE, updated DATE)')

  cur.execute(f'CREATE TABLE IF NOT EXISTS "{username}/{repo_name}"."messages" \
      (message TEXT, key TEXT, id TEXT, completed BOOLEAN, created DATE, updated DATE)')

CREATE TABLE IF NOT EXISTS Is a SQL command that will create a new table with the schema specified if it can not find a table with the name we specified (in this case "lists" or "messages") within our repo.

If you go to https://bit.io/*YOUR_USERNAME*/to_do_view you can see the empty tables that were just created.

3584 3584

CRUD

After setting up our repo the rest is easy! Bit.io uses Postgres at its core so queries you would use to interact with any other SQL database work here too.

Creating a new lists is as simple as Inserting a new row into the lists table:

def createNewList(list_name):
    cur.execute(f'INSERT INTO "{username}/{repo_name}"."lists" \
    VALUES (\'{list_name}\', \'{uuid.uuid4()}\', false, \'{datetime.datetime.now(timezone.utc)}\', \'{datetime.datetime.now(timezone.utc)}\')')

And creating a new tasks is as simple as adding a new messages to our message table:

def createNewItem(item_name, key):
    cur.execute(f'INSERT INTO "{username}/{repo_name}"."messages" \
    VALUES (\'{item_name}\', \'{key}\', \'{uuid.uuid4()}\', false, \'{datetime.datetime.now(timezone.utc)}\', \'{datetime.datetime.now(timezone.utc)}\')')

We can also update the status of tasks and delete them if we no longer need them

def updateItem(id, status):
    cur.execute(f'UPDATE "{username}/{repo_name}"."messages" \
    SET completed = {status} WHERE id = \'{id}\'')
    
def delete_item(id):
    cur.execute(f'DELETE FROM "{username}/{repo_name}"."messages" \
    WHERE id = \'{id}\'')

Finally we can fetch all the data we need using the information schema to structure an object we can later parse in our UI.

def getLists():
    cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name = '{}' AND table_schema = '{}/{}'".format('lists', username, repo_name))
    columns = cur.fetchall()
    list_columns_unzipped = [item for t in columns for item in t]

    cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name = '{}' AND table_schema = '{}/{}'".format('messages', username, repo_name))
    columns = cur.fetchall()
    messages_columns_unzipped = [item for t in columns for item in t]

    cur.execute(f'SELECT * FROM "{username}/{repo_name}"."lists" \
    WHERE archived = false')
    data = cur.fetchall()

    all_to_do_lists = []

    for list_item in data:
        zip_iterator = zip(list_columns_unzipped, list(list_item))
        to_do_list = dict(zip_iterator)

        to_do_list['list_items'] = []

        key = to_do_list['key']
        cur.execute(f'SELECT * FROM "{username}/{repo_name}"."messages" WHERE key = \'{key}\'')
        message_data = cur.fetchall()

        for message in message_data:
            zip_iterator = zip(messages_columns_unzipped, list(message))
            message = dict(zip_iterator)
            to_do_list['list_items'].append(message)

        all_to_do_lists.append(to_do_list)

    return all_to_do_lists

Updated 12 months ago