hands on with postgrest, csv to rest api

objective: create a restful api service to expose data inside a csv using postgres and postgrest. in short: csv file -> data api

machine: macbook air

1. Install Postgres.app

2. Import csv to postgres via psql

a) psql tool setup. see here. mine is different as the version is a little bit old.

b) my test csv file

repository_url,actor,created_at
https://github.com/matthewhartman/base,tamimibrahim,2014-08-31 23:59:58
https://github.com/mmcgrana/services-engineering,code-guru,2014-08-31 23:59:37
https://github.com/skatejs/skatejs,bencevans,2014-08-31 23:59:35

c) psql -h localhost

CREATE DATABASE csv;
\c csv
create schema "1";
CREATE TABLE "1".test (repository_url varchar, actor varchar, created_at timestamp);
COPY "1".test FROM '/path/to/test.csv' DELIMITER ',' CSV HEADER;

2. Install postgrest on mac

a) download osx version from https://github.com/begriffs/postgrest

b) it’s in xz format. Install “The Unarchiver” from mac app store. It’s free. then extract the binary

3. Run postgrest

./postgrest-0.2.6.0 --db-host localhost --db-port 5432 --db-name csv --db-user <username> --anonymous <username> --port 3000

4. test api

open browser, http://localhost:3000/test?actor=eq.bencevans, you get

[{"repository_url":"https://github.com/skatejs/skatejs","actor":"bencevans","created_at":"2014-08-31 23:59:35"}]
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s