Support for "default" parameter in sqlalchemy.Column about databases HOT 13 OPEN

This would be really nice to have. Ignoring the default value in the table definition currently makes this package not work as an async drop-in replacement for equivalent SQLAlchemy calls. For example:

url = "sqlite:///temp.db"
engine = sa.create_engine(url)
metadata = sa.MetaData()
table = sa.Table(
    "table", 
    metadata,
    sa.Column("Id", sa.Integer(), primary_key=True),
    sa.Column("Thing", sa.Integer(), default=0, nullable=False),
)
metadata.create_all(bind=engine)
database = Database(url)
await database.connect()
await database.execute(table.insert(), values={"Id": 1})

results in IntegrityError: NOT NULL constraint failed: table.Thing whereas the usual SQLAlchemy way

engine.execute(table.insert().values({"Id": 2}))

works as expected.

from databases.

I have to agree, this feels to me like it's within the scope of the project and currently rather unexpected behavior. If something supports and recommends SQLAlchemy table definitions, it's pretty confusing if it supports most options but a few are just left out.

Workarounds above are fine and of course there are many ways we can abstract this out ourselves pretty easily/quickly, so it's not the end of the world if you truly feel supporting this would be outside the scope of this project.

But in that case, it would be great if you could specify in the documentation which features are not supported. Simple as the solution is, this is only useful once the user has become aware of the problem. Only discovering limitations when a row insert yields an unexpected result isn't terribly friendly, and makes me wonder if anything else is quietly unsupported that I'll only become aware of down the line if/when I try to use it.

from databases.

Fair enough - I think on balance that it'd be reasonable to have this supported. Pull requests welcome.

from databases.

You do have server_default which is a bit more restrictive but still can be used as a potential workaround.

from databases.

For me personally, I'm not interested in using an ORM so it would be nice if application-level defaults were supported. However, I completely understand if you think that's something out-of-scope for databases.

from databases.

@euri10 Search in SQLAlchemy, not this project - it’s a table definitions thing.

from databases.

@dgilland you might consider server_default which will apply the default at the DB level, like now() func.
Another thing to consider at the app level is to delegate this to some validation schema/class/model.
As example, if data for insertion comes from the payload you might want to validate it before actually committing it to the db. One of many ways to do it might be a marshmallow schema with some @post_load action.
I am quite sure typesystem can do similar stuff as well.

from databases.

In my latest relase of asyncom I support this case for simple cases (values and callables.) there are also other defaults supported.

https://github.com/vinissimus/asyncom/blob/master/asyncom/om.py#L167

Here the tests:
https://github.com/vinissimus/asyncom/blob/master/asyncom/tests/test_default_values.py

from databases.

Good question.
Not necessarily, no. We might consider it out of scope. It’s a bit of a blurry line, since everything else in the column data is all about the actual database schema, but default is an application-level indicator. We might choose not to support it here, but only do so at other layers of abstraction eg. The ORM package does have default support.

Not sure.

from databases.

@gvbgduh Thanks for the suggestions! 👍

from databases.

@gvbgduh what's the server_default you're referring to, I greped the source and found nothing?

from databases.

You can generate the defaults yourself like this:

def get_defaults(db_table):
    defaults = {}
    for column in db_table.columns:
        if column.default is not None:
            value = column.default.arg
            if callable(value):
                value = value()
            defaults[column.name] = value
    return defaults

from databases.

Using server_default alone cannot completely solve this problem. You can try using the following approach.

table = sa.Table(
    "table", 
    metadata,
    sa.Column("Id", sa.Integer(), primary_key=True),
    sa.Column("Thing", sa.Integer(), default=sa.cast(0, sa.Integer), nullable=False),
)

However, if the default value is set as a function body to generate the default value in real-time when adding data,like thisdefault=datetime.now, this method will be ineffective. Does the author have a better solution?

from databases.