Separate The Data Logic From Your Application Jack Chen @aquajach 

No content

No content

No content

No content

Object Relational Mapper (ORM) Ruby - Rails - Active Record Python - Django - QuerySets PHP - Laravel - Eloquent 

Why ORM? 

ORM Example class User < ActiveRecord::Base validates :email, format: { with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\z/i, on: :create } end 

ORM Example def index @users = User.all end def show @user = User.where('email = ? OR phone = ?', params[:login], params[:login]).first end 

ORM Example def create @user = User.new(params) if @user.save render 'show' else @errors = @user.errors render 'error' end end 

ORM Example json.data do json.name user.name json.email user.email json.phone user.phone json.registered_at user.created_at end 

ORM Example json.errors do json.array! @errors.full_messages do |message| json.message message end end 

ORM Example Get http://your_host/[email protected] Response: { "name": "Jack Chen", "email": "[email protected]", "phone": "+85266669999", "registered_at": "2016-10-09T07:13:00.338133" } 

ORM Example Post http://your_host/users.json Request: { "user": { "name": "Jack", "email": “jack@invalid_email”, "phone": "+86139131239123" } } Response: { "errors": [ { "message": "Email is invalid" } ] } 

No content

ORM Works...but 

No content

class Carnival def list_members_by_prize(prize) self.joins('INNER JOIN booths on booth.carnival_id = carnivals.id, INNER JOIN games ...') .select('...') .group('...') .where('...') .order('...') end end 

No content

Is DB only a storage? 

Stored Procedures SQL 

PostgreSQL Example create table users( name varchar, email varchar UNIQUE CONSTRAINT valid_email CHECK (email ~ '\A\S+@\S+\.\S+\Z'), phone varchar CONSTRAINT valid_phone CHECK (phone ~ '\A\+\d+\Z'), created_at timestamp default now() not null, updated_at timestamp default now() not null ); 

PostgreSQL Example create type user_json as ( name varchar(255), email varchar(255), phone varchar(255), registered_at TIMESTAMP ); 

PostgreSQL Example -- Get all users create function all_users(OUT data jsonb) as $$ DECLARE user_info user_json[]; BEGIN user_info := array_agg(s) from (select name, email, phone, created_at from users) as s; data := array_to_json(user_info); END; $$ LANGUAGE PLPGSQL; 

PostgreSQL Example -- Get a user create function get_user(login VARCHAR, OUT data jsonb) as $$ DECLARE user_info user_json; BEGIN select * from users where email = login or phone = login limit 1 into user_info; data := row_to_json(user_info); END; $$ LANGUAGE PLPGSQL; 

PostgreSQL Example -- Create a user create function create_user(new_name VARCHAR, new_email varchar, new_phone varchar, OUT data jsonb) as $$ BEGIN insert into users(name, email, phone) values(new_name, new_email, new_phone); data := get_user(new_email); Exception when integrity_constraint_violation then if sqlerrm like '%valid_phone%' then RAISE 'The phone format is invalid' USING CONSTRAINT = 'valid_phone'; ELSIF sqlerrm like '%valid_email%' then RAISE 'The email format is invalid' USING CONSTRAINT = 'valid_email'; END IF; END; $$ LANGUAGE PLPGSQL; 

def query(sql, params = []) @db = PG::Connection.new(dbname: 'plsql_orm_dev') @db.exec_params("select data from #{sql}", params).first['data'] end 

def index render json: query('all_users()') end def show render json: query('get_user($1)', [params[:login]]) end def create render json: query('create_user($1, $2, $3)', [user_params[:name], user_params[:email], user_params[:phone]]) end 

PL/pgSQL, PL/Perl, PL/Python, PL/V8, PL/Java, PL/Ruby 

create extension plv8; create or replace function print() returns VARCHAR as $$ return 'Hong Kong Code Conf is' + (false == [] ? '' : ' not') + ' awesome'; $$ LANGUAGE plv8; select print(); 

create extension plv8; create or replace function print() returns VARCHAR as $$ return 'Hong Kong Code Conf is' + (false == [] ? '' : ' not') + ' awesome'; $$ LANGUAGE plv8; select print(); // => “Hong Kong Code Conf is awesome!” 

create or replace function plruby_call_handler() returns language_handler as ‘../plruby.bundle’ language C; create or replace language plruby handler plruby_call_handler; 

CREATE OR REPLACE FUNCTION encrypt_code(data_string varchar, key_string varchar) returns varchar as $$ require “base64" require “openssl” cipher = OpenSSL::Cipher.new("AES-128-CBC") cipher.encrypt cipher.key = key_string crypt = cipher.update(data_string) + cipher.final crypt_string = (Base64.urlsafe_encode64(crypt)) return crypt_string $$ language plruby; select encrypt_code('-1476244774', 'dE2fhq/fD4f8Lq/4qoMXLg'); 

PostgreSQL extensions • Rust: https://github.com/thehydroimpulse/postgres-extension.rs • Python: Psycopg: http://initd.org/psycopg/docs/index.html 

Deployment • Source Control vs Not Source Control • Deployment Hook 

Data Logic vs Business Logic 

Simple vs Complex 

Don’t forget the hidden costs! 

Fast vs Slow 

Wrap Up