"Back to the future" with SQL and stored procedures

Stored procedures are frequently used by database administrators to encapsulate application data logic, but the web development community has never embraced them because of concerns about portability, maintainability and a general distaste for SQL.

But the currently standard alternative, the duo of database abstraction and ORM, comes with some baggage of its own: poor performance, underutilization of advanced database features, and ad-hoc query syntaxes that end up being more complex than SQL itself.

The growing acceptance of NoSQL databases show us that contemporary web developers are again willing to consider vendor-specific database libaries for application development, so let's see what one vendor in particular - PostgreSQL - has to offer.

In this talk I'll show how building an API based on a mix of simple Ruby objects and Postgres stored procedures can be a very compelling alternative to ORM.

This talk was given in Spanish at Rubyconf Argentina in 2011.

Transcript

1. Norman Clarke Business Vision Ruby Labs @compay Volver al futuro con SQL y stored procedures
5. Lo que les espera... • Características, ventajas y desventajas de stored procedures • Una librería experimental basada en stored procedures • Stored procedures vs. ORM
6. Stored Procedures programación "real" con SQL
7. postgres=# select greeting(); greeting ------------- hello world! (1 row)
8. 1 CREATE FUNCTION greeting() 2 RETURNS TEXT AS $$ 3 BEGIN 4 RETURN 'hello world!'; 5 END; 6 $$ LANGUAGE 'plpgsql';
9. declare begin select ... if ... then update ... else while ... loop ... end loop; end if; return ...; end;
10. ¿Cuántas funciones? Postgres: 2333 Lua: 135
11. Ventajas
12. Cacheo automático de consultas
13. Menos coordinación entre la BD y Ruby O Java, Python, Perl, PHP, etc.
14. Encapsulación del esquema Stored Procedures Esquema Ruby
15. Toda la lógica de negocios en SQL
17. No hagan eso por favor
18. Squirm github.com/bvision/squirm
19. Squirm • Azúcar sintáctico para la gema "pg" • Connection pool básico • Stored procedures como procs o lambdas
20. 1 Squirm.connect host: "localhost" 2 Squirm.transaction do 3 Squirm.exec "SELECT ..." do |result| 4 result.to_a 5 end 6 Squirm.rollback 7 end
21. 1 Squirm do 2 connect host: "localhost" 3 transaction do 4 exec "SELECT ..." do |result| 5 result.to_a 6 end 7 rollback 8 end 9 end
22. 1 Squirm do 2 exec "CREATE FUNCTION ..." 3 proc = procedure "greeting" 4 proc.call "Juan" 5 #=> "¡hola Juan!" 6 end
23. 1 class Foo 2 3 @@bar = Procedure.load "bar" 4 5 def bar(*args) 6 @@bar.call(*args) 7 end 8 end 9 10 foo = Foo.new 11 foo.bar("hello")
24. GET followers/ids GET friends/ids GET lists/all GET favorites GET statuses/home_timeline GET statuses/mentions GET statuses/user_timeline GET direct_messages
25. SELECT followers.ids() SELECT friends.ids() SELECT lists.all() SELECT favorites() SELECT statuses.home_timeline() SELECT statuses.mentions() SELECT statuses.user_timeline() SELECT direct_messages()
26. Squirm Model github.com/bvision/squirm_model
27. Squirm Model • Generador de tablas, procedures • SQL "scaffolding" • Active Model
28. $ squirm table person id email birth_date access_time bio 1 CREATE TABLE "person" ( 2 "id" SERIAL NOT NULL PRIMARY KEY, 3 "email" VARCHAR(64) NOT NULL UNIQUE, 4 "birth_date" DATE, 5 "access_time" TIMESTAMP WITH TIME ZONE, 6 "bio" TEXT 7 );
29. $ squirm table person id created_at 1 CREATE TABLE "person" ( 2 "id" SERIAL NOT NULL PRIMARY KEY, 3 "created_at" TIMESTAMP WITH TIME ZONE NOT NULL 4 ); 5 6 CREATE OR REPLACE FUNCTION "update_person_created_at_timestamp" 7 RETURNS TRIGGER AS $$ 8 BEGIN 9 NEW.created_at = NOW(); 10 RETURN NEW; 11 END; 12 $$ LANGUAGE 'plpgsql'; 13 14 CREATE TRIGGER "update_person_created_at_timestamp" 15 BEFORE INSERT ON "person" 16 FOR EACH ROW EXECUTE PROCEDURE "update_person_created_at_time
30. $ squirm table person id email --api CREATE TABLE "person" ... CREATE SCHEMA "person" ... CREATE FUNCTION "person.get" ... CREATE FUNCTION "person.create" ... CREATE FUNCTION "person.update" ... CREATE FUNCTION "person.delete" ...
31. 1 class Person 2 extend Squirm::Model ... 3 validates_presence_of :name 4 end 5 6 Person.create(...) 7 @person = Person.find(1) 8 @person.valid? 9 @person.to_json 10 redirect_to @person
32. 1 class Person 2 extend Squirm::Model 3 4 sample do |s| 5 s.id = 1 6 s.name = "Juan Fulano" 7 end 8 9 validates_presence_of :name 10 end
33. 1 class PersonTest < Test::Unit::TestCase 2 def test_create 3 assert Person.create(Person.sample) 4 end 5 end
34. 1 Squirm do 2 connect host: "localhost" 3 4 exec Person.to_ddl 5 6 Person.finalize 7 8 p = Person.create name: "John" 9 p.update name: "Johnny" 10 p.delete 11 end
35. ROFLSCALE 0 7.5 15 22.5 30 Squirm Model ActiveRecord Benchmarks
36. ¿Por qué no usar un ORM?
37. Usen los ORM • Active Record • DataMapper • Sequel • otros
38. Pero conozcan sus defectos
39. Exhibition.all( :run_time.gt => 2, :run_time.lt => 5 ) run_time > 1 AND run_time < 5 ...you might be wondering how we can specify conditions beyond equality without resorting to SQL. Well, thanks to some clever additions to the Symbol class, it’s easy!
40. table = Product.arel_table Product.where( table[:price].eq(2.99). or(table[:name].matches("%foo")) ).to_sql #=> "WHERE price = 2.99 OR name LIKE '%foo'" railscasts.com/episodes/215-advanced-queries-in-rails-3
41. SQL (mal) generado
42. La abstracción dificulta el uso de features avanzados
43. Los stored procedures ofrecen una alternativa interesante
44. "Pensemos diferente"
45. Nihil sub sole novum
46. Postgres y MySQL están muy desaprovechados
47. No usemos abstracciones innecesarias
48. ¡Gracias!
50. Obelisco: flickr.com/photos/budgetplaces/4173902613/ Matz: flickr.com/photos/rrrodrigo/2394122680/ Gracias, fotógrafos