Using the demo database

For sqlcli to be useful you need two things:

database
SQLModel object(s)

Luckily, sqlcli comes with a built in command to spin up a small SQLite database and a python file with SQLModel classes you can using for experimenting.

Set up

To get started, lets first create a new directory you can use to experiment with sqlcli.

mkdir demo
cd demo

Then, run the init-demo command to create the SQLite database and a python file that contains the SQLModel definitions.

sqlcli init-demo

This will create a new sqlite database on your computer and the related SQLModel classes that you can then use to test sqlcli. The command will output two new files in your working directory:

sqlcli_demo
├── database.db # A sqlite database with demo data.
└── models.py   # SQLModel classes that map to the demo database.

With these two files you have everything you need to start working with sqlcli 🎉. The demo models are defined as follows:

sqlcli_demo/models.py

from typing import List, Optional

from sqlmodel import Field, Relationship, SQLModel


class Sport(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    athletes: List["Athlete"] = Relationship(back_populates="sport")


class Athlete(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    sport_id: Optional[int] = Field(default=None, foreign_key="sport.id")
    sport: Optional[Sport] = Relationship(back_populates="athletes")

Explore the database

Now with your database in place and your models defined you will be able to play with sqlcli! Lets try a few different commands and see what happens.

`sqlcli select`

The select command is similar to SELECT * FROM <TABLE> in SQL. You can use it to view your data.

sqlcli select -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"

$ sqlcli select -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"
# Please select a table [sport/athlete]:$ athlete
┏━━━━┳━━━━━━━━━━┳━━━━━━━━━━┓
┃ id ┃ name     ┃ sport_id ┃
┡━━━━╇━━━━━━━━━━╇━━━━━━━━━━┩
│ 1  │ Ronaldo  │ 1        │
│ 2  │ Messi    │ 1        │
│ 3  │ Beckham  │ 1        │
│ 4  │ Gretzky  │ 2        │
│ 5  │ Crosby   │ 2        │
│ 6  │ Ovechkin │ 2        │
│ 7  │ Sundin   │ 2        │
│ 8  │ Domi     │ 2        │
└────┴──────────┴──────────┘

Because you defined your models in the sqlcli_demo/models.py file sqlcli knows the tables that are in your database and will automatically prompt you to choose of the tables.

Sometimes you may already know the name of your table. In that case, you can pass the name of the table to the select command as an argument.

sqlcli select sport -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"

$ sqlcli select sport -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"
┏━━━━┳━━━━━━━━━━┳━━━━━━━━━━┓
┃ id ┃ sport_id ┃ name     ┃
┡━━━━╇━━━━━━━━━━╇━━━━━━━━━━┩
│ 1  │ 1        │ Ronaldo  │
│ 2  │ 1        │ Messi    │
│ 3  │ 1        │ Beckham  │
│ 4  │ 2        │ Gretzky  │
│ 5  │ 2        │ Crosby   │
│ 6  │ 2        │ Ovechkin │
│ 7  │ 2        │ Sundin   │
│ 8  │ 2        │ Domi     │
└────┴──────────┴──────────┘

`sqlcli insert`

The insert command is similar to INSERT INTO <TABLE> from SQL. You can use it to add new records to your database. Just like with the select command you can optionally specify the name of the table if you already know it. Try it out!

sqlcli insert athlete -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"

$ sqlcli insert athlete -d "sqlite:///sqlcli_demo/database.db" -m "sqlcli_demo/models.py"

───────────────────────── column: `id` ──────────────────────────
# <class 'int'> (optional):$ 
──────────────────────── column: `name` ─────────────────────────
# <class 'str'>:$ Carter 
────────────────────── column: `sport_id` ───────────────────────
The column `sport_id` is a foreign key related to the `sport` table. Please select from one of the 
options below from the `id` column:
     sport     
┏━━━━━━━━┳━━━━┓
┃ name   ┃ id ┃
┡━━━━━━━━╇━━━━┩
│ Soccer │ 1  │
│ Hockey │ 2  │
└────────┴────┘
# <class 'int'> (optional) [1/2]:$ 1
╭──────────────── New row successfully added 🎉 ────────────────╮
│ {'id': 9, 'sport_id': 1, 'name': 'Carter'}                    │
╰───────────────────────────────────────────────────────────────╯

Because you defined your models using SQLModel sqlcli already knows a lot about your database. You will be prompted to enter a new value for each record attribute:

Optional attributes are not required.
Foreign keys will only accept valid options and will give your a preview of the possible options.
Types are automatically enforced (e.g. integers must be integers).

Setting up environment variables

It can be annoying to specify the database URL and SQLModel modules every time. To avoid this you can set two environment variables the sqlcli will default to:

export DATABASE_URL="sqlite:///sqlcli_demo/database.db"
export MODELS_PATH="sqlcli_demo/models.py"

Now you can call the same commands but without specifying the -d and -m options:

sqlcli select athlete

$ sqlcli select athlete
┏━━━━┳━━━━━━━━━━┳━━━━━━━━━━┓
┃ id ┃ sport_id ┃ name     ┃
┡━━━━╇━━━━━━━━━━╇━━━━━━━━━━┩
│ 1  │ 1        │ Ronaldo  │
│ 2  │ 1        │ Messi    │
│ 3  │ 1        │ Beckham  │
│ 4  │ 2        │ Gretzky  │
│ 5  │ 2        │ Crosby   │
│ 6  │ 2        │ Ovechkin │
│ 7  │ 2        │ Sundin   │
│ 8  │ 2        │ Domi     │
│ 9  │ 1        │ Carter   │
└────┴──────────┴──────────┘

sqlcli select sport

$ sqlcli select sport
┏━━━━━━━━┳━━━━┓
┃ name   ┃ id ┃
┡━━━━━━━━╇━━━━┩
│ Soccer │ 1  │
│ Hockey │ 2  │
└────────┴────┘

sqlcli insert athlete

$ sqlcli insert athlete

───────────────────────── column: `id` ──────────────────────────
# <class 'int'> (optional):$ 
──────────────────────── column: `name` ─────────────────────────
# <class 'str'>:$ Carter 
────────────────────── column: `sport_id` ───────────────────────
The column `sport_id` is a foreign key related to the `sport` table. Please select from one of the 
options below from the `id` column:
     sport     
┏━━━━━━━━┳━━━━┓
┃ name   ┃ id ┃
┡━━━━━━━━╇━━━━┩
│ Soccer │ 1  │
│ Hockey │ 2  │
└────────┴────┘
# <class 'int'> (optional) [1/2]:$ 1
╭──────────────── New row successfully added 🎉 ────────────────╮
│ {'id': 9, 'sport_id': 1, 'name': 'Carter'}                    │
╰───────────────────────────────────────────────────────────────╯