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
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