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

"Back to the future" with SQL and stored proced...

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

Norman Clarke

January 31, 2012
Tweet

More Decks by Norman Clarke

Other Decks in Programming

Transcript

  1. Lo que les espera... • Características, ventajas y desventajas de

    stored procedures • Una librería experimental basada en stored procedures • Stored procedures vs. ORM
  2. 1 CREATE FUNCTION greeting() 2 RETURNS TEXT AS $$ 3

    BEGIN 4 RETURN 'hello world!'; 5 END; 6 $$ LANGUAGE 'plpgsql';
  3. declare begin select ... if ... then update ... else

    while ... loop ... end loop; end if; return ...; end;
  4. Squirm • Azúcar sintáctico para la gema "pg" • Connection

    pool básico • Stored procedures como procs o lambdas
  5. 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
  6. 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
  7. 1 Squirm do 2 exec "CREATE FUNCTION ..." 3 proc

    = procedure "greeting" 4 proc.call "Juan" 5 #=> "¡hola Juan!" 6 end
  8. 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")
  9. 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
  10. 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()
  11. $ 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 );
  12. $ 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
  13. $ 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" ...
  14. 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
  15. 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
  16. 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
  17. 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!
  18. 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