Stored Procedure as a Service

Functions as a service (FaaS) is an emerging pattern to build APIs at scale. You can use various FaaS implementations such as AWS Lambda, Azure Functions, and Google Cloud Functions to build APIs ecosystem for your organisation. Stored procedures can be considered as a variation of FaaS. Developing APIs against these stored procedures is generally considered as an anti-pattern. But what if you have a substantial investment in stored procedures (8-12 years) and most of your business logic is embedded in stored procedures. In this talk, we discuss how you can leverage existing stored procedures to create a fast-track API transformation program on top of your legacy systems.

Transcript

1. Stored Procedure
   as a
   Service (SPaaS)
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

2. • solutions manager apac @ Isentia
   • leading media intelligence company
   • data and product engineering
   • data processing with NLP & search focus
   t @abhishektiwari w https://abhishek-tiwari.com
   Bit of
   context
   

   View Slide

3. Wolf in sheep's
   clothing
   • a data pipeline built using legacy technologies
   • database as queue, ETL using StoredProc
   • trigger calling StoredProc calling trigger
   • stateful, strongly coupled, monolith databases
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

4. • thin applications fat StoredProc VLDB
   • more than 5000 StoredProc written over 10Y
   • deliver new products/services in a short span
   • need to move fast - continuous delivery
   Our
   challenge
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

5. subroutine stored in the
   database data dictionary
   StoredProc
   Application (VC) Database
   Client UI
   In Browser
   StoredProc
   &
   UDFs
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

6. StoredProc
   good idea that quickly turned
   into massive technical debt
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

7. Moving data is harder
   than moving logic
   StoredProc
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

8. Good Bad Ugly
   StoredProc
   Concerns
   • improved performance
   • security and access control
   • highly procedural
   • limited constructs
   • can't pass objects
   • vendor lock-in/specific
   • maintainability
   • zillion parameters
   • business logic
   • versioning & testing
   • debugging & logging
   • continuous delivery
   • cross-database StoredProc
   t @abhishektiwari w https://abhishek-tiwari.com
   

   View Slide

9. Phased Approach
   t @abhishektiwari w https://abhishek-tiwari.com
   Ecosystem of Microservices
   Decouple UI and Backend Ecosystem of APIs
   

   View Slide

10. Separate UI layer from the
    backend layer
    t @abhishektiwari w https://abhishek-tiwari.com
    Client UI
    In Browser
    APIs
    Static Stateless UI
    (Angular/React)
    Delivered via CDN
    w https://example.com
    w https://api.example.com
    Mobile
    Apps
    

    View Slide

11. Convert legacy backend layer
    into ecosystem of APIs
    t @abhishektiwari w https://abhishek-tiwari.com
    Ecosystem of APIs
    on top of Databases
    Instant CRUD APIs
    without code
    LoopBack Script Logic
    DreamFactory Script Logic
    Roll your own APIs
    FaaS
    Authentication &
    Authorization Setup
    Script Logic
    SPaaS
    Authentication &
    Authorization Setup
    decision tree
    

    View Slide

12. SPaaS
    t @abhishektiwari w https://abhishek-tiwari.com
    API
    Gateway
    Client UI APIs
    (Node.js)
    w https://api.example.com
    Database
    StoredProc
    &
    UDFs
    

    View Slide

13. MSSQL
    StoredProc
    Node.js
    Express.js mssql
    Middleware Route Handler
    connect
    execute
    TDS driver
    TDS Protocol
    UDFs
    HTTP Request
    /v1/coverage/today/50
    HTTP Response
    t @abhishektiwari w https://abhishek-tiwari.com
    Promises, Streams
    or Callbacks
    Connection
    Pooling
    Wiring up StoredProc
    Execute
    SQL2JSON
    

    View Slide

14. Variation of
    FaaS
    • self-contained piece of reusable functionality
    • can be executed by events or API endpoints
    • read and write to database backend
    • can be written in multiple languages
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

15. Similar
    Concerns
    • vendor lock-in or specific implementations
    • tooling for continuous integration & delivery
    • monitoring, logging and debugging
    • testing, discovery, security and latency
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

16. SPaaS API Delivery
    Develop
    APIs
    Change
    committed
    Build &
    Test
    Promote
    to UAT
    Promote
    to Prod
    UAT
    Servers
    Prod
    Servers
    Artifacts
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

17. SPaaS SQL Delivery
    StoredProc
    Changes
    Change
    committed
    Build &
    Test
    Promote
    to UAT
    Schema
    Changes
    Change
    committed
    Promote
    to Prod
    Database
    UAT
    Database
    Prod
    Artifacts
    SQL Server
    Management Studio
    t @abhishektiwari w https://abhishek-tiwari.com
    Redgate SQL Compare
    Database
    INT
    Database
    DEV
    Redgate DLM Automation
    

    View Slide

18. • mock API request & response using Swagger
    • mock environments as first-class citizen
    • reusable API tests – contract, load, security
    • tooling interoperability using Swagger
    Mock
    first
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

19. Breaking
    Monolith
    • strict rule - no new StoredProc
    • separate business logic & data logic
    • build microservices – one at a time
    • each microservice owns it’s own data store
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

20. API
    Gateway
    Client UI APIs
    w https://api.example.com
    Database
    StoredProc
    &
    UDFs
    Service A DB-A
    Service B DB-B
    Service X DB-X
    CDC
    Transition
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

21. API
    Gateway
    Client UI APIs
    w https://api.example.com
    Service D DB-D
    Service E DB-E
    Service X DB-X
    CDC
    Future
    t @abhishektiwari w https://abhishek-tiwari.com
    Service C DB-C
    Service B DB-B
    Service A DB-A
    

    View Slide

22. Driving
    Outcomes
    • more than 200 APIs in less than 6 months
    • two new native mobile products
    • a new responsive web product
    • seamless opportunities – API economy
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

23. • team maturity drives API maturity model
    • experience design influences API reusability
    • developer experience defines overall success
    • use Swagger as epicentre of you API program
    Lessons
    Learned
    t @abhishektiwari w https://abhishek-tiwari.com
    

    View Slide

24. t @abhishektiwari w https://abhishek-tiwari.com
    Q&A
    

    View Slide