Awesome Open Source
Awesome Open Source

PostgreSQL shopping cart

All functionality is in PostgreSQL's PL/pgSQL functions.

SELECT * FROM … result
items_get() show all items
cart_get(person_id) get cart (unpaid invoice)
lineitem_add(person_id, item_id, quantity) add item to cart
lineitem_delete( delete lineitem in cart
lineitem_update(, quantity) change quantity (0=delete)
invoice_get( get order
invoice_update(, country) update country
invoice_update(, country, address) update address
invoice_delete( delete order
invoice_paid(, payment info) mark order as paid
invoices_get() show all orders
invoices_get_unshipped() orders needing to be shipped
invoice_shipped(invoice_id, info) mark order as shipped
invoices_get_for(person_id) this person's orders
items_get_for(person_id) items this person has paid for


createuser -s dude
createdb -U dude -E UTF8 dude_test
gem install pg
gem install json
cd store
ruby test-db.rb
ruby test-api.rb


ruby getdb-example.rb
psql -U dude dude_test
pg» set search_path = store,peeps;
pg» select * from invoices_get();
pg» select * from invoice_shipped(4, 'posted');

Every API function returns:

  1. "code" = HTTP status code
  2. "js" = JSON result

Directory structure in store/

Each function is its own file inside the subdirectories. Then make.rb merges them into schema.sql.

  • tables.sql = tables and indexes
  • api/ = public API functions (only use these)
  • functions/ = private functions used by API
  • triggers/ = triggers for data logic
  • views/ = re-usable views for JSON
  • fixtures.sql = sample data I use for testing
  • make.rb = every time you change a function in api/functions/triggers/views, re-run make.rb to re-generate schema.sql
  • schema.sql = generated by make.rb : don't alter
  • test-api.rb = unit tests of API calls
  • test-db.rb = unit tests of private functions and triggers


I put each project into its own schema (in this case "store") that references a central schema called "peeps" of all the people I know.

That's why peeps.people and peeps.countries are in a separate directory. My live version has way more to it than this, but I saved a subset of it here to show an example.

Nice thing about having a separate schema per-project, too, is for unit tests, just drop and re-build the schema inbetween every test.


Email me at

Sorry I won't be watching pull-requests and such, here. I'm posting this just as some example code.

Get A Weekly Email With Trending Projects For These Topics
No Spam. Unsubscribe easily at any time.
plpgsql (134