Roadmap

[dantownsend]

• JSON fields
• More aggregation functions - e.g. SUM
• Aliases in SELECT queries (e.g. SELECT foo AS bar FROM my_table)
• Improved support for data migrations
• Fixtures
• Schema visualisation tool
• piccolo sql_shell new command - execute SQL directly in a shell, via the configured engine
• piccolo shell new command - like the playground, but for your own tables
• Shorter aliases for commands in CLI, or tab completion
• Improved documentation for how to test Piccolo apps
• Nested transactions
• Move Pydantic integration from Piccolo API into Piccolo
• Allow the ForeignKey references argument to accept a string such as 'my_app.SomeTable' when circular imports are an issue.
• Support Postgres Enum types.
• Row level constraints.
• Making foreign key constraints optional on ForeignKey columns.
• Allow UUIDs as primary keys?
• Subqueries

[dantownsend]

Please feel free to suggest features for the roadmap.

[99hats]

Nice project!
How about row level security?
Also, +1 for Allow the ForeignKey references argument to accept a string such as 'my_app.SomeTable' when circular imports are an issue.

[dantownsend]

@99hats Thanks for the feedback. Accepting ForeignKey references as strings is in progress as we speak, and will be in the next release. How do you envisage row level security working? I could add a way to define the policies, and run them when creating tables / running migrations.

[99hats]

@dantownsend That sounds great. It could also be added to the web admin. Here's how supabase.io (a firebase clone using postgres) implements it on web admin: https://supabase.io/docs/guides/auth#row-level-security

[dantownsend]

@99hats Thanks for the link - I'll take a look at how they're doing it.

There's a fix for the circular dependency issue in Piccolo 0.14.10 - ForeignKey can now accept strings.

class Band(Table):
    name = Varchar()
    manager = ForeignKey('Manager')

class Manager(Table):
    name = Varchar()

https://piccolo-orm.readthedocs.io/en/latest/piccolo/schema/column_types.html#foreignkey

[99hats]

@dantownsend Awesome, I swear all of my projects have circular dependencies. Thank you!

[99hats]

@dantownsend Are you considering modeling one-to-many and many-to-many relationships?

[dantownsend]

@99hats Good question - I haven't quite decided yet. I created a discussion for this #44 I'm interested to hear your opinion on it!

[dbanty]

Hello! 👋 I just finished reading through the Piccolo docs and am very excited to give it a try! I'm coming from SQLAlchemy which I generally use with FastAPI. Here are some questions / suggestions based on my reading:

1. Some documentation on how to add Piccolo to an existing application could be very useful. Right now I've run piccolo asgi new and am reading / moving around the generated code as I figure out how to re-implement my existing SQLAlchemy structure.
2. An equivalent to the "Django Comparison" for SQLAlchemy could be nice
3. How would one go about implementing custom base classes or mixins? We use a pattern of "soft deleting" where all of our models get a _deleted attribute which is auto-filtered on queries. In SQLAlchemy be do this by creating our own BaseModel with __abstract__ = True so that it's not an actual table but can be inherited from to create tables which include all the attributes.
4. Do you have any plans for / thoughts on polymorphism?
5. I don't really understand the Project / App structure and what it's used for. Is this mostly a data science thing? Is there any reason a web app would want multiple Piccolo Apps? Maybe some clarification about the intentions would help clarify "what is a project" and "what is an app"?

Those are all my initial thoughts for now, sorry if this is the wrong place to put them 😅. Thanks for making such a cool project!

[coder3112]

Hey @dantownsend , what do you mean by fixtures in your original comment?

[dantownsend]

@coder3112 Hi! In terms of fixtures, I was thinking of dumping existing database rows to JSON, and then being able to reload them again. Similar to dumpdata and loaddata in Django, but called piccolo fixture create and piccolo fixture load.

The easiest way to perform this serialisation / deserialisation is using something like Pydantic. In piccolo_api there's a function called create_pydantic_model which can create a serialiser automatically from a Piccolo table. The plan was to move this into the main piccolo repo, but at the time Pydantic didn't support Python 3.9, so was put on hold.

It's something I'd like to explore again soon.

[heliumbrain]

Hi! I've spent enough time with Piccolo the last week or so to get a decent grasp of it. Would like to give my feedback, even though all of it can't directly be translated to roadmap items.

First of all, I think it would make sense to split the roadmap in to 3 main areas, depending on what you envision for Piccolo in the next year or so.

Providing a modern (ASGI) ORM

This would include the features that users might be expecting from a ORM but is not in Piccolo right now for one reason or another, such as:

1. Support for M2M fields
2. Improvements to migration
3. Improved documentation with onboarding for users with different backgrounds (coming from Django, SQLAlchemy or something comepletely different)

Supporting the wider ASGI ecosystem

I think this is a potentially huge area. Looking at the ASGI ORM landscape there's a big need for a complete solution (a lá Django + Django ORM). There's great async ORMs out there, and the tooling around them (auth, admin, etc.) definitely exists - but there's not a single complete package available with as little pain points as Piccolo. I also think that with the migration to SQLAlchemy 1.4 and down the line 2.0, a lot of ORMs will struggle. Not being reliant on SQLAlchemy can be a great thing in this circumstance :) This area would include stuff as:

1. Support for dependency injection in FastAPI
2. Clearer (or less) separation between Piccolo and Piccolo-API
3. Provide components (such as login, admin) that's easy to get started with - but also provide a clear way to tweak it and "plug it in" to other frameworks (Starlette, FastAPI, Blacksheep, ...)
   1. The current admin and session-auth is probably the best I've seen for FastAPI. But there's other solutions out there that's much easier to get up and running with
4. Docs, docs, docs & examples :)
5. Move Pydantic integration from Piccolo API into Piccolo

Making Piccolo the best it can be

This area would include anything else that doesn't fit in to the other two. Stuff that will really make Piccolo stand out (if that's what you want) or just in general be a great ORM

Here I think there's a decision to be made. Do you want Piccolo to be the best all-purpose Python ASGI ORM that CAN tie in to frameworks such as FastAPI - Or do you want Piccolo to be THE BEST FastAPI/Blacksheep/Starlette ORM that can also fill other purposes. I might be wrong, but I think it will be hard to fill both those roles.

This ended up being longer than expected, but just wanted to give you my thought after a few days of using Piccolo. Big kudos @dantownsend - you've done a fantastic job! Happy to help out where I can moving forward 🚀

[dantownsend]

@heliumbrain Some great suggestions there, thanks. I've created a separate discussion thread to explore these ideas more.

[AliSayyah]

@dantownsend Is this roadmap up-to-date?

[dantownsend]

@AliSayyah No, it's a bit dated. I've just crossed off a few which have already been done.

This issue can probably be closed, and replaced with individual issues instead.

When I created this issue we didn't have GitHub discussions, or projects. So a check list was about as good as it got!

We can pick up the conversation here:

#126