Need help with store?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

222 Stars 16 Forks MIT License 8 Commits 0 Opened issues


PostgreSQL shopping cart

Services available


Need anything else?

Contributors list

# 321,014
8 commits

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.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.