Is `create_admin` supposed to create the SQLite and database tables?

[badlydrawnrob]

I'm currently rewriting the piccolo agsi new setup for FastAPI. Do you still need the create_tables() or create_db_tables() function to run, as well as the create_admin function?

I've changed a few things:

app = FastAPI(
    routes=[
        Route("/", HomeEndpoint),
        Mount(
            "/admin/",
            create_admin(
                tables=APP_CONFIG.table_classes,
                # Required when running under HTTPS:
                # allowed_hosts=['my_site.com']
            ),
        ),
        Mount("/static/", StaticFiles(directory="static")),
    ],
    lifespan=lifespan,
)

Becomes (using the FastAPI packages rather than Starlette)

app = FastAPI()

admin = create_admin(tables=APP_CONFIG.table_classes) #! (3)
app.mount("/admin", admin) # (2)

app.mount("/static", StaticFiles(directory="static"), name="static")

And piccolo_app.py

from piccolo.conf.apps import AppConfig
from tasks.tables import Task


APP_CONFIG = AppConfig(
    app_name="tasks",
    table_classes=[Task],
    migrations_folder_path=None,
    migration_dependencies=[],
    commands=[]
)

I'm trying to remove anything I feel is unnecessary or using Python "magic" so perhaps I've inadvertently broken something. Running the below code as well as create_admin seems to work, but the piccolo agsi new FastAPI example seems to have no create tables function that I can see.

@asynccontextmanager
async def lifespan(app: FastAPI):
    await create_db_tables(Task, if_not_exists=True)
    yield

[sinisaos]

Is create_admin supposed to create the SQLite and database tables?

No. Since the admin is an standalone ASGI app create_admin create ASGI app and expose Piccolo tables to admin.

Becomes (using the FastAPI packages rather than Starlette)

This is the same because FastAPI is basically a wrapper around Starlette and Pydantic. If you look at the source code of FastAPI, you can see that FastAPI imports all the relevant code from Starlette. You can use FastAPI or Starlette style. Whichever you like because they are the same.

from piccolo.conf.apps import AppConfig
from tasks.tables import Task

APP_CONFIG = AppConfig(
app_name="tasks",
table_classes=[Task],
migrations_folder_path=None,
migration_dependencies=[],
commands=[]
)

migrations_folder_path=None is currently not supported in Piccolo (although it runs on Linux). Here is PR for that.

@asynccontextmanager
async def lifespan(app: FastAPI):
await create_db_tables(Task, if_not_exists=True)
yield

You don't need this if you are running migrations (but SQLite has limited support for database migrations because SQLite has limited support for ALTER TABLE). You just need to change the db engine (to SqliteEngine) in piccolo_conf, remove connection_pool and lifespan from app.py and you are good to go. A little tip. Use this if you want a quick start with SQLite. You can later extend this to use Piccolo apps etc.

[badlydrawnrob]

Sorry, I spotted that you have to use the migrations in the piccolo asgi new setup for the Task table and admin tables. I was avoiding migrations and didn't spot it.

There's a few things about the onboarding process that could be clearer (to me at least), so I'm noting them down and will have a go at writing them out myself. I've changed the code quite a bit to my understanding of FastAPI and coding style.

This is the same because FastAPI is basically a wrapper around Starlette and Pydantic.

Yes I understand this, but it's a bit odd to see all that starlette stuff rather than FastAPI functions.

migrations_folder_path=None

Good that it's on the radar. I have some general thoughts on setup which I'll add to the onboarding I'm figuring out. On my local machine I'm not getting any errors by using None.

You don't need this if you are running migrations (but SQLite has limited support for database migrations because SQLite has limited support for ALTER TABLE).

Yeah I understand there's some differences to Postgres. I don't know which is better: to autocreate with create_table() or use migrations. I've always found migrations quite difficult to get my head around, so the former seems more suited to onboarding.

I'd be interested to know how easy/reliable auto migrations are and exactly what isn't applicable to SQLite. To be honest though I quite like using a GUI, raw SQL if it's simple enough, or a tool like sqlite-utils. Migrations isn't something I've had much experience with.

A little tip. Use this if you want a quick start with ...

Yes I've looked over that code and have integrated a bit of it into my version of agsi new, except the BaseUser part which I still need to understand. There's bits that are quite different to other ORMs like the lack of .connect() and .close() functions — it seems like SQLite query functions handle that automatically:

• Is the connection always kept open? Or after every async route .select() query is the connection closed?
• Could you explain it like I'm five what the benefit of transactions over your default setup are?

I don't know if you guys are on Slack or not, but it'd be helpful to have a line of communication other than Github issues if you've got a little time to spare!

[badlydrawnrob]

And it seems all create_admin(tables=APP_CONFIG.table_classes) does is register any tables you have that you'd like to view in the admin GUI? It doesn't run any SQL.

[sinisaos]

Yeah I understand there's some differences to Postgres. I don't know which is better: to autocreate with create_table() or use migrations. I've always found migrations quite difficult to get my head around, so the former seems more suited to onboarding.

I think Postgres (with considerable differences from SQLite) is the best open source RDBMS that has excellent features and stability. You only focus on creating tables, but every time you make changes to columns or tables (rename,altered, droped etc.) you have to do migrations to keep the database in sync. Sqlite has limited ALTER TABLE and that's why Piccolo (or any other ORM) has limited support for migrations in SQLite.

I'd be interested to know how easy/reliable auto migrations are and exactly what isn't applicable to SQLite. To be honest though I quite like using a GUI, raw SQL if it's simple enough, or a tool like sqlite-utils. Migrations isn't something I've had much experience with.

I think Piccolo migrations are reliable as they can be. Also, Piccolo allows you to easily write migrations in raw SQL. This option is great for some features that Piccolo doesn't currently support such as composite constraints, composite indexes, Postgres full-text search, etc.

There's bits that are quite different to other ORMs like the lack of .connect() and .close() functions — it seems like SQLite query functions handle that automatically:

Piccolo handles db drivers connections (opening and closing) and you don't have to think about it. For Postgres, Piccolo also handles connection pools over a lifespan (or events depending on the web framework).

• Could you explain it like I'm five what the benefit of transactions over your default setup are?

I have to say I don't understand this. Transactions are a mechanism for executing multiple queries at once, but only if all queries are successful, the transaction is commited. If only one query fails, the transaction is rolled back, ensuring that the database remains in a consistent state. In the example repository, I just created a single-file application for quick prototyping in SQLite without any configuration, migrations (using create_table), etc. It should only be used for exploring and testing ideas.

I don't know if you guys are on Slack or not, but it'd be helpful to have a line of communication other than Github issues if you've got a little time to spare!

I'm not. There is Discord but I'm not on that either.

[badlydrawnrob]

@sinisaos So it's more feature rich with Postgres than it is SQLite. Migrations at least. As mentioned before one of the reasons for choosing SQLite over Postgres (for now at least) is it's a lot easier to setup and move around (it also seems to be gaining some traction as an alternative for production sites and vectors for Ai). It has some downsides of course, like not being as strict. I've spotted that Auto migrations aren’t supported in SQLite so that's all I need to know for now, I guess.

Piccolo handles db drivers connections (opening and closing) and you don't have to think about it.

That's great. I think you could be shouting about that a bit more!

I have to say I don't understand this.

Sorry, it's a Reddit thing — it means "explain it in a way a five year old could understand" and is very useful sometimes. So TL;DR using the transactions setup, if any given route using Depends(transaction) one of the queries fails, they all fail and it reverses the queries? And supposedly using queries without transactions means it could mess up your database if one of the queries fails?

I'll read up a little more. I thought it was more to do with multiple connections to the server at the same time.

I'm not. There is Discord but I'm not on that either.

I'm not on Discord but if I need it I know it's there. So I guess I just continue to communicate with you via issues? Or let me know another way.

[sinisaos]

is it's a lot easier to setup and move around (it also seems to be gaining some traction as an alternative for production sites and vectors for Ai)

@badlydrawnrob But for Postgres there is Neon or Supabase (which I use a lot lately with a React frontend for fullstack apps and I consider Supabase great, because you get a Postgres database with a lot of included batteries like auth, storage etc. With Supabase I don't have to use any ORM or backend framework (but you can if you want) and you can do everything with raw SQL using functions, triggers, RLS and all other great Postgres stuff)

if any given route using Depends(transaction) one of the queries fails, they all fail and it reverses the queries? And supposedly using queries without transactions means it could mess up your database if one of the queries fails?

If I were you, I wouldn't bother with transactions, especially if you don't use multiple queries at once. In Piccolo you don't have to pass sessions as dependencies like in SQLAlchemy (which you consider a bloated mess and I disagree with that. SQLAlchemy is much more complex than Piccolo because it follows the Data Mapper pattern and you have to handle database sessions, but it's also widely used and has lots of resources for help if you get stuck). Another little advice. Build in small steps, read the documentation, use an examples (in my Github profile you have some starter templates to get started using Piccolo with various router frameworks), use the playground to see and try out different types of queries.
I have to admit I'm surprised how many problems you're having with Piccolo, and Piccolo is one of the few libraries that's really easy to get started with (I think it's a great feature and I really like that) because it just works. Cheers.