DEV Community: Nicolas Penot

My secret stack to deliver powerful GraphQL apps like experts

Nicolas Penot — Tue, 20 Dec 2022 14:23:20 +0000

I already knew the power of Postgres and its PL/pgSQL programming language (simple and fast). But it has been game-changing when I discover Postgraphile. Suddenly I was able to develop beautiful APPs offering exceptional UX.

Step by step, I end up having a full backup stack that I consider the best tech stack for applications. The reason is simple, I just integrated together the best of the open-source world:

Postgres: The World's Most Advanced Open Source Relational Database.
- Function: To build custom logic.
- Trigger: To trigger functions on data changes or events.
- pg_cron: To schedule function execution regularly.
- REST requests python package: to call any REST API.
Postgraphile: Fastest GraphQL API server for PostgreSQL.
Passport JS: Simple authentication for Node.js.
Unified Authentication: User gets a session that works for requests AND subscriptions.
Nodemailer: A module for Node.js applications to allow easy-as-cake email sending.

Bottom line

This full backup stack that I called Agoston allows me to build anything, integrate any API and scale large while ensuring the best performance that a database can offer thanks to Postgres.

P.S. I also launched a managed Agoston backend stack that you can test for free. Thus you can immediately get the benefits without the hurtle of implementing it! Give it a try here! :)

Full stack example using React, urql and GraphQL API - Part 2

Nicolas Penot — Tue, 22 Nov 2022 13:29:36 +0000

In the previous post, we saw how to generate your backend, enable Google Authentication, and start a new React project with GraphQL query.

Now, let's get through the process of building a small data model and implement the corresponding GraphQL query and subscription in
React to create a real-time chat.

This tuto is for beginners. You don't need to be a senior developer or be an expert in React, SQL, and GraphQL. Agoston's backend will provide the power of using GraphQL without having to deal with its complexity.

1. Connect to the Postgres instance of your backend

Go to the Agoston.io backends view. Expand the View of the Postgres access of your backend:

You will see the information to connect to your dedicated Postgres instance:

You can use the Postgres client or IDE of your choice, such as DataGrip, VS Code, pgAdmin, etc. I personally use pgAdmin which works perfectly.

2. Create your data model in the Postgres instance

Agoston is database centric. You do everything in the database, and Agoston will automatically expose it to the GraphQL endpoint.

So, create a new Postgres server in pgAdmin:

Add the connection details that you get in the Agoston Postgres access popup:

Still, on pgAdmin, we expand the Postgres server we just created and open a new "Query tool":

And we create two tables that will handle our chat data:

