0

Shotgun API vs Direct Database Access

Hello,

I was wondering just how dangerous it is to deal with the shotgun database directly, or perhaps via a Python OPM like Peewee or SqlAlchemy, rather than going through the API. Put differently, is there a lot of high level logic in the api, with hidden but important cascading behaviors? Or is the API essentially just exposing the basic data objects that we might alternatively use a different method to do?

I'd really like to create a suite of Python Peewee OPM classes based on our Shotgun needs, but I worry about corrupting the database if I don't use the API. Anyone have any thoughts?

3 comments

  • 0
    Avatar
    Ben Zenker

    Giff,

    From what I've used it for, it appears that the API is simply a way to create database rows, query the database columns, and update the values in any database column.

    Not sure if that answers your question

  • 0
    Avatar
    Satish Goda

    My guess is that the database is the low-level and its schema might be changed to improve speed. The API provides the filters to query the database (and seems fairly stable and would not change). So, using the API is better. Otherwise, you might have to update your OPM classes in case the schema changed.

  • 0
    Avatar
    Tony Aiello

    TL;DR:  read-only is safe, deleting EventLogEntry rows is safe, anything else is dangerous.

     ----

    We have looked into this and we do use a bit of limited direct-database action.

    It definitely can be dangerous to *write* to the database directly, most particularly if creating new fields, and especially entity fields.  Yes, there is some higher-level logic implemented server-side, that would not get executed if you go straight to the db.  Don't do this.

    If you want to fill-in some alphanumeric data directly into some columns on some rows, you might be OK but I can't guarantee db integrity or things like data getting memcache'd for faster searching.  Probably shouldn't do this.

    You can delete EventLogEntry rows with impunity but I've been cautioned not to delete rows from any other table.

    It is safe to *read* from the database directly -- we use the psycopg2 module for this purpose in some cases to get a nice speed boost.  You do need to know a lot more about the underlying schema, and in doing so you can often create a more-optimized version of a SQL query than the API+server may otherwise construct, which is where you'd get that speed.  So yes, if there's a schema change, you may have account for it in your code; that said, we haven't had to do so in a long time.

    All of this presumes that you have a local installation, because you then control the credentials to get at your local database.  I don't believe you can get the username + password for the db if you're cloud-hosted.

Please sign in to leave a comment.