DEV Community: Siodb

Schemaless SQL database for strong and lasting back end

Nicolas Penot — Fri, 24 Sep 2021 08:53:28 +0000

Benefits of the solution

Schemaless structures maintain in the code
Your documents stored as relational tables transparently
Performance and efficiency of relational database
Easier to maintain in the long term
Consume much fewer resources (cheaper in the cloud)

Examples

Single document - code side

  var doc = {
         email: "dwain.jonhson@gmail.com",
         firstname: "Dwain",
         lastname: "Jonhson",
         username: "dwainjonhson"
  };
  doc.save();

  collection("users").find({username: "dwainjonhson"});

  /*
      {
         trid : 2,   // auto id generation
         email: "dwain.jonhson@gmail.com",
         firstname: "Dwain",
         lastname: "Jonhson",
         username: "dwainjonhson"
      }
  */

Single document - Database side

> select * from users;

TRID   EMAIL                       FIRST_NAME     LAST_NAME     USERNAME
------ --------------------------- -------------- ------------- --------------
     2 dwain.jonhson@gmail.com     Dwain          Jonhson       dwainjonhson

Nested documents - code side

  var doc = {
         email: "dwain.jonhson@gmail.com",
         firstname: "Dwain",
         lastname: "Jonhson",
         username: "dwainjonhson",
         phones: [{
           alias: "home",
           number: "+1-202-555-0143" 
         },{
           alias: "mobile",
           number: "+1-202-555-0156" 
         }]
  };
  doc.save();

  collection("users").find({username: "dwainjonhson"});

  /*
      {
         trid : 2,   // auto id generation
         email: "dwain.jonhson@gmail.com",
         firstname: "Dwain",
         lastname: "Jonhson",
         username: "dwainjonhson"
         phones: [{
           trid : 1,   // auto id generation
           alias: "home",
           number: "+1-202-555-0143" 
         },{
           trid : 2,    // auto id generation
           alias: "mobile",
           number: "+1-202-555-0156" 
         }]
      }
  */

Nested documents - database side

> select * from users;

TRID   EMAIL                       FIRST_NAME     LAST_NAME     USERNAME
------ --------------------------- -------------- ------------- --------------
     2 dwain.jonhson@gmail.com     Dwain          Jonhson       dwainjonhson

-- Nested phone documents automatically organized in table with the proper relationship.
> select * from users_phones;

TRID   USERD_TRID   ALIAS             NUMBER
------ ----------- ----------------- ------------------------
     1           2 home              +1-202-555-0143
     2           2 mobile            +1-202-555-0156

Conclusion

Push your document in the database without any worries about restructuring them later. Made all the modifications that you didn't predict because wasn't part of the initial requirements. And do this afterward with the comfort of SQL. Finally, reduce the cloud cost due to the resource consumption involved with traditional NoSQL databases.

This concept allows you to code fast with your data objects as documents (like Mongodb) while benefiting transparently from the relational databases (like PostgreSQL) .

Any questions? Please comment below :)

SQL queries through REST available now!

Nicolas Penot — Tue, 06 Jul 2021 06:40:15 +0000

curl -X POST \
-d '[
      {
         "EMAIL" : "dwain.jonhson@gmail.com",
         "FIRST_NAME" : "Dwain",
         "LAST_NAME" : "Jonhson",
         "USERNAME" : "dwainjonhson"
      }
]' \
https://${USER}:${TOKEN}@localhost:50443/database/my_app/tables/users/rows

curl https://${USER}:${TOKEN}@localhost:50443/query?q='select * from my_app.users;'

{
   "status" : 200,
   "rows" : [
      {
         "TRID" : 1,
         "EMAIL" : "jason.statham@gmail.com",
         "FIRST_NAME" : "Jason",
         "LAST_NAME" : "Statham",
         "USERNAME" : "jasonstatham"
      },
      {
         "TRID" : 2,
         "EMAIL" : "dwain.jonhson@gmail.com",
         "FIRST_NAME" : "Dwain",
         "LAST_NAME" : "Jonhson",
         "USERNAME" : "dwainjonhson"
      }
   ]
}

siocli> select * from my_app.users;

TRID   EMAIL                       FIRST_NAME     LAST_NAME     USERNAME
------ --------------------------- -------------- ------------- --------------
     1 jason.statham@gmail.com     Jason          Statham       jasonstatham
     2 dwain.jonhson@gmail.com     Dwain          Jonhson       dwainjonhson

Row(s): 2

Elapsed time: 15 ms.

Available in the cloud => https://datahub.siodb.io

Start your next app with Siodb, 100% encrypted, 100% automated.
Implemented in few minutes through REST API.

P.S. Let me know your feedback :)

Open data storage: Share your data along with your code in a minute!

Nicolas Penot — Tue, 16 Feb 2021 11:28:36 +0000

This tutorial assumes that you already have an account in the Siodb DataHub. If not yet the case, you can create one here for free.

Step 1: Create your database

Go on the DataHub

Step 2: Create your table

In the platform, create your table structure:

Step 3: Import the prepared snippet into your code

Choose your programming language and copy the prepared snippet in your code:

Conclusion

In a few times, you will have your database ready to be spread - no more operations!

PS 1: Embed your database in your GitHub project along with your code:

PS 2: Your project is open? Explain your project to crowd: