Can we use piccolo just as query builder?

[danechitoaie]

Can we use piccolo just as query builder?
I don't need the playground, admin, or migrations.

And is there a way to use it without piccolo_conf.py and piccolo_app.py as I don't have my app structured like this in project with apps in it.

[dantownsend]

Yeah, you can use it all in a single file if you want.

I use it like that quite a lot for scripts etc.

Here's an example:

import asyncio
from piccolo.engine.sqlite import SQLiteEngine
from piccolo.table import Table, create_db_tables
from piccolo.columns import Varchar

#########
# Define tables

# Define the engine for connecting to the database
DB = SQLiteEngine()

# Bind it explicitly to each Table, rather than using `piccolo_conf.py`
class MyTable(Table, db=DB):
    my_column = Varchar()

#########
# An example app using the query builder

app = FastAPI()

@app.get('/')
async def my_endpoint():
    return await MyTable.select()

#########
# Optionally create the database tables when the script starts

async def main():
    await create_db_tables(MyTable, if_not_exists=True)

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

    import uvicorn
    uvicorn.run(app)

Main takeaways:

• You can explicitly bind the engine to the table, rather than using piccolo_conf.py.
• When your app / script starts run create_db_tables which creates the tables in the database. Or just create the tables manually in the database instead.

[danechitoaie]

Thank you. Followup question to this.

I'm using Litestar.dev and in there there are two hooks (on_startup and on_shutdown).
I was planning to define the engine DB = SQLiteEngine() in the on_startup hook as that's where I'm also loading the settings (DB_URL, etc).
But If I do it like that I can no longer define my tables class MyTable(Table, db=DB): as they expect to have the db param passed in the code not at runtime.

Any way I can handle this?

[dantownsend]

There's no need to define the engine in the startup hook - it can be defined globally.

If using Postgres, you may want to start a connection pool inside the startup hook though:

def some_startup_hook():
    await DB.start_connection_pool()

def some_shutdown_hook():
    await DB.close_connection_pool()

It's optional though - it works fine without a connection pool.

[danechitoaie]

The reason why I wanted to define it in startup hook was that that's where I was loading the settings (and doing validation). If I cannot make use of that it means I'll have to load the settings twice once in the startup hook and then again in the python module where I'm defining the engine.

[dantownsend]

OK, I see.

You can assign the engine programatically to tables in the startup. Set db to some temporary value.

class MyTable(Table, db=...):
    my_column = Varchar()

def some_setup_hook():
    DB = SQLiteEngine()
    MyTable._meta.db = DB