Support for SQLite pragma and strict table settings

[badlydrawnrob]

Apologies if this is somewhere in the documentation, but I can't find it. I'm looking for a replacement for Peewee (which has pragma and strict_tables support) that's async with FastAPI. Do you support these settings?

Also, a little confused about this SQLite async page. Is this only relevant if you're running a select then an insert/update/delete (in that order?) — for example, if you were to run an insert first, then a select, within the same function, you can just follow the regular docs?

[dantownsend]

We don't have a way of creating strict tables currently with Piccolo.

Strict tables would be problematic for some of our column types (like Array) - we take advantage of the fact that you can specify arbitrary column types in SQLite. For example, by defining a column as an Array we know we have to parse that back into a list.

If working from an existing database with strict tables it should work OK, as long as you keep to the narrower set of column types (Integer, Real, Text, Blob).

With PRAGMAs, we do currently set some:

piccolo/piccolo/engine/sqlite.py

Line 696 in 9517f49

await connection.execute("PRAGMA foreign_keys = 1")

We could extend this pretty easily to allow the user to specify their own PRAGMA statements. Presumably you would want to use this to enable strict mode.

Also, a little confused about this SQLite async page. Is this only relevant if you're running a select then an insert/update/delete (in that order?) — for example, if you were to run an insert first, then a select, within the same function, you can just follow the regular docs?

Yes, exactly. It's just a problem with SQLite if you have multiple DEFERRED transactions, and they do a SELECT followed by an INSERT / UPDATE / DELETE. You may never run into this problem - it is a weird edge case with SQLite.

[badlydrawnrob]

Hmm. I guess strict_tables could be added manually ... but understand your point about flexibility.

We take advantage of the fact that you can specify arbitrary column types in SQLite (like Array)

• Are these Arrays stored as a json blob in SQLite? Or a plain string?
• Is that in your documentation someplace, like in this blog post?
  • I can only find this API schema page.

I'm guessing you could switch it out for JSONB for strict tables.

Types

My main concern is catching types before they get added to the SQLite database, as it's far from type safe. I had my model setup in Peewee ORM but the insert didn't fail (A String was supposed to be an Integer) — it was my mistake in the code, but I didn't catch it. It's why I like Elm lang, as I don't have to worry too much about screwing up!

My follow-up questions would be:

1. Does Piccalo ALWAYS catch wrong types (like FastAPI does with Pydantic) or do I have to be extra careful with my code?
2. Is migration a problem? Or will Postgres types be similar to SQLite ones?

PRAGMA

That'd be super helpful to add support. So far I've been using {"journal_mode": "wal", "cache_size": -65536, "foreign_keys": 1} with Peewee. I'm sure down the road there'll be others needed. I'm using SQLite for prototyping and eventually migrate to Postgres.

Async

Yes, exactly. It's just a problem with SQLite if ...

So it seems like better to just use async and wait until you discover the database locked issue?

[sinisaos]

My main concern is catching types before they get added to the SQLite database, as it's far from type safe. I had my model setup in Peewee ORM but the insert didn't fail (A String was supposed to be an Integer) — it was my mistake in the code, but I didn't catch it. It's why I like Elm lang, as I don't have to worry too much about screwing up!

My follow-up questions would be:

1. Does Piccalo ALWAYS catch wrong types (like FastAPI does with Pydantic) or do I have to be extra careful with my code?
2. Is migration a problem? Or will Postgres types be similar to SQLite ones?

Piccolo will not catch wrong types before inserting (we can insert Integer type into Varchar column), but we can always use Pydantic to do validation. It is easy to use Pydantic with Piccolo. We have two options, write Pydantic models ourselves or use Piccolo utility function create_pydantic_model. Here is a small example script

Example

import asyncio

from piccolo.columns import Varchar, Array
from piccolo.table import Table, create_db_tables
from piccolo.utils.pydantic import create_pydantic_model
from piccolo.engine.sqlite import SQLiteEngine
from pydantic import BaseModel


DB = SQLiteEngine()


# Table example
class Manager(Table, db=DB):
    name = Varchar()
    arr_column = Array(base_column=Varchar())


# Models using Piccolo
ManagerModelIn = create_pydantic_model(table=Manager)

# Models using Pydantic
# class ManagerModelIn(BaseModel):
#     name: str
#     arr_column: list[str]


async def main():
    # Table creating
    await create_db_tables(Manager, if_not_exists=True)
    # data = ManagerModelIn(name=1, arr_column=[1, 2])
    # will raise ValidationError because we
    # are using the wrong types (integer, not string)
    data = ManagerModelIn(name="John", arr_column=["a", "b"]) # correct
    manager = Manager(**data.model_dump())
    await manager.save()
    # select data
    print(await Manager.select())


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

[badlydrawnrob]

@dantownsend @sinisaos Has there been any movement on PRAGMAs?

I prefer to keep my API layer models and Data layer models separate (this seems better practice to me?) as in this example so it seems your create_pydantic_model example above (ManagerModelIn) is doing that as a sort of "middle man" before using the normal Picallo Manager class.

From a quick look at your docs it seems you're only using Pydantic on the API layer and not doing secondary checks before putting it into the database. In general (if you don't mess up your code) .. is this safe to do? I know enough about my coding skills (Elm has nice compiler errors, Python is rubbish) that I'm going to mess up and with SQLite that seems a risk 😄 — having that PRAGMA would act as an easier safeguard.

I'm assuming by "write Pydantic models ourselves" you're meaning that ManagerModelIn would be written from scratch with BaseModel and **data.model_dump() in the same way?