4 ms·

I didnt understand the purposes of document stores until the past couple of years and they are fabulous for building POCs. Enhanced JSON support will help a lot

I didnt understand the purposes of document stores until the past couple of years and they are fabulous for building POCs. Enhanced JSON support will help a lot for making sqlite a suitable document store. I get full type support by serializing and deserializing protobuf messages from a db column and not making this column JSONB means i can filter this column too, instead of having to flatten the searchable data to other columns.

p1necone 3y ago
Yeah as long as you're reading and writing to the database with the same language, and that language has good type safety the benefits of your database schema effectively being defined by the same types as the rest of your code is pretty nice for a lot of use cases. You just have to be vigilant about correctly migrating existing data to the current shape if you ever make breaking changes to types.
- BozeWolf 3y ago
  This. Would be nice if there was a framework (in go, or python pydantic) which would help me migrate data made with old structs to new structs. And also deal with the transaction. For now i use sqlite to deal with transactions and only make backward compatible updates to structs. Brittle, but it is a toy app anyways. (Normally use django to deal with models and migrations, but wanted to do something different)
  - buzziebee 3y ago
    Yeah migrations are the biggest issue for me. I really don't like not knowing what the actual shape of the document will be. Missing transactions, and not great relationship performance makes modelling some systems more hassle than it's worth. I gave it a good go to use mongo and firestore for a few projects, but after a year or two of experimenting I'll be sticking to SQL based DBs unless there are super clear and obvious benefits to using a document based model.
    - piaste 3y ago
      There's a gradual approach there, where you start out with a JSONB column, and then as each piece of the data structure stabilizes* you move it out of json fields and into its own columns/tables. * meaning, when there's enough code that depends on it that changing it would require some planning
      - jadbox 3y ago
        This is the way I build all my apps now. Eventually the jsonb field stores nothing as it all gets moved to defined fields.
      - rav 3y ago
        Alternatively, keep it in the JSON/JSONB column until you need to search/filter/query on it, in which case you pull it out into a column.
        piaste 3y ago
        Even that may not be immediately necessary. I don't think SQLite has it yet, but Postgres can build partial indexes on JSONB fields. Though most of the time, in that situation, I would pull it out.
  - halfcat 3y ago
    Most commonly I see people use Alembic to migrate SQLAlchemy and Pydantic models. But I tend to just use Django. Every time I try piecing together the parts (ex FastAPI, Pydantic, Alembic, etc) I reach a point where I realize I’m recreating a half baked Django, and kick myself for not starting with Django in the first place.

p1necone 3y ago

Yeah as long as you're reading and writing to the database with the same language, and that language has good type safety the benefits of your database schema effectively being defined by the same types as the rest of your code is pretty nice for a lot of use cases. You just have to be vigilant about correctly migrating existing data to the current shape if you ever make breaking changes to types.

BozeWolf 3y ago
This. Would be nice if there was a framework (in go, or python pydantic) which would help me migrate data made with old structs to new structs. And also deal with the transaction. For now i use sqlite to deal with transactions and only make backward compatible updates to structs. Brittle, but it is a toy app anyways. (Normally use django to deal with models and migrations, but wanted to do something different)
- buzziebee 3y ago
  Yeah migrations are the biggest issue for me. I really don't like not knowing what the actual shape of the document will be. Missing transactions, and not great relationship performance makes modelling some systems more hassle than it's worth. I gave it a good go to use mongo and firestore for a few projects, but after a year or two of experimenting I'll be sticking to SQL based DBs unless there are super clear and obvious benefits to using a document based model.
  - piaste 3y ago
    There's a gradual approach there, where you start out with a JSONB column, and then as each piece of the data structure stabilizes* you move it out of json fields and into its own columns/tables. * meaning, when there's enough code that depends on it that changing it would require some planning
    - jadbox 3y ago
      This is the way I build all my apps now. Eventually the jsonb field stores nothing as it all gets moved to defined fields.
    - rav 3y ago
      Alternatively, keep it in the JSON/JSONB column until you need to search/filter/query on it, in which case you pull it out into a column.
      - piaste 3y ago
        Even that may not be immediately necessary. I don't think SQLite has it yet, but Postgres can build partial indexes on JSONB fields. Though most of the time, in that situation, I would pull it out.
- halfcat 3y ago
  Most commonly I see people use Alembic to migrate SQLAlchemy and Pydantic models. But I tend to just use Django. Every time I try piecing together the parts (ex FastAPI, Pydantic, Alembic, etc) I reach a point where I realize I’m recreating a half baked Django, and kick myself for not starting with Django in the first place.