Bringing PostgreSQL closer to your Application Layer

Introducing SQLpy a lightweight tool to help you organise SQL queries and data access in Python projects. https://sqlpy.readthedocs.io

Transcript

1. Bringing PostgreSQL closer to your
   Application Layer
   1
   

   View Slide

2. I used to work at an Investment Bank
   2
   

   View Slide

3. ...definitely not this!
   3
   

   View Slide

4. ...more like this
   4
   

   View Slide

5. ...specifically
   5
   

   View Slide

6. Hi I’m Huss
   Co-founder & CTO @ 9fin
   Investment Banking Technology
   ● Equities Trading
   ● Bespoke Trader Desk tools
   ● Risk and Control systems
   Aeronautical Engineering
   ● Lots of aeroplane stuff
   ● .. But no Databases! :(
   9fin.com
   @9finHQ
   angel.co/9fin
   6
   @HUSSTECH
   

   View Slide

7. Good
   choice! Where did it
   go?
   DBAs and Devs!? Never
   the twain shall meet
   7
   Bringing PostgreSQL closer to your
   Application Layer
   

   View Slide

8. Software Archeology
   ● BigCo EE: Silo groups with big databases
   ● “Fat Client” architecture
   ● 3 tier relatively recent
   ○ Typically endpoints on enterprise message bus
   ○ SOAP!
   8
   

   View Slide

9. BigCo Org Structure
   ● Hierarchical
   ● Long communication and decision chains
   ● “shared services” idea emerges 2008+
   ● DBAs and Sysadmins moved here
   ○ Away from business aligned teams
   ● Now hard to diagnose issues quickly
   ○ Everyone is not sitting together
   anymore
   9
   

   View Slide

10. WebCo 2.0
    ● Flatter structure - mixed teams, practitioners of many technologies
    ● Databases are out-of-the-box “good enough”
    ○ Heroku, RDS, et al
    ● The DBA function merged into Sr. Devs usually
    ● Explosion in dynamic languages with rich tooling for web development
    10
    

    View Slide

11. Object Relational Mapping
    ● Model your entities using an ORM library
    for your application
    ● Takes care of data retrieval and
    persistence
    ● Great for bootstrapping
    ○ Focus on app code not storage
    ○ Strong control of your business
    objects
    ● Rich libraries in many languages
    11
    

    View Slide

12. That awkward moment when you need SQL
    ● At some point you’re going to want to take
    control of the query generation
    ○ Performance tune
    ○ Force an index use
    ○ Use advanced DB features
    ■ WITH RECURSIVE,
    Subqueries/CTEs, PARTITION
    OVER, JSON
    ● Raw SQL support in ORMs varies
    Example: Cumulative total of revenue made at
    a fictional DVD rental business?
    12
    from flask import Flask
    from flask_sqlalchemy import SQLAlchemy
    from sqlalchemy.sql import func
    # setup SQLAlchemy and model object
    app = Flask(__name__)
    app.config['SQLALCHEMY_DATABASE_URI'] =
    'postgresql://xxx'
    db = SQLAlchemy(app)
    class Payment(db.Model):
    __tablename__ = 'payment'
    payment_id = db.Column(db.Integer, nullable=False,
    primary_key=True)
    rental_id = db.Column(db.SmallInteger, nullable=False)
    amount = db.Column(db.Numeric(scale=(4, 2),
    asdecimal=True), nullable=False)
    payment_date = db.Column(db.DateTime,
    nullable=False)
    

    View Slide

13. That awkward moment when you need SQL
    13
    # set up two query objects one for a subquery one for the window function
    subquery =
    db.session.query(func.DATE(Payment.payment_date).label('payment_date'),
    func.sum(Payment.amount).label('amount')
    ).group_by(func.DATE(Payment.payment_date))\
    .order_by(func.DATE(Payment.payment_date))\
    .subquery('s')
    window = db.session.query(subquery.c.payment_date,
    subquery.c.amount,
    func.sum(subquery.c.amount).over(
    order_by=subquery.c.payment_date)
    ).select_entity_from(subquery).order_by(subquery.c.payment_date)
    ● 10+ (readable) lines
    ● This is quite nice syntax
    actually
    ● But the semantics are
    halfway between Python
    object orientation and SQL
    declarative style
    How to do the same in SQL?
    SELECT payment_date, amount, sum(amount) OVER (ORDER BY payment_date)
    FROM ( SELECT CAST(payment_date AS DATE) AS payment_date, SUM(amount) AS amount
    FROM payment
    GROUP BY CAST(payment_date AS DATE)) p
    ORDER BY payment_date;
    

    View Slide

14. SQLpy - a (not) new idea
    ● Write SQL *next* to your
    application code
    ● No query strings dotted all over
    source files
    ● A light wrapper around your
    DB2.0 API
    ● Bases on YeSQL by Kris Jenkins
    14
    

    View Slide

15. SQLpy + Models
    ● Still like linking behaviours and actions via
    models?
    ● Use the DAO pattern - define the models
    how you want
    ○ Add extra methods to serialize or
    validate
    15
    class Hello(object):
    def __init__(self, queryfunc, cur, args):
    self._query = queryfunc
    self._result = self.result(cur, args)
    @property
    def result(self):
    return self._result
    @result.setter
    def result(self, cur, args):
    self._result = self._query(cur, 0, args)
    def refresh(self, cur, args):
    return self._query(cur, 0, args)
    def jsonout(self):
    return json.dumps(self.result)
    

    View Slide

16. SQLpy + Functions
    ● Prefer your app only make calls to stored
    procedures?
    ● At least the list of sprocs used is all in one
    place
    16
    -- name: cool_proc
    SELECT public.cool_proc();
    -- name: cooler_proc
    SELECT public.cooler_proc(%s, %s);
    sql = sqlpy.Queries('queries.sql')
    ....
    resultsA = sql.COOL_PROC(cur, 0)
    resultsB = sql.COOLER_PROC(cur, 0, args)
    

    View Slide

17. SQLpy + Composition
    ● Constructing SQL queries is legitimately dangerous!
    ● How to do this in SQLpy, but not destroy the valid SQL syntax in queries file?
    ● BUILT SQL
    ○ Clauses added based on parameters passed to wrapping function
    ○ Transparent and lightweight
    ● Query still passes through the DB2.0 API sanitiser for safe execution
    17
    

    View Slide

18. SQLpy + Composition
    ● Constructing SQL queries is legitimately
    dangerous!
    ● How to do this in SQLpy, but not destroy
    the valid SQL syntax in queries file?
    ● BUILT SQL
    ○ Clauses added based on parameters
    passed to wrapping function
    ○ Transparent and lightweight
    ● Query still passes through the DB2.0 API
    sanitiser for safe execution
    18
    -- name: built_sql_statement$
    -- a built up sql statement
    SELECT * FROM hello
    WHERE 1=1
    AND id = %(id_low)s
    OR id = %(id_high)s
    AND message = %(msg)s;
    sql = sqlpy.Queries('queries.sql')
    ....
    kwargs = {'id_low': 1}
    results = sql.BUILT_SQL_STATEMENT(cur, 0,
    **kwargs)
    SELECT * FROM hello
    WHERE 1=1
    AND id = 1;
    

    View Slide

19. SQLpy + Your SQL
    ● Essentially this is the one central idea behind SQLpy
    ● Let’s play with PostgreSQL
    ○ Correlate pg_stat_statements with the queries in SQLpy
    ○ Because you know what was logged (pg_stat_statements) and what was run
    (sqlpy)
    ○ See how your database is actually being used
    ○ Critical data for indexing and optimization decisions
    19
    SQLpy
    pg_stat_statements REGEX All the query context!
    

    View Slide

20. sqlpy.readthedocs.io
    ● github.com/9fin/sqlpy (MIT)
    ● Made for PostgreSQL first
    ○ Other DBs working in theory, but not
    tested!
    ● Still ○ ...so critical comments and other
    contributions welcome
    ● Check out other ports for YeSQL
    ○ Js, Go, C#, Ruby, Erlang, Clojure, PHP
    ○ github.com/krisajenkins/yesql/#othe
    r-languages
    20
    

    View Slide

21. Thank You!
    References and Credits [slide#]
    [3] image: By Raiffeisenverband Salzburg reg. Gen. m. b. H., Schwarzstr. 13-15, 5024 Salzburg -
    Raiffeisenverband Salzburg reg. Gen. m. b. H., Schwarzstr. 13-15, 5024 Salzburg (transferred from
    de:Image:RVS Handelsraum.jpg), CC BY 2.0 at,
    https://commons.wikimedia.org/w/index.php?curid=5009720
    [4] image: CC0 license
    [5] image: An unnamed colleague
    21
    

    View Slide