Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Stored Procedure as a Service

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.


Abhishek Tiwari

October 06, 2016


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

  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
  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
  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
  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
  6. StoredProc good idea that quickly turned into massive technical debt

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

    w https://abhishek-tiwari.com
  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
  9. Phased Approach t @abhishektiwari w https://abhishek-tiwari.com Ecosystem of Microservices Decouple

    UI and Backend Ecosystem of APIs
  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
  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
  12. SPaaS t @abhishektiwari w https://abhishek-tiwari.com API Gateway Client UI APIs

    (Node.js) w https://api.example.com Database StoredProc & UDFs
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  24. t @abhishektiwari w https://abhishek-tiwari.com Q&A