CREATE TABLE IF NOT EXISTS agoston_public.chats
(
    id serial,
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    created_ts timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chats_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS agoston_public.chat_messages
(
    id serial,
    chat_id integer NOT NULL,
    user_id integer NOT NULL DEFAULT get_current_user_id(),
    created_ts timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message character varying(250) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT chat_messages_pkey PRIMARY KEY (id),
    CONSTRAINT chat_messages_chat_id FOREIGN KEY (chat_id)
        REFERENCES agoston_public.chats (id),
    CONSTRAINT chat_messages_user_id FOREIGN KEY (user_id)
        REFERENCES agoston_identity.user_identities (id)
);

Finally, grant necessary permissions for this demo:

GRANT SELECT ON TABLE agoston_public.chats TO anonymous, authenticated;
GRANT SELECT ON TABLE agoston_public.chat_messages TO anonymous, authenticated;

3. Create a GraphQL query from the built-in GraphiQL IDE

Your Agoston backend comes with a GraphiQL IDE that helps you build your requests. Open it from the backends view:

A new tab opens with GraphiQL. In GraphiQL, there is an explorer of your GraphQL schema. That's very convenient for exploring your schema and building GraphQL requests. Open it here:

In the explorer, you'll see the chat tables we created before.

NOTE: Any change to the data model in Postgres will automatically be included in the GraphQL endpoint.

Now we can create the first query to retrieve the chat messages from our chat id 1:

query chatMessages($chatId: Int! = 1) {
    chatMessages(condition: {chatId: $chatId}, orderBy: ID_DESC, first: 25) {
      nodes {
        id
        createdTs
        userId
        message
      }
    }
  }

4. Implement the GraphQL query in React

In our React application, I create not only the query but also the subscription that will listen for new messages coming in the chat messages table:

import { useQuery, useMutation, useSubscription } from 'urql';
import { gql } from '@urql/core';

// Here is a fragment that I will use to avoid 
// duplicated attributes in the query and in 
// the subscription. Because they return the 
// same attributes
const ChatMessagesFragment = gql`
  fragment MessageNode on ChatMessage {
    id
    createdTs
    userId
    message
  }
`;

// Here is the Query executed during 
// the module loading
const ChatMessages = gql`
  query chatMessages($chatId: Int!) {
    chatMessages(condition: {chatId: $chatId}, orderBy: ID_DESC, first: 25) {
      nodes {
        ...MessageNode
      }
    }
  }
  ${ChatMessagesFragment}
`;

// Here is the subscription that will 
// load new messages
const NewChatMessages = gql`
  subscription chatMessages($chatId: Int!, $topic: String!) {
    listen(topic: $topic) {
      query {
        chatMessages(condition: {chatId: $chatId}, orderBy: ID_DESC, first: 25) {
          nodes {
            ...MessageNode
          }
        }
      }
    }
  }
  ${ChatMessagesFragment}
`;

5. Create the React component that will use the GraphQL query and subscription

function ChatBoxMessages() {

  const chatId = 1;

  // We do a first initial query to load our chat data
  const [resultQuery] = useQuery({
    query: ChatMessages,
    variables: { chatId },
  });

  // We create a subscription using the urql hook to start listening
  // to our trigger notification that will let us know when new messages are inserted
  const [resultSubscription] = useSubscription({ query: NewChatMessages, variables: { chatId, topic: `chat_messages:chat_id:${chatId}` } });

  if (resultQuery.fetching) return <p>Loading...</p>;
  if (resultQuery.error) return <p>Oh no... {resultQuery.error.message}</p>;

  var messages = []
  if (!resultSubscription.data){
    messages = resultQuery.data.chatMessages.nodes;
  }else{
    messages = resultSubscription.data.listen.query.chatMessages.nodes;
  }

  return (
    <div className="messages">
      {messages.map(message => (
        <div key={message.id}>
          <p className="message-line"> ⏱ {message.createdTs.substring(0,19).replace("T", " ")} <span className='user-id'>👤 {message.userId===0?'anonymous':`User ${message.userId}`}</span> <span className="message">🗣 {message.message}</span></p>
        </div>
      ))}
    </div>
  );
};


const Chat = () => {
    return (
      <div className="chatbox">
        <ChatBoxInput />
        <ChatBoxMessages />
      </div>
    );
}

export default Chat

Complete example available here.

Conclusion

This post shows how simple and fast the process is to get from your data model to a real-time React component using GraphQL query and subscription.

Disclaimer

I'm the maker of Agoston.io, which is a backend as a service leveraging tried-and-tested open-source solutions. It allows beginner and junior developers to leverage the power of GraphQL without having to deal with its complexity.

Agoston includes a single GraphQL endpoint, self-hosting, unified authentication, subscription, dedicated Postgres, custom code, Stripe webhooks, job scheduling, and triggers.

Full stack example using React, urql and GraphQL API - Part 1

Nicolas Penot — Thu, 20 Oct 2022 06:50:13 +0000

Want a real-time full-stack application online in less than a day? This post describes the steps that let any (juniors, frontend) developers spawn their backend (with Agoston.io), set the minimum configuration, and start implementing authentication, GraphQL requests, and subscription.

1. Spawn your backend

Create an account on Agoston.io. On the main page, create a new (free) backend:

After a few minutes, you have a complete and secure backend with Postgres, Postgraphile, etc.

2. Enable Google Authentication

Click on the backend name. That will lead you to the backend configuration page. On the bottom part, you will find the authentication configuration.

Enable the Google authentication and input your Client ID and Client secret (you can get one following the Google instructions here).

Callback URL: Google will ask you for the callback URL. You will find it in the configuration page, section Authentication strategies, field Callback URL.

3. Reload the backend

Necessary steps to take into account the Google authentication:

The reload last less than a minute.

4. Start a new React App

npx create-react-app agoston-example-react-urql
cd agoston-example-react-urql
npm install --save react-router-dom
npm install --save urql graphql graphql-ws
npm run start

5. Import Graphql and create the GraphQL client

// file ./index.js

// Graphql and urql import
import { createClient, Provider, defaultExchanges, subscriptionExchange } from 'urql';
import { createClient as createWSClient } from 'graphql-ws';


// GraphQL client for websocket subscription
const wsClient = createWSClient({
  fetchOptions: () => ({
    credentials: 'include',
  }),
  url: GRAPHQL_WS_ENDPOINT, // Change the endpoint here with the one of your backend
});

// GraphQL client for standard Graphql requests
const client = createClient({
  fetchOptions: () => ({
    credentials: 'include',
  }),
  url: GRAPHQL_ENDPOINT, // Change the endpoint here with the one of your backend
  exchanges: [
    ...defaultExchanges,
    subscriptionExchange({
      forwardSubscription: (operation) => ({
        subscribe: (sink) => ({
          unsubscribe: wsClient.subscribe(operation, sink),
        }),
      }),
    }),
  ],
});

//Make the GraphQL client available for the application
const root = ReactDOM.createRoot(document.getElementById('root'));
root.render(
  <Provider value={client}>
    <RouterProvider router={router} />
  </Provider>
);

Complete file on Github.

6. Add graphQL query to get the session data

// file ./routes/root.js

const AgostonSessionData = `query agostonSessionData {
    isSessionAuthenticated
    getCurrentRole
    getCurrentUserId
}`

7. Execute the query in a component and render

// Find the login link in the configuration page, section `Authentication strategies`, field `Auth. URL`.
var AGOSTON_AUTH_GOOGLE="https://<backend_uuid>.<cluster_uuid>.backend.agoston.io/auth/google-oauth20"

Find the login link in the configuration page, section `Authentication URLs`, field `Logout current session`.
var AGOSTON_AUTH_LOGOUT="https://<backend_uuid>.<cluster_uuid>.backend.agoston.io/auth/logout"

function RootWithSessionData() {

    // Execute the query
    const [resultQuery] = useQuery({
      query: AgostonSessionData
    });

    if (resultQuery.fetching) return <p>Loading...</p>;
    if (resultQuery.error) return (
          <div>
            <p>☠ App fails to load | ❌ {resultQuery.error.message}</p>
          </div>
          );

    var sessionData = resultQuery.data;

    return (
        <div >
            <p className={sessionData.isSessionAuthenticated?'banner d-none':'banner'}>🔥 Authenticate yourself <a href={AGOSTON_AUTH_GOOGLE}>here</a>.</p>
            <p className={sessionData.isSessionAuthenticated?'banner':'banner d-none'}>
                🔥 Authenticated as <span className="user-id">user {sessionData.getCurrentUserId}</span> Logout <a href={AGOSTON_AUTH_LOGOUT}>here</a>.
            </p>
        </div>
      );
  };


const Root = () => {
    return (
      <div>
        <RootWithSessionData />
      </div>
    );
}

Complete file on Github.

Conclusion

You now have your application's foundation, including your user's authentication.

The next step will be to create your data model and implement GraphQL requests (query, mutation, subscription).

Disclaimer

I'm the maker of Agoston.io which is a backend as a service leveraging tried-and-tested open-source solutions. Agoston includes: a single GraphQL endpoint, self-hosting, unified authentication, subscription, dedicated Postgres, custom code, Stripe webhooks, job scheduling, and triggers.

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 :)

Use relational database as document database: get the best of both worlds

Nicolas Penot — Thu, 23 Sep 2021 12:48:33 +0000

Problem with document database: can rapidly become a mess, it's difficult to rearrange and can consume a lot of resources when scanning.

Problem with relational database: schemas (tables/columns) are too static and must be maintained on the database side and code side.

Solution: A database that internally stores documents in tables by breaking down documents' attributes into rights tables' fields "on the fly".

Benefits of the solution:

Schemaless structures maintain in the code
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

Wanted: Feedbacks!

How that would help you overcome the common issues working with SQL or Document databases?
Any suggestions?

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 :)

Should we use databases as pure data storage without application logic?

Nicolas Penot — Tue, 11 May 2021 06:37:20 +0000

Database with application logic: includes views, triggers, procedures, etc

Database without application logic: tables/collections only

Personally, even if it can offload development time to the database, having application logic in the database has a lot of issues: difficult to maintain, difficult to version, having the business logic split into multiple parts of the application infrastructure.

When you keep all your application logic in the application code, everything is under control and easier to maintain over time.

Isn't it?

As a developer, are you going to use Rocky Linux as soon as possible?

Nicolas Penot — Thu, 06 May 2021 06:47:58 +0000

Because I'm wondering how urgent is it to have our app ported on Rocky Linux soon.

Should SQL Query through REST API be a GET or a POST?

Nicolas Penot — Mon, 03 May 2021 06:32:35 +0000

Query example:

select users.username, users.email, orders.id, orders.date, orders.amount
from users, orders 
where users.trid = orders.user_trid 
and users.user_trid in ( 31, 32, 33 ) 
and users.status = 1

Option 1: GET - Query in the URL

https://server.com/query?q=select%20users.username%2C%20users.email%2C%20orders.id%2C%20orders.date%2C%20orders.amount%20from%20users%2C%20orders%20where%20users.trid%20%3D%20orders.user_trid%20and%20users.user_trid%20in%20%28%2031%2C%2032%2C%2033%20%29%20and%20users.status%20%3D%201

Option 2: POST - JSON Payload

[{
    "query": "select users.username, users.email, orders.id, orders.date, orders.amount from users, orders where users.trid = orders.user_trid and users.user_trid in ( 31, 32, 33 ) and users.status = 1"
}]

Return

{
    "users": [{
        "username": "usr_name_1",
        "email": "usr_name_1@gmail.com",
        "orders": [{
            "id": 2543,
            "date": "2020.08.11",
            "amount": 35.99
        }, {
            "id": 2843,
            "date": "2020.10.01",
            "amount": 45.99
        }, {
            "id": 3843,
            "date": "2020.12.23",
            "amount": 395.99
        }]
    }, {
        "username": "usr_name_2",
        "email": "usr_name_2@gmail.com",
        "orders": [{
            "id": 3815,
            "date": "2021.01.11",
            "amount": 35.99
        }, {
            "id": 3176,
            "date": "2021.02.03",
            "amount": 45.99
        }, {
            "id": 3898,
            "date": "2020.03.05",
            "amount": 395.99
        }]
    }]
}

Original issue: https://github.com/siodb/siodb/issues/133

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: