Additional sqlalchemy utilities

[mehcode]

I have a number of nifty things I've developed (or found and spruced up) for sqlalchemy that don't have a real place at the moment and I'd like to find them one in open source land. What is the scope of this project? A grab-bag of all utils for sqlalchemy?

Some things for consideration:

• auto created / updated time columns
• uuid column
• timezone column
• data-based fixtures (nearly identical to django's with a couple of environmental changes: https://docs.djangoproject.com/en/dev/howto/initial-data/)
• django style object manager (https://docs.djangoproject.com/en/dev/topics/db/managers/)
• multi-db routing (think flask-alchemy binds plus django for_read / for_write / etc. -- declarative config for master/slave)
• declarative permissions using binary expressions

That's all I can think of.. I can probably dig up some more. The point is that I use a lot of these in a lot of our projects and It'd be nice to put them somewhere on github. If you don't think just adding some more buckets to this project would be a good idea; do you have another? We could always add lots of "extra_requires" (for any additional dependencies).

[kvesteri]

I think the scope of this project is exactly what you described it: a grab-bag of utils for sqlalchemy. As for larger extensions we could create entirely new repositories. I've already done this with:

https://github.com/kvesteri/wtforms-alchemy
https://github.com/kvesteri/sqlalchemy-continuum
https://github.com/kvesteri/sqlalchemy-i18n
https://github.com/kvesteri/sqlalchemy-defaults

As for the features you suggested, here is my two cents:

• auto created / updated time columns - please give desired API for this
• uuid column - good idea, maybe this could be implemented as a uuid typedecorator which would use underlying native database uuid type whenever possible (atleast in the case of pgsql)
• timezone column - very good idea, maybe a timezone typedecorator instead of column?
• data-based fixtures - I already created a package for sqlalchemy fixtures: https://github.com/kvesteri/sqlalchemy-fixtures. This package sucks though. Our company switched to using this: https://github.com/dnerdy/factory_boy (we added sqlalchemy driver for it, I think we haven't yet made a pull request there though)
• django style object manager - good idea once again, maybe we should first start with the desired API for this?
• declarative permissions using binary expressions - once again good idea, I'd love to see the API for this

[kvesteri]

I added you as a contributor for this project.

[mehcode]

I added you as a contributor for this project.

Good stuff; I appreciate it. I'm going to start by opening issues for a few of these.

[kvesteri]

Thanks for creating the issues, great stuff! Out of curiosity which MVC framework are you using?

[mehcode]

As our development focuses primarily on RESTful interfaces, we use https://github.com/armet/python-armet as the view / controller layer.

Armet is a framework-agnostic REST framework. Currently it connects to django, bottle, cyclone, and flask for the http layer and django or sqlalchemy for the model layer (and any combination of the two layers is supported).

We're using it tied to flask for the http layer at the moment with (semi-obviously) sqlalchemy for the model layer.

[mehcode]

@kvesteri Let me know what you think of alchemist: https://github.com/concordusapps/alchemist

It's meant to be the glue between flask and sqlalchemy using many of the same conventions django provides (such as segmented applications). It also provides (using flask-script) a powerful CLI to manage the db, server, etc.

[kvesteri]

I like many of the concepts you have there. Maybe this package would be better split in two separate concerns? How about Flask-Packages and Flask-AlchemyScript (or Flask-Script-SQLAlchemy :) ?.

[kvesteri]

@mehcode What kind of data validation are you using for REST APIs? We've used WTForms along with WTForms-JSON and WTForms-Alchemy for data validation with POST, PUT and PATCH requests. It would be great if we could collaborate on these projects too.

[mehcode]

https://github.com/armet/python-armet has its own internal validation:

from armet import resources
from . import models

class Resource(resources.ModelResource):
    class Meta:
        connectors = {'http': 'flask', 'model': 'sqlalchemy'}
        model = models.User

    def clean_name(self, value):
        assert len(value) > 10, 'Name must be longer than 10 characters'
        return value

With the above definition and the following request...

POST /user
{"name": "bob"}

The response would be ...

400
{ "name": ['Name must be longer than 10 characters']}

The idea is any ValueErrors or AssertionErrors that get triggered anywhere get collected according to the context of the call and formed into this error dictionary. For instance, this allows any asserts in the sqlalchemy model validators to become nice 400-code responses for the client.

Futerhmore, I've been planning to allow linking up a django form to handle validation (in django of course).

class Resource(resources.ModelResource):
    class Meta:
        connectors = {'http': 'flask', 'model': 'django'}
        form = UserForm

It'd be nice to have other form libraries to plugin into armet (like WTForms). I'll definitely put it on the TODO list to create the binding between WTForms and armet. It looks nice. I'll take a look in there to see if there's anything extra I need, etc. when I get to this.

---

Along the same line of thinking, I'd love to get some collaboration going for armet if you're interested. Most of my free time next week I have set aside for documentation, website, and various things for it.