Explore the intersection between sqlite-utils and dataclasses

[simonw]

Aside: this makes me think it might be cool if sqlite-utils had a way of working with dataclasses rather than just dicts, and knew how to create a SQLite table to match a dataclass and maybe how to code-generate dataclasses for a specific table schema (dynamically or even using code-generation that can be written to disk, for better editor integrations).

Originally posted by @simonw in https://github.com/simonw/llm/issues/65#issuecomment-1616742529

[simonw]

Dataclasses were added in Python 3.7 and sqlite-utils was originally written for Python 3.6 - but both 3.6 and 3.7 are EOL now.

The thing that makes Dataclasses particularly interesting is the potential to use type annotations with them to help specify the types of the related SQLite columns.

Example for https://datasette.io/content/users

CREATE TABLE [users] (
   [login] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [avatar_url] TEXT,
   [gravatar_id] TEXT,
   [html_url] TEXT,
   [type] TEXT,
   [site_admin] INTEGER,
   [name] TEXT
);

And the dataclass:

from dataclasses import dataclass

@dataclass
class User:
    id: int
    login: str
    node_id: str
    avatar_url: str
    gravatar_id: str
    html_url: str
    type: str
    site_admin: int
    name: str

[tobych]

I'd suggest not bothering with this, because so many choose Pydantic over Python's built-in dataclasses.

And there are a few packages that help with that already:

• pydantic-sqlite
• pydbantic can work with SQLite

So sure, it's not dataclasses, but big picture this would duplicated the efforts of others.