Feedback needed

[dantownsend]

Piccolo is now used in production on several projects. The API is pretty stable. I'd appreciate any beta testers, who will install the library, provide feedback on the docs, and highlight any issues they have.

[gazorby]

Hi!

First, thanks for this project!

I started using piccolo on a new project and I really like the SQL like syntax, auto migrations, modularization and the overall simplicity without doing too much under the hood.

After playing with it some hours, here are some issues I encountered :

• In the docs, Authentication>Registering the app, it says to add piccolo.apps.user.piccolo_conf to the AppRegistry, but shouldn't it be bepiccolo.apps.user.piccolo_app instead? I got python complaining about not finding the module and I solved it by changed this path.

• I ran into some troubles when trying to add a column to a table with existing rows. The auto migration didn't forwards because my field was non nullable (but with a default) and something was needed to populate existing rows. Did I miss something? Or it's just that it's not possible yet?

• Is there a way to access the Table instance after calling save()? The save method returns an Insert object but I didn't see how to get inserted values with it.
  I ask this because I have a foreign key in a table and I have to create and save the referenced table's instance first, then query it just after and finally instanciate the first instance using the id from the queried result as the foreign key. If the save() method could have returned inserted values it could have saved one query in this particular situation (because there is only one foreign key in the table).
  Maybe I just didn't understand well how to do it, so I'd be glad If you could enlighten me.

Long life to piccolo ;)

[dantownsend]

Hi Matthieu! Thanks for the feedback, it's much appreciated.

With regards to your first point - good catch, that is a typo, which I will fix shortly.

There is currently a limitation with auto migrations when adding a new column to an existing table, it has to be set to null=True. I'll add this to the docs, and will create an issue to get this rectified.

With the save method, it can be a bit confusing - you still have to run it. Effectively the save method is just a proxy to an insert or update query. To get the table instance you do something like this:

person = Person(name="Bob")
await person.save().run()
print(person.id)
>>> 1

If you want to fetch the instance from the database:

person = await Person.objects().where(Person.name == "Bob").first().run()

# We can then update it:
person.name = "Fred"
await person.save().run()
print(person.name)
>>> Fred

With a schema like this:

class Company(Table):
    name = Varchar()

class Person(Table):
    name = Varchar()
    company = ForeignKey(Company)

You can handle foreign keys as follows:

company = Company(name="Widget Co")
await company.save().run()

person = Person(name="Bob", company=company.id)
await person.save().run()
print(person.company)
>>> 1

You can also get the referenced table instance as follows:

person = await Person.objects().where(Person.name == "Bob").first()
manager = await person.get_related('manager').run()
print(manager.name)
>>> Widget Co

Hopefully that helps - any other questions, feel free to ask.

[gazorby]

Thanks for your explanation about the save method, I understand better how to use it.

Some other questions that comes up to me :

• Regarding the schema : is there any side effects when extend an existing table by inheritance? For example I extended the BaseUser table to add some fields, but I wonder if it's not better to use a foreign key or if it's just depends of the use case.

• It is possible to leverage postgre's full text search in piccolo? In Django ORM we can define search fields to ease the use of full text search engine, is there anything approaching it in piccolo?

[dantownsend]

I'd recommend using foreign keys to other tables, rather than inheritance at this time. Inheritance should still work, but there are some tricky edge cases.

Take this example - Piccolo auto migrations don't currently know that NameMixin is just an abstract table which shouldn't be created in the database:

class NameMixin(Table):
    name = Varchar()

class Person(NameMixin):
    pass

The workaround for now is to use tags.

Full text search isn't currently supported, but you do have quite a bit of control over text filtering using like and ilike.

await Person.objects().where(Person.name.like("%Jones%")).run()

For any query features not currently supported, you can drop down to raw SQL:

await Person.raw('my custom query').run()

[sinisaos]

Hi Daniel!
I’m not a professional, but I love trying out new libraries and I really like your project. There were no problems on localhost and everything works really well (migration, admin, etc.) but when try deploy site on Heroku (free account), I had two problems.
The first problem was heroku postgres (hobby / dev) SSL check failed for asyncpg, but I can fix this as sugested on MagicStack/asyncpg#238 (I didn't have such a problem with encode databases or tortoise orm). After that, i can successfully connect to db, start migrations and create an admin user.
Second problem is that I can't log in as admin (403 Forbidden) in piccolo-admin even though sessions and piccolo_user tables are in the database and admin user is set to True.
Is this axios and vue problem or? Any sugestions.

You can view the simple website at https://github.com/sinisaos/starlette-piccolo-orm.

Thanks for a great project!

[dantownsend]

@sinisaos Thanks for trying the project out - I'm really impressed with the app you've built.

I haven't tried it with Heroku yet. When I use SSL with a version of Postgres I've installed myself it seems OK, so I need to investigate further.

With the admin, did you create the user using piccolo user create?

Have you posted your project on the Encode forums? I think other people would be interested to see a complete Starlette app like this. I'll also put a link to it in the Piccolo docs.

[sinisaos]

First of all thank you for liking app, I really appreciate it.

Yes. I used piccolo user create to create the administrator and everything is fine in the database. The user was created successfully, and the admin column was set to True. Checked it in the postgres console.

Feel free to use anything from the github repo if you find something interesting.

Thanks for putting the link in the piccolo docs (it's my honor), maybe it be useful to someone.

[dantownsend]

@sinisaos I ran it locally, and it worked great for me. I just updated the requirements file slightly - have opened a pull request. The admin is a bit vague if login fails. I'll try and make it more helpful.

[sinisaos]

I see that you have cleaned and updated the requirements.txt (I'm just pip freeze my whole virtualenv), but for example I need bcrypt for auth non admin users. Maybe we did not understand each other but like I previously wrote admin works perfectly locally but does't work on Heroku.

[dantownsend]

@sinisaos Ah, I see! My bad. Feel free to ignore that pull request. Time for me to get a Heroku account!

[sinisaos]

@dantownsend No problem and thanks for your time to investigating. Heroku is really good for free account and you can deploy 5 apps.

[sinisaos]

@dantownsend When I have time I will try to deploy piccolo-admin example to Heroku. There may be a conflict between piccolo authentication and my user authentication although it doesn't make sense because picollo-admin is standalone asgi app mount to starlette app.

[dantownsend]

@sinisaos Thanks. It might be that Heroku is stripping or malforming the headers, and CSRF is failing as a result. This can happen when a request gets redirected. I'll have a look.