Add new step to `piccolo asgi new` for selecting the database to be used

[thetreythomas]

Wondering how difficult it would be to eventually add a third choice in the piccolo asgi new for selecting the database...

I'm just setting up a Litestar + Piccolo environment for the first time, and used the piccolo asgi new to get started and found that it defaults to Postgres. Since I am just messing around, using this as a testbed to learn, I am using sqlite.

Looking through your documentation, it didn't take much to make the changes and get everything working, but adding something like this would be a nice QoL addition to what is already a very nice boilerplate. Giving the user the choice of all three databases you all support.

[dantownsend]

It's a good idea. CockroachDB should slot in pretty seamlessly. SQLite doesn't work quite as well with Piccolo's auto migrations, due to limited alter table support, but we can figure something out (maybe a new CLI command like piccolo tables create which creates all tables in the database without using migrations). We can then suggest this instead of running migrations in the index.html file.

[sinisaos]

@dantownsend I agree that for CockroachDB it should be easy because CockroachDB uses auto migration just like Postgres and we just need to change the database engine in piccolo_conf.py, but it's different for Sqlite because it doesn't use migrations, opening and closing db pool etc.

but we can figure something out (maybe a new CLI command like piccolo tables create which creates all tables in the database without using migrations). We can then suggest this instead of running migrations in the index.html file.

I don't think we should remove migrations from the index.html file because users can immediately see how migrations are used without looking at the documentation. That's just my opinion and I've found the easiest way to use Sqlite is from a single file without any configuration. @thetreythomas here is a template to use Sqlite with FastAPI from a single file and you can easily change app.py and requirements.txt to use Litestar. Something like this.

#app.py
import asyncio
import typing as t

import uvicorn
from litestar import Litestar, asgi, delete, get, patch, post
from litestar.contrib.piccolo_orm import PiccoloORMPlugin
from litestar.exceptions import NotFoundException
from litestar.types import Receive, Scope, Send
from piccolo.apps.user.tables import BaseUser
from piccolo.columns import Boolean, Varchar
from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table
from piccolo_admin.endpoints import create_admin
from piccolo_api.session_auth.tables import SessionsBase

from piccolo_conf import DB

class Task(Table, db=DB):
    """
    An example table.
    """

    name = Varchar()
    completed = Boolean(default=False)

# mounting Piccolo Admin
@asgi("/admin/", is_mount=True)
async def admin(scope: "Scope", receive: "Receive", send: "Send") -> None:
    await create_admin(tables=[Task])(scope, receive, send)

@get("/tasks", tags=["Task"])
async def tasks() -> t.List[Task]:
    tasks = await Task.select().order_by(Task.id, ascending=False)
    return tasks

@post("/tasks", tags=["Task"])
async def create_task(data: Task) -> Task:
    task = Task(**data.to_dict())
    await task.save()
    return task

@patch("/tasks/{task_id:int}", tags=["Task"])
async def update_task(task_id: int, data: Task) -> Task:
    task = await Task.objects().get(Task.id == task_id)
    if not task:
        raise NotFoundException("Task does not exist")
    for key, value in data.to_dict().items():
        task.id = task_id
        setattr(task, key, value)

    await task.save()
    return task

@delete("/tasks/{task_id:int}", tags=["Task"])
async def delete_task(task_id: int) -> None:
    task = await Task.objects().get(Task.id == task_id)
    if not task:
        raise NotFoundException("Task does not exist")
    await task.remove()

async def main():
    # Tables creating
    await BaseUser.create_table(if_not_exists=True)
    await SessionsBase.create_table(if_not_exists=True)
    await Task.create_table(if_not_exists=True)

    # Creating admin users
    if not await BaseUser.exists().where(BaseUser.email == "admin@test.com"):
        user = BaseUser(
            username="piccolo",
            password="piccolo123",
            email="admin@test.com",
            admin=True,
            active=True,
            superuser=True,
        )
        await user.save()

app = Litestar(
    route_handlers=[
        admin,
        tasks,
        create_task,
        update_task,
        delete_task,
    ],
    plugins=[PiccoloORMPlugin()], # or you can use create_pydantic_model directly
)

if __name__ == "__main__":
    asyncio.run(main())

    uvicorn.run(app, host="127.0.0.1", port=8000)

[thetreythomas]

@sinisaos Thanks for this!

[sinisaos]

@thetreythomas No problem. I just updated the template so you can also use Litestar to quickly explore Piccolo with Sqlite. Feel free to use it.