JustOneDB

This add-on is operated by JustOneDB

JustOneDB - A NewSQL database designed and built for the cloud.

JustOneDB

Last Updated: 13 March 2014

addons database

This article is a work in progress, or documents a feature that is not yet released to all users. This article is unlisted. Only those with the link can access it.

Table of Contents

This add-on have been deprecated, and is now disabled for new installs.

JustOneDB Add-On for Heroku

PostgreSQL compatible NewSQL relational database.

Provisioned with a single click, simple to operate and cost effective. Easy, fast, no admin, no tuning and no limits. Choose how you wish to connect and then leave the database to us. Your application just works instantly and seamlessly. JustOneDB is fast (like every column is indexed) yet requires no design or administration. Data is fully replicated and backed-up (paid plans). Pay only for your raw data size and simple migration from other databases.

Dashboard

The JustOneDB dashboard allows you to see how many tables are in your database, how much space you are currently using, together with your connection details. It also allows you to import, export or migrate data to/from your database.

Main Screen

Installing the add-on - developer plan

To use JustOneDB on Heroku under the developer plan, install the JustOneDB add-on as follows:

$ heroku addons:add justonedb

Database provisioning is carried out synchronously. Once the request has completed, you are good to go. Enjoy.

Installing the add-on - production plan

To use JustOneDB on Heroku under a production plan, install the JustOneDB add-on as follows:

$ heroku addons:add justonedb:plan_name

Sigma provisioning is carried out synchronously, all others are asynchronous. The progress of asynchronous requests can be seen on the dashboard, or by checking for the JUSTONEDB_SUPPORT_ID environment variable.

$ heroku config | grep JUSTONEDB_SUPPORT_ID

JUSTONEDB_SUPPORT_ID => cb7a8ddd

Upgrading to a production plan

Once you have completed your development testing, and are ready to upgrade to production, or you want to move to a bigger plan, do the following.

$ heroku addons:upgrade justonedb:plan_name

Now you can migrate your data using the web interface. Either click on the link to JustOneDB from the Heroku dashboard, or use

$ heroku addons:open justonedb

See the section ‘Migrating Data’ below.

Removing the add-on

If you need to remove the JustOneDB add-on, use the following.

WARNING: You will lose all your data from all of your JustOneDB databases in the current Heroku application.

$ heroku addons:remove justonedb

Migrating Data

Migration Screen

If you have an existing PostgreSQL database it’s simple to move your data into JustOneDB. Should you want to move your data away from JustOneDB to another PostgreSQL database that’s easy too! You can choose to use the command line, justonedb gem, or the dashboard.

Importing into JustOneDB from a PostgreSQL Database

Use either the standard pg_dump tool or another backup tool to create a PostgreSQL dump file. The dump file can be held locally or accessed remotely via a URL (for example, on S3)

You will need the credentials from your JustOne DBI URL

$ heroku config | grep JUSTONEDB_DBI_URL
JUSTONEDB_DBI_URL => postgres://user:pass@host:port/database

To restore the database from a local backup, use the credentials above in the following restore command:

$ PGPASSWORD=pass pg_restore -O --no-tablespaces --no-acl -h host -p port -U user -d database filename

To restore the database via a URL, obtain the remote URL, then copy and paste it and use the credentials in the following restore command:

$ curl "URL" | PGPASSWORD=pass pg_restore -O --no-tablespaces --no-acl -h host -p port -U user -d database

Exporting from JustOneDB

Should you want to move your data out of JustOneDB, use the credentials in the following pg_dump command to export your data:

$ PGPASSWORD=pass pg_dump --verbose -Fc --no-acl --no-owner --no-tablespaces -h host -p port -U user > filename

Import/Export Using the dashboard

To import or export from a database, simply use the appropriate dashboard button. Note, that for importing, we currently only support ASCII dumps from postgres made using the pg_dump -Fc flag.

Migrating using the justonedb gem

It is possible to use the gem to migrate your data from one JustOneDB plan to a new one following a plan change.

$ gem install justonedb
Fetching: justonedb-1.1.1.gem (100%)
Successfully installed justonedb-1.1.1
1 gem installed
Installing ri documentation for justonedb-1.1.1...
Installing RDoc documentation for justonedb-1.1.1...

$ justonedb heroku migrate

Perform your migration tests, then

$ justonedb heroku promote

And to clean up old parameters, as they will no longer be required

$ justonedb heroku purge-old

Getting Started - Ruby/Rails

To get started with JustOneDB in a new Rails application under Heroku, just install the justonedb gem, and change the DATABASE_URL using the following:

$ gem install justonedb
Fetching: justonedb-1.1.1.gem (100%)
Successfully installed justonedb-1.1.1
1 gem installed
Installing ri documentation for justonedb-1.1.1...
Installing RDoc documentation for justonedb-1.1.1...

$ justonedb heroku make-default
Making JUSTONEDB_DBI_URL the default Rails database
DATABASE_URL now points directly to the JustOneDB database; application restarting

Alternatively, it can be done without using the justonedb gem.

$ heroku config | grep JUSTONEDB_DBI_URL
JUSTONEDB_DBI_URL => postgres://user:pass@host:port/database

$ heroku config:set DATABASE_URL='postgres://user:pass@host:port/database'

Getting Started - Ruby/Sequel

Here is a short Ruby example that creates a new table, inserts some rows, then queries them using the Sequel gem:

require 'rubygems'
require 'pp'
require 'sequel'

DB = Sequel.connect(ENV['JUSTONEDB_DBI_URL'])

DB.drop_table(:items) if DB.table_exists?(:items)

DB.create_table :items do
        primary_key :id
        String :name
        Float :price
end

items = DB[:items]
items.insert(:name => 'abc', :price => rand * 100)
items.insert(:name => 'def', :price => rand * 100)
items.insert(:name => 'ghi', :price => rand * 100)

puts "Item count: #{items.count}"

all_items = items.all
puts all_items.pretty_inspect

Getting Started - Ruby/ActiveRecord

Here is a short Ruby example that creates a new table, inserts some rows, then queries them using the ActiveRecord gem:

require 'rubygems'
require 'pp'
require 'uri'                # Needed due to bug in active_record outside Rails
require 'active_record'

# Establish a connection
ActiveRecord::Base.establish_connection(ENV['JUSTONEDB_DBI_URL'])

# Create a table (normally this would be in your Rakefile)
ActiveRecord::Migration.class_eval do
        drop_table :items if table_exists?("items")

        create_table :items do |t|
                t.string        :name
                t.float                :price
        end
end

class Items < ActiveRecord::Base
end

Items.create!(:name => 'abc', :price => rand * 100)
Items.create!(:name => 'def', :price => rand * 100)
Items.create!(:name => 'ghi', :price => rand * 100)
items = Items.all

puts "Item count: #{items.count}"

puts items.pretty_inspect

Getting Started - Perl

Here is a short Perl example that creates a new table, inserts some rows, then queries them using the DBI module:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Env qw(JUSTONEDB_DBI_URL);

my ($scheme, $username, $password, $host, $port, $database) = split(/:\/+|[:@]|\//, $JUSTONEDB_DBI_URL);

my $dbh = DBI->connect("dbi:Pg:database=$database;host=$host;port=$port", $username, $password);

$dbh->do("drop table items") if $dbh->tables('%', '%', 'items', 'TABLE');
$dbh->do("create table items (id serial primary key, name text, price float4)");

$dbh->do("insert into items (name, price) values (?, ?)", undef, 'abc', rand(100));
$dbh->do("insert into items (name, price) values (?, ?)", undef, 'abc', rand(100));
$dbh->do("insert into items (name, price) values (?, ?)", undef, 'abc', rand(100));

my $rows = $dbh->selectall_hashref("select * from items", 'id');

foreach my $key (sort keys %$rows)
{
        printf "id: %d, name: %s, price: %f\n", $rows->{$key}->{'id'},
                $rows->{$key}->{'name'}, $rows->{$key}->{'price'};
}

Getting Started - Python

Here is a short Python example that creates a new table, inserts some rows, then queries them using the psycopg2 library:

import os
import random
import urlparse
import pprint
import psycopg2

dsn = os.environ['JUSTONEDB_DBI_URL']
url = urlparse.urlparse(dsn)

# Connect to the database
conn = psycopg2.connect(database = url.path[1:],
                        user = url.username,
                        password = url.password,
                        host = url.hostname,
                        port = url.port)

# Open a cursor from the connection
cur = conn.cursor()

# Drop the table if it already exists
cur.execute("select relname from pg_class where relname = 'items' and relkind = 'r'")
if bool(cur.rowcount):
        cur.execute("drop table items")

# Create the example table
cur.execute("create table items (id serial primary key, name text, price float4)")

# Insert some rows ...
cur.execute("insert into items (name, price) values (%s, %s)", ('abc', random.random() * 100))
cur.execute("insert into items (name, price) values (%s, %s)", ('def', random.random() * 100))
cur.execute("insert into items (name, price) values (%s, %s)", ('ghi', random.random() * 100))

# Select them and display the contents
cur.execute("select * from items")
records = cur.fetchall()
pprint.pprint(records)

conn.commit()

# Close the cursor and database connection
cur.close()
conn.close()

Getting Started - Java

Here is a short Java example using JDBC, that creates a new table, inserts some rows, then queries them using:

package JustOneDB;

import java.sql.*;
import java.util.*;
import java.net.URI;

/**
 * Example JDBC code.
 */
public class JustOneDB {

  /**
   * Main method
   */
  public static void main(String[] aArguments) throws IllegalArgumentException,SQLException,URISyntaxException {

        //connect to the database
        String dbi_url = System.getenv("JUSTONEDB_DBI_URL");
        URI uri = new URI(dbi_url);
        String connectionString = String.format("jdbc:postgresql://%s:%d/%s", uri.getHost(), uri.getPort(), uri.getPath().replaceFirst("/", ""));
        Connection connection = DriverManager.getConnection(connectionString, uri.getUserInfo().split(":", 0)[0], uri.getUserInfo().split(":", 0)[1]);

        //construct statement
        Statement statement=connection.createStatement();

        //create table
        statement.executeUpdate("CREATE TABLE items (id SERIAL, name VARCHAR, price DECIMAL(5,2))" );

        //construct random number generator
        Random random=new Random();

        //insert rows
        statement.executeUpdate("INSERT INTO items (name,price) VALUES('abc',"+random.nextInt(100)+")");
        statement.executeUpdate("INSERT INTO items (name,price) VALUES('abc',"+random.nextInt(100)+")");
        statement.executeUpdate("INSERT INTO items (name,price) VALUES('abc',"+random.nextInt(100)+")");

        //execute query
        ResultSet resultSet = statement.executeQuery("SELECT COUNT(*),AVG(price) FROM items");

        //get result row
        resultSet.next();
        resultSet.getRow();

        //get count and average
        int count = resultSet.getInt(1);
        float average = resultSet.getInt(2);

        //print results
        System.out.println("Item count:"+count);
        System.out.println("Average price:"+average);

        //close down
        statement.close();
        connection.close();

        }//main()

}//JustOneDB{}

Getting Started with REST - Python with requests

Here is a short Python example using the REST interface, that creates a new table, inserts some rows, then queries them using the requests library:

import os, requests, json, urlparse, pprint, time, random

parsed_url = urlparse.urlparse(os.environ['JUSTONEDB_REST_BASE'])
db_url = os.environ['JUSTONEDB_REST_DB']
username = parsed_url.username
password = parsed_url.password
base_url = parsed_url.scheme + '://' + parsed_url.hostname + ':' + str(parsed_url.port) + parsed_url.path

# Create a new REST session
r = requests.post(base_url + db_url + '/session', auth=(username, password))
session_url = json.loads(r.content)['session']
cookies = r.cookies

# Create a table
data = json.dumps( { 'name': 'items',
                     'column' :
                            [ { 'name': 'id', 'type': 'Number' },
                              { 'name': 'name', 'type': 'String' },
                              { 'name': 'price', 'type': 'Number' }
                            ]
                    })
r = requests.post(base_url + session_url + '/table', data, cookies=cookies)
table_url = json.loads(r.content)['table']

# Insert some rows
data = json.dumps( { 'commit': 'yes',
                     'column' :
                              [ { 'name': 'id', 'value': 1 },
                                { 'name': 'name', 'value': 'abc' },
                                { 'name': 'price', 'value': random.random() * 100 }
                              ]
                    })
r = requests.post(base_url + table_url + '/row', data, cookies=cookies)

data = json.dumps( { 'commit': 'yes',
                     'column' :
                              [ { 'name': 'id', 'value': 2 },
                                { 'name': 'name', 'value': 'def' },
                                { 'name': 'price', 'value': random.random() * 100 }
                              ]
                    })
r = requests.post(base_url + table_url + '/row', data, cookies=cookies)

data = json.dumps( { 'commit': 'yes',
                     'column' :
                              [ { 'name': 'id', 'value': 3 },
                                { 'name': 'name', 'value': 'ghi' },
                                { 'name': 'price', 'value': random.random() * 100 }
                              ]
                    })
r = requests.post(base_url + table_url + '/row', data, cookies=cookies)

# Select the rows
data = json.dumps( { 'select': [ { 'table': 'items',
                                   'column' : [        'id', 'name', 'price' ]
                                  } ]
                    })
r = requests.post(base_url + session_url + '/query', data, cookies=cookies)
query_url = json.loads(r.content)['query']

data = json.dumps({ 'fetch': 0 })
r = requests.put(base_url + query_url, data, cookies=cookies)
data = json.loads(r.content)

print "Count = " + str(data['count'])
pprint.pprint(data['rows'])

# Drop the query, table, and the session
requests.delete(base_url + table_url, cookies=cookies)
requests.delete(base_url + query_url, cookies=cookies)
requests.delete(base_url + session_url, cookies=cookies)

Getting Started with REST - Ruby with RestClient

Here is a short Ruby example using the REST interface, that creates a new table, inserts some rows, then queries them using the rest_client library:

require 'rubygems'
require 'rest_client'
require 'json'
require 'pp'

url = ENV['JUSTONEDB_REST_BASE']
parsed_uri = URI.parse(url)
session_base = ENV['JUSTONEDB_REST_DB'] + '/session'

base_url = "#{parsed_uri.scheme}://#{parsed_uri.host}:#{parsed_uri.port}#{parsed_uri.path}"
username, password = parsed_uri.userinfo.split(':')

# Create a new REST session
site = RestClient::Resource.new(base_url, username, password)
site.options[:headers] = { :content_type => :json, :accept => :json }

response = site[session_base].post '', :content_type => 'text/json', :accept => 'text/json'
site.options[:cookies] = response.cookies
session_url = JSON.parse(response)['session']

# Create a table
data = { 'name' => 'items',
         'column'  =>
                 [ { 'name' => 'id', 'type' => 'Number' },
                   { 'name' => 'name', 'type' => 'String' },
                   { 'name' => 'price', 'type' => 'Number' }
                 ]
       }.to_json
response = site[session_url + '/table'].post data
table_url = JSON.parse(response)['table']

# Insert some rows
data = { 'commit' => 'yes',
         'column'  =>
                 [ { 'name' => 'id', 'value' => 1 },
                   { 'name' => 'name', 'value' => 'abc' },
                   { 'name' => 'price', 'value' => rand * 100 }
                 ]
       }.to_json
response = site[table_url + '/row'].post data

data = { 'commit' => 'yes',
          'column'  =>
                  [ { 'name' => 'id', 'value' => 2 },
                    { 'name' => 'name', 'value' => 'def' },
                    { 'name' => 'price', 'value' => rand * 100 }
                  ]
       }.to_json
response = site[table_url + '/row'].post data

data = { 'commit' => 'yes',
          'column'  =>
                  [ { 'name' => 'id', 'value' => 3 },
                    { 'name' => 'name', 'value' => 'ghi' },
                    { 'name' => 'price', 'value' => rand * 100 }
                  ]
        }.to_json
response = site[table_url + '/row'].post data

# Select the rows
data = { 'select' => [ { 'table' => 'items',
                         'column'  => [ 'id', 'name', 'price' ]
                       }
                     ]
       }.to_json
response = site[session_url + '/query'].post data
query_url = JSON.parse(response)['query']

data = { 'fetch' => 0 }.to_json
response = site[query_url].put data
json = JSON.parse(response)

puts "Count = #{json['count']}"
pp json['rows']

# Drop the query, table, and the session
site[table_url].delete
site[query_url].delete
site[session_url].delete

Access from your desktop - using psql

If you want to look at your database using Postgres psql, you will need to install the Postgres client locally. Here are some examples of how to do this in various OS’s:

Mac OS X

$ brew install postgresql

Ubuntu/Debian

$ apt-get install postgresql-9.1

RHEL/Fedora/CentOS

$ yum install postgresql91

Windows

To install the PostgreSQL client on Windows, visit the following:

PostgreSQL on Windows

Running psql under Linux

Either use this command snippet:

$ heroku config | grep JUSTONEDB >> ~/.bash_profile
$ source ~/.bash_profile
$ eval $(perl -e '@pg = split(/:\/+|[:@]|\//, $ENV{JUSTONEDB_DBI_URL}); print "PGPASSWORD=$pg[2] psql -U $pg[1] -h $pg[3] -p $pg[4] -d $pg[5]\n";')

or use the following (requires some copy/pasting):

$ heroku config | grep JUSTONEDB_DBI_URL
JUSTONEDB_DBI_URL => postgres://user:pass@host:port/database
$ PGPASSWORD=pass psql -U user -h host -p port -d database

Backups

Full daily backups are automatically taken on your behalf and kept for 7 days. These are stored on multiple sites.

Should you want to manually create your own additional backups and store them locally, this can be done using the command line pg_dump, or the dashboard.

pg_dump

$ PGPASSWORD=pass pg_dump --verbose -Fc --no-acl --no-owner --no-tablespaces -h host -p port -U user > filename

Backups Using the dashboard

Main Screen

To produce a backup from the dashboard, simple click on the Export button

Support

All JustOneDB support and runtime issues should be logged with Heroku Support at support.heroku.com. For any other related issues or product feedback, please email support@justonedb.com or Twitter @justonedb

Further Reading

See JustOneDB REST Reference Guide for details about using JustOneDB with the REST interface.

See JustOneDB Heroku Reference Guide for details about using JustOneDB on Heroku.