November 2, 2020

Cloud Starter Plan User Guide

After subscribing to a Cloud Starter Plan, you should receive after a few minutes an email with the following détails (otherwise, write us on support@siodb.io):

  • Database public IP: <Database-Machine-Public-IP>
  • Database private IP: <Database-Machine-Pivate-IP>
  • Client machine IP: <Client-Machine-IP>
  • Database name: <Database-Name>
  • User: <User-Name>
  • Token: <token-value>

Example:

  • Database public IP: 18.193.211.219
  • Database private IP: 10.0.0.189
  • Client machine IP: 18.193.167.75
  • Database name: dAzyw2lSFCXykb1Y2d
  • User: uAzyw2lSFCXykb1Y2d
  • Token: 9acd75a41e27787465e680781490b71bcc2d72059ef54e0da221c8c2e3ca4437b7bc4be4be60fc975080b7aa24bbfe376dd855d8c29f8578fe20dba88e858e69

SQL Client

To use the SQL client provided by the Siodb Cloud, you must download your private key received by email.

We recommend using the last version of the SSH client (Linux) or the last version of Putty (Windows) to access the SQL client-server.

Then you can connect to the client machine with your user and the private key as follow:

chmod 600 ~/<User-Name>.pem
ssh -p 49999-i ~/<User-Name>.pem <User-Name>@<Client-Machine-IP>

Once on the client machine, you can start a session with the Siodb instance hosting your database:

siocli --host <Database-Machine-Pivate-IP>

Then you can issue standard SQL commands to create a first table in your database:

create table <Database-Name>.employees ( firstname text, lastname text, salary float, hire_date timestamp) ;

insert into <Database-Name>.employees ( firstname, lastname, salary, hire_date)
values
( '马', '云', 249000.00, '1964-09-10' ),
( 'Юрий', 'Гагарин', 49000.00, '1934-03-09' ),
( 'Barack', 'Obama', 149000.00, '1961-08-04' )
;

Example:

$ ssh -p 49999 -i ~/uAzyw2lSFCXykb1Y2d.pem uAzyw2lSFCXykb1Y2d@18.193.167.75

  Welcome on the Siodb client machine !
  Support? Here => support@siodb.io

Last login: Mon Nov  2 17:43:26 2020 from 213.200.224.21

$ siocli --host 10.0.0.189
Siodb SQL Client v.0.8.0
Compiled on Oct 15 2020 08:58:24
Copyright (C) 2019-2020 Siodb GmbH. All rights reserved.

siocli> create table dAzyw2lSFCXykb1Y2d.employees ( firstname text, lastname text, salary float, hire_date timestamp) ;
Connected to 10.0.0.189:50000
Command execution time: 455 ms.

insert into dAzyw2lSFCXykb1Y2d.employees ( firstname, lastname, salary, hire_date)
      > values
( '马', '云', 249000.00, '1964-09-10' ),
( 'Юрий', 'Гагарин', 49000.00, '1934-03-09' ),
( 'Barack', 'Obama', 149000.00, '1961-08-04' )
      > ;
3 rows affected
Command execution time: 56 ms.

siocli> select * from dAzyw2lSFCXykb1Y2d.employees ;
TRID                FIRSTNAME                                LASTNAME                                 SALARY      HIRE_DATE                            
------------------- ---------------------------------------- ---------------------------------------- ----------- ----------------------------------------
                  1 马                                        云                                             249000 Thu Sep 10 1964 12:00:00.000000000 AM
                  2 Юрий                                     Гагарин                                        49000 Fri Mar 09 1934 12:00:00.000000000 AM
                  3 Barack                                   Obama                                         149000 Fri Aug 04 1961 12:00:00.000000000 AM

3 rows.
Command execution time: 8 ms.

Access to the full SQL documentation here.

REST API

Once you’ve created a least one table through SQL, you’re ready to use the REST API. To use the REST API with your database, you need your username, the token, the database public IP/Hostname, and your database name.

POST Request

curl -k -X POST \
-d '<JSON-PayLoad>' \
https://<User-Name>:<token-value>@<Database-Machine-Public-IP>:50443/databases/<Database-Name>/tables/employees/rows

Example:

curl -k -X POST \
-d '[
    { "firstname": "马","lastname": "云","salary": "249000.00","hire_date": "1964-09-10"},
    { "firstname": "Юрий","lastname": "Гагарин","salary": "49000.00","hire_date": "1934-03-09"},
    { "firstname": "Barack","lastname": "Obama","salary": "149000.00","hire_date": "1961-08-04"}
]' \
https://uAzyw2lSFCXykb1Y2d:9acd75a41e27787465e680781490b71bcc2d72059ef54e0da221c8c2e3ca4437b7bc4be4be60fc975080b7aa24bbfe376dd855d8c29f8578fe20dba88e858e69@18.193.211.219:50443/databases/dAzyw2lSFCXykb1Y2d/tables/employees/rows

Returns:

{
   "affectedRowCount" : 3,
   "status" : 200,
   "trids" : [
      4,
      5,
      6
   ]
}

GET Request

curl -k \
<User-Name>:<token-value>@<Database-Machine-Public-IP>:50443/databases/<Database-Name>/tables/employees/rows \
| json_pp -json_opt pretty,utf8

Example:

curl -k \
https://uAzyw2lSFCXykb1Y2d:9acd75a41e27787465e680781490b71bcc2d72059ef54e0da221c8c2e3ca4437b7bc4be4be60fc975080b7aa24bbfe376dd855d8c29f8578fe20dba88e858e69@18.193.211.219:50443/databases/dAzyw2lSFCXykb1Y2d/tables/employees/rows \
| json_pp -json_opt pretty,utf8

Returns:

{
   "rows" : [
      {
         "FIRSTNAME" : "马",
         "HIRE_DATE" : "1964-09-10",
         "LASTNAME" : "云",
         "SALARY" : 249000,
         "TRID" : 1
      },
      {
         "FIRSTNAME" : "Юрий",
         "HIRE_DATE" : "1934-03-09",
         "LASTNAME" : "Гагарин",
         "SALARY" : 49000,
         "TRID" : 2
      },
      {
         "FIRSTNAME" : "Barack",
         "HIRE_DATE" : "1961-08-04",
         "LASTNAME" : "Obama",
         "SALARY" : 149000,
         "TRID" : 3
      },
      {
         "FIRSTNAME" : "马",
         "HIRE_DATE" : "1964-09-10",
         "LASTNAME" : "云",
         "SALARY" : 249000,
         "TRID" : 4
      },
      {
         "FIRSTNAME" : "Юрий",
         "HIRE_DATE" : "1934-03-09",
         "LASTNAME" : "Гагарин",
         "SALARY" : 49000,
         "TRID" : 5
      },
      {
         "FIRSTNAME" : "Barack",
         "HIRE_DATE" : "1961-08-04",
         "LASTNAME" : "Obama",
         "SALARY" : 149000,
         "TRID" : 6
      }
   ],
   "status" : 200
}

Access to the full REST API documentation here.

Local SQL Client

Additionally, you can use a local Siodb client to interact with your database from your local environment without using the provided Siodb Client Machine.

To use a local Siodb client, you can install the latest version of Siodb or use an official Siodb container as describe here. Then you’ll be able to connect to your instance like this (download the private key to use as the value of the argument --identity-file):

<Path-To-Siodb-Bin-Directory>/siocli --host <Database-Machine-Public-IP> --user <User-Name> --identity-file ./<User-Name>.pem

Need help?

Need more details or want to give your feedback? Please send us an email to support@siodb.io, and we’ll get back to you soon.