librelist archives

« back to archive

Best Practice for Adding DB Auditing with Flask and Flask-SQLAlchemy

Best Practice for Adding DB Auditing with Flask and Flask-SQLAlchemy

From:
Farhan
Date:
2014-03-27 @ 20:51
Hi,

I need a little bit of guidance as to how I should add database auditing 
for my Flask app using Flask-SQLAlchemy. I need to record, each insert, 
delete, and update operation. In case of the ‘Update’ operation, I need to
record the old value and the new value for each field that was updated? Is
this overkill!?

Right now, each of my models are backed by an audit table, with a 
one-to-many to the audit table. The audit table has an auto increment 
primary key, action (insert, update, delete), old value, and new value 
(only filled in for updates).

Thanks in advance.

Best,
Farhan

Re: [flask] Best Practice for Adding DB Auditing with Flask and Flask-SQLAlchemy

From:
Cory Dolphin
Date:
2014-03-27 @ 23:56
Hey Farhan,

If you need to perform such auditing, why are you mutating the underlying
datastore? It sounds like what you want is actually an immutable data
store, where writes are essentially appends to a commit log. If that is the
case, you may want to check out something like Datomic.
I fear maintaining your own database of changes is difficult to do without
introducing race conditions (though I am happy to be proved wrong by
others!)

Cory


On Thu, Mar 27, 2014 at 4:51 PM, Farhan <inshany@gmail.com> wrote:

> Hi,
>
> I need a little bit of guidance as to how I should add database auditing
> for my Flask app using Flask-SQLAlchemy. I need to record, each insert,
> delete, and update operation. In case of the 'Update' operation, I need to
> record the old value and the new value for each field that was updated? Is
> this overkill!?
>
> Right now, each of my models are backed by an audit table, with a
> one-to-many to the audit table. The audit table has an auto increment
> primary key, action (insert, update, delete), old value, and new value
> (only filled in for updates).
>
> Thanks in advance.
>
> Best,
> Farhan
>

Re: [flask] Best Practice for Adding DB Auditing with Flask and Flask-SQLAlchemy

From:
Farhan
Date:
2014-03-28 @ 00:11
Hi Cory,

Thanks for your response. Agree with you there, this is the first time I 
am implementing this kind of auditing in an app and don’t really know what
to do, aside from the novice attempt I mentioned earlier. Not sure about 
the race condition, since the audit tables will only be written to (insert
only), so I don’t think that would be an issue, although I could be 
mistaken.

Appreciate the reference to Datomic, I will check it out.

Best,
Farhan

On Mar 27, 2014, at 4:56 PM, Cory Dolphin <wcdolphin@gmail.com> wrote:

> Hey Farhan,
> 
> If you need to perform such auditing, why are you mutating the 
underlying datastore? It sounds like what you want is actually an 
immutable data store, where writes are essentially appends to a commit 
log. If that is the case, you may want to check out something like 
Datomic. 
> I fear maintaining your own database of changes is difficult to do 
without introducing race conditions (though I am happy to be proved wrong 
by others!)
> 
> Cory 

Re: [flask] Best Practice for Adding DB Auditing with Flask and Flask-SQLAlchemy

From:
Daniel Fairhead (OMNIvision)
Date:
2014-03-28 @ 10:20
If you do want to do it though, anyway, then it can be done reasonably 
easily by structuring your project something like:

logic/
    all sql alchemy stuff lives here, and all business logic.

views/
    no access to sql alchemy at all!  only access data through functions 
defined in "logic".


So your logic functions might end up looking something like:


def update_product_details(product_id, **kwargs):
     product = Product.query(id=product_id).fetch()
     for key, value in kwargs.items():
        old_value = getattr(product, key)
        if old_value != value:
            try:
                setattr(product, key, value)
                log_changing('product', product_id, key,
                             old_value, value)
            except:
                log_change_error('product', product_id, key, value)


and then log_changing writes the details to whatever file or database 
you want.  Obviously, you need to add in any data validation and stuff 
as well, but it should work...

Dan


On 28/03/2014 00:11, Farhan wrote:
> Hi Cory,
>
> Thanks for your response. Agree with you there, this is the first time I
> am implementing this kind of auditing in an app and don’t really know
> what to do, aside from the novice attempt I mentioned earlier. Not sure
> about the race condition, since the audit tables will only be written to
> (insert only), so I don’t think that would be an issue, although I could
> be mistaken.
>
> Appreciate the reference to Datomic, I will check it out.
>
> Best,
> Farhan
>
> On Mar 27, 2014, at 4:56 PM, Cory Dolphin <wcdolphin@gmail.com
> <mailto:wcdolphin@gmail.com>> wrote:
>
>> Hey Farhan,
>>
>> If you need to perform such auditing, why are you mutating the
>> underlying datastore? It sounds like what you want is actually an
>> immutable data store, where writes are essentially appends to a commit
>> log. If that is the case, you may want to check out something like
>> Datomic.
>> I fear maintaining your own database of changes is difficult to do
>> without introducing race conditions (though I am happy to be proved
>> wrong by others!)
>>
>> Cory
>