Save 37% off PRO during our Black Friday Sale! »

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

  2. • solutions manager apac @ Isentia • leading media intelligence

    company • data and product engineering • data processing with NLP & search focus t @abhishektiwari w 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
  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
  5. subroutine stored in the database data dictionary StoredProc Application (VC)

    Database Client UI In Browser StoredProc & UDFs t @abhishektiwari w
  6. StoredProc good idea that quickly turned into massive technical debt

    t @abhishektiwari w
  7. Moving data is harder than moving logic StoredProc t @abhishektiwari

  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
  9. Phased Approach t @abhishektiwari w Ecosystem of Microservices Decouple

    UI and Backend Ecosystem of APIs
  10. Separate UI layer from the backend layer t @abhishektiwari w Client UI In Browser APIs Static Stateless UI (Angular/React) Delivered via CDN w w Mobile Apps
  11. Convert legacy backend layer into ecosystem of APIs t @abhishektiwari

    w 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 API Gateway Client UI APIs

    (Node.js) w 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 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
  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
  16. SPaaS API Delivery Develop APIs Change committed Build & Test

    Promote to UAT Promote to Prod UAT Servers Prod Servers Artifacts t @abhishektiwari w
  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 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
  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
  20. API Gateway Client UI APIs w Database StoredProc &

    UDFs Service A DB-A Service B DB-B Service X DB-X CDC Transition t @abhishektiwari w
  21. API Gateway Client UI APIs w Service D DB-D

    Service E DB-E Service X DB-X CDC Future t @abhishektiwari w 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
  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
  24. t @abhishektiwari w Q&A