Introspective RESTful APIs with Postgres and Django

Databases are the elephants in the room of modern technology: mature, reliable, sturdy fixtures in the technology world that have served us well for many decades. Databases stay relevant by continuing to reinvent themselves to serve new technologies further up the stack. The latest buzzwords further up that stack are APIs and Microservices, so prevalent that it is hard to see a tech advertisement that doesn't mention them. While related to "the cloud" and "big data", whatever the heck those terms actually mean, APIs and Microservices have a slightly less annoying marketing schtick and more concrete relations to relational databases.

But what do these relations look like in practice? In this talk, Timothy Allen of Wharton Research Data Services will show how his team at The Wharton School has evolved from providing financial data exclusively in SAS data formats to a robust backend powered by PostgreSQL, which allows financial research to happen in many ecosystems: still available in SAS, but also R, Python, Perl, Matlab, Julia, and more. He will present a case study of using Django REST Framework to build an "API through introspection."

This case study will show how WRDS built a full Django web site and RESTful web service by introspecting financial data stored in a PostgreSQL database cluster. The models for the ORM, serializers for the RESTful API, views for presenting the data to a user, filters for refining queries, URL routing, web browsable interface, user token authorization, and permissions, are all handled by introspecting various features of the PostgreSQL database information schema.

Transcript

1. I N T R O S P E C T I V E R E S T F U L A P I S W I T H P O S T G R E S A N D D J A N G O … A N D H O W I T W I L L M A K E Y O U R E N T I R E L I F E B E T T E R ! T H A N K Y O U T O : @ T H E P R A C T I C A L D E V F O R T H E FA K E C O V E R S , A N D T H E W R D S P D I & D ATA T E A M S F O R M A K I N G T H I S A L L P O S S I B L E !
2. I ’ M T I M . A N I T D I R E C T O R , A D VA N C E D I N I T I A T I V E S F O R W H A R T O N R E S E A R C H D A TA S E R V I C E S ( W R D S ) F O R M E R B A R C A M P P H I L LY C O - C H A I R , PA N M A V P P Y T H O N I S TA A N D D J A N G O N A U T ( P S F & D S F M E M B E R ) F U N L O V I N G N E R D , H O C K E Y FA N , G U I TA R I S T @ F L I P P E R PA - T W I T T E R , G I T H U B , FA C E B O O K , E V E RY W H E R E ! P H I L LY P U G A N D D J A N G O C O N U S O R G A N I Z E R
3. W R D S : A B R I E F H I S T O RY A S I N G L E S O U R C E F O R L E A D I N G G L O B A L R E S E A R C H D A TA B A S E S F I R S T A C A D E M I C C L I E N T: S TA N F O R D U N I V E R S I T Y I N 1 9 9 7 F I R S T G O V E R N M E N T C L I E N T: F R B N Y I N 2 0 0 3 F I R S T C O R P O R A T E C L I E N T: C O M PA S S - L E X E C O N I N 2 0 1 1 F O U N D E D I N 1 9 9 2 F O R W H A R T O N FA C U LT Y I N I T I A L LY P R O V I D E D S A S W I T H S & P A N D C R S P D A TA
4. W R D S : T O D AY A S I N G L E S O U R C E F O R L E A D I N G G L O B A L R E S E A R C H D A TA B A S E S S U P P O R T S S S H V I A C L O U D , S A S , R , P Y T H O N , W E B & M O R E 3 5 0 + T B O F D A TA ; 2 + P B T O TA L R A W S T O R A G E C O N T I N U I N G E X PA N S I O N B E Y O N D F I N A N C I A L D A TA 4 5 0 + I N S T I T U T I O N S ; ~ 5 0 % O F G R O W T H H A S B E E N I N T E R N A T I O N A L O V E R T H E PA S T F I V E Y E A R S 5 0 , 0 0 0 + A C T I V E U S E R S V I A W E B , S S H , R E M O T E C O N N E C T S
5. D U B I O U S D ATA B A S E D E C I S I O N S S P O I L E R A L E R T ! P O S T G R E S Q L H A S M A D E O U R L I V E S B E T T E R . S Q L S E R V E R F O R W E B S I T E D A TA R E S E A R C H D A TA - E X C E P T N Y S E TA Q - I N O R A C L E P E R M I S S I O N S … A L L R E S E A R C H D A TA WA S S T O R E D I N S A S . ( S A S 7 B D A T ) M Y S Q L F O R W E B S I T E D A TA
6. P G T O T H E R E S C U E … A P P R E H E N S I O N A B O U T “ Y E T A N O T H E R D A TA B A S E … ” D J A N G O A N D WA G TA I L O V E R C F A N D W O R D P R E S S I N T E G R A T I O N W I T H L D A P A N D P E R M I S S I O N S / A C L S G O A L : P O S T G R E S A S C A N O N I C A L S O U R C E F O R A C L S A L L R E S E A R C H D A TA S U C C E S S F U L LY L O A D E D I N P O S T G R E S P O S T G R E S B A C K I N G D J A N G O F O R T H E W E B S I T E
7. F O U N D AT I O N C O M P L E T E . B U I L D I N G T H E R E S T F U L A P I . 1 9 9 2 : W R D S WA S C R E A T E D T O D U M P D A TA T O L O T U S 1 - 2 - 3 . 2 0 1 7 : W R D S C R E A T E S A R E S T F U L A P I T O A N Y W H E R E . C O D E G E N E R A T E D B Y I N T R O S P E C T I N G P O S T G R E S Q L . W E B B R O W S A B L E E N D P O I N T S P E R M I T T E D B Y U S E R . F I LT E R S C R E A T E D F O R C O L U M N S F I R S T I N I N D E X . D J A N G O R E S T F R A M E W O R K : A N A M A Z I N G O P E N S O U R C E P R O J E C T F O R B U I L D I N G R E S T F U L A P I S I N D J A N G O M O D E L S , S E R I A L I Z E R S , F I LT E R S , A N D P E R M I S S I O N S .
8. T H E “ T O - D O ” L I S T T H E W R D S A P I WA S B U I LT F O R A F O R T U N E 5 0 C L I E N T W H O N E E D E D I T Y E S T E R D A Y. T H E R E ’ S S T I L L S O M E T H I N G S T O D O . A T T R A C T A WA Y H A R D C O D E D VA L U E S T O S E T T I N G S . C R E A T E P I P PA C K A G E A N D R E L E A S E T O G I T H U B / P Y P I . M A K E P O S T G R E S Q L T H E C A N O N I C A L P E R M I S S I O N S O U R C E . C L E A N U P C U R S O R . E X E C U T E ( ) M E T H O D S ; PA R A M E T E R I Z E . S W I T C H T O D J A N G O T E M P L A T E S F O R C O D E G E N E R A T I O N .
9. L E T ’ S L O O K AT C O D E & D O A L I V E D E M O ! S I N C E I ’ M A T O TA L M A S O C H I S T … @ F L I P P E R PA - T W I T T E R / G I T H U B / E T C H T T P S : / / 2 0 1 7 . D J A N G O C O N . U S - J O I N U S ! T I M @ P Y P H I L LY. O R G http://whr.tn/techjobs