DEV Community: Julien

Calendar events and emails as a SQL database using Nylas

Julien — Tue, 27 Aug 2024 13:30:00 +0000

This is a submission for the Nylas Challenge: Galaxy Brain.

What I Built and Why

You probably know SQL for relational databases. But what if you could use it to manage your emails and calendar events? That's what I wanted to explore.
It's a bit strange, but actually, it's fun and useful.

Anyquery is a SQL query engine. I created a plugin for it with Go to make it compatible with Nylas, allowing anyone to manage their emails and events with SQL.

My main motivation was bulk editing. Let's say I was wrong on the location of a recurrent event. I would need to modify each event location. Using SQL, I can just do

UPDATE nylas_events 
SET location = 'Eiffel tower` 
WHERE title = 'Dentist';

Moreover, imagine that I have to send bulk emails to my customers. I can just do

INSERT INTO nylas_emails("to", body, subject) 
SELECT email, 'New version 0.3.1', 'Discover the update' 
FROM my_customers;

Or export my 10 next upcoming events to JSON

anyquery -q "SELECT * FROM nylas_events WHERE start_at >= date()" --json

SQL is a very powerful language and this plugin opens a new world of possibility.

Demo

I have recorded a little demo where I show you:

How to query your calendar events
How to add an event using an INSERT INTO statement
How to update an event using an UPDATE statement
How to delete an event using a DELETE statement
How to list your unread emails using a SELECT statement
How to send an email using an INSERT INTO statement
How to trash emails using a DELETE statement

I'm using TablePlus as the SQL IDE, and Anyquery in MySQL mode to run the plugin I made.

Code

As plugins are required to be in the main repository of anyquery, the plugin I built is available in the plugins/nylas directory, and with an MIT License
https://github.com/julien040/anyquery/tree/main/plugins/nylas

You can install it by running anyquery install nylas. You'll need anyquery installed, and a Nylas sandbox account set up with a grant ID and an API key.

My Journey

The Nylas APIs were of great help to query and modify data from different calendar/email providers. I'm particularly proud of the seamless integration with the SQL query engine.
While this project is a submission for the hackathon, I'm pretty sure I'll use it weekly, and others might too. Managing productivity tools with a declarative language is an efficient and effective approach.

Apple notes is my CMS

Julien — Mon, 12 Aug 2024 22:00:00 +0000

Introduction

You may have already come across this meme and the superiority of Apple Notes.

Well, what if you could use it as a CMS to manage the content of your blog? That’s what I wanted to try for my « Today I learned » website. Here is the end result at https://til.julienc.me

Querying Apple Notes

We need a way to fetch the notes from Apple Notes. To do so, we’ll use Anyquery, it’s a SQL database that can query almost anything, including Apple Notes.

Install Anyquery at https://anyquery.dev/docs/#installation
Install the Apple Notes plugin: anyquery install notes

Query our notes using SQL and save it to JSON (in my case, my notes are in the folder TIL)

anyquery -q "SELECT name, html_body, modification_date 
FROM notes_items WHERE folder = 'TIL';" --json > notes.json

You now have a file notes.json which contains all your notes in an array of objects. Each object has three properties:

The name of the note (name)
Its last modified time (modification_date)
The body note in HTML (html_body)

For example:

[
    {
        "name": "Example",
        "modification_date": "2024-08-11T00:00:00Z",
        "html_body": "<h1>Example</h1><p>This is an example</p>"
    }
]

Our last task is to connect the website to it

Connecting the website

Personally, I’m using Astro.JS. Our first task will be to generate the static path for each entry.
To do so, I can just do import notes from "../../notes.json"; and pass it to export function getStaticPaths(). I’m also using a slugify function to ensure the generated URLs are valid.

// [...blog].astro
import notes from "../../notes.json";

function slugify(string: string) {
    return string
        .toLowerCase()
        .replace(/\s+/g, "-")
        .replace(/[^a-z0-9-]/g, "");
}

export function getStaticPaths() {
    return notes.map((note) => {
        return {
            params: {
                blog: slugify(note.name),
            },
        };
    });
}

const { blog } = Astro.params;
const note = notes.find((note) => slugify(note.name) === blog);

Once paths are generated, we need to write a little bit of CSS to match the Apple Notes style:

article.notes {
            color: #454545;
            font-size: 0.9rem;
            font-style: normal;
            font-weight: 400;
            line-height: normal;
            letter-spacing: -0.015rem;
        }

article.notes > div:first-child > h1 {
        color: #de9807;
        margin-bottom: 0.5rem;
}

... truncated (retrieve the full CSS in the repository at src/styles.css)

We are now done !

Conclusion

Congratulations, you're now using Apple Notes as a CMS. It's a powerful, collaborative CMS that is just bound to your iCloud storage limits. You can add images, tables, formatted text, code, etc.
Here is an example of the formatting options:
https://til.julienc.me/example-of-capabilities

You can deploy your own blog from Apple Notes to Vercel by doing the following:

Clone the repository git clone https://github.com/julien040/apple-notes-cms
Run npm install or pnpm install
Run chmod u+x deploy.sh
Run vercel to init and connect the project
Run ./deploy.sh to build and push the project to Vercel

Links

Source code: https://github.com/julien040/apple-notes-cms
Result: https://til.julienc.me/

Lotir - Share link and images between your phone and your computer

Julien — Sun, 09 Jan 2022 00:05:03 +0000

Overview of My Submission

I've always found painful to use email to share things to my phone. But I've never found any solutions solving this problem efficiently. They would either be too complex or not designed as first for this use case.
So I built this app by myself.

Submission Category:

Action Star
This app is event-driven. Each time something is shared from desktop, a notification is sent to phone thanks to Realm Trigger

Link to Code

julien040 / lotir

Lotir is an app to share text and images between your devices

Lotir

What is Lotir ?

I've created this app because I couldn't support sending myself an email to share something on my phone.

Lotir is a simple app that allows you to share images and text between your phone and your computer.

It's free
No ads
Simple to use
Share text and images
Temporary : Lotir keep your stuff for 7 days

How to use it ?

To use the app, you need to install it on your phone. Download it from Github Release. To access the computer app, just go to lotir.ml and set a sync key.

If you want to know more about this project, it has been built for the MongoDB Atlas hackathon. Check the announcement post here.

Development

Project structure

This repository is monorepo. It contains the following sub-projects:

mobile - Mobile apps (Expo)
desktop - Desktop apps (Neutralino)
website - Web app (Next.js)
…

View on GitHub

Additional Resources / Info

What's the use case of the app ? 🤔

Lotir permites an easy share of text and image between your phone and your computer. It takes less than 30 seconds to share. And it's bi-directionnal. You can share from your computer or your phone.
Lotir can be useful in these situations :

Imagine you receive a long OTP Code on your phone. Use Lotir to send it to your computer.
You have a very long url that you should send to Whatsapp. Use Lotir to send it to your phone.
You have a photo on your phone, and you need it on your computer. Send it over Lotir.

Also, everything is temporary. Thanks to MongoDB TTL indexes, each thing shared stays on the server for 7 days.

Features

Share any text from and to your phone/computer
Share any image from and to your phone/computer
Receive a notification when a new share happened
Data is safe and temporary. I use a MongoDB Database hosted on Atlas. Data is replicated using a replica-set in three availability zones and can't stay for more than 7 days.

How to use ?

Lotir does not have the concept of registration/login. Instead, it uses a sync key. Consider it as a secret key, it authenticates you to the server. Keep it hidden. To use Lotir, just put the same key in all your devices.

When you done that, you're ready to use Lotir

Download app

As you saw, Lotir is composed of two parts : a computer app and a phone app.

Computer

Lotir.ml is a web app that can handle all computer functionalities. However, if you prefer desktop apps, you can download them from github release.

Phone

Currently, iOS is not yet available.
To use Lotir on your phone, install its APK from github release.

Google Play protect may show several warnings about the security of this app. You can skip them.

Architecture 🗺

Clients (blue on diagram)

Clients are mobile apps, desktop apps and web apps. They identify themselves using the sync key specified in settings. Using Realm SDK (web and React Native), they interact with MongoDB Realm Functions.

Web app is built using NextJS and Realm Web SDK. UI is handled by chakra UI. I've added a custom theme on top of it.
Mobile apps are built using Expo and NativeBase. Notification service is also handled by Expo. Apps are then built using EAS Build.
Desktop apps are just a webview around Lotir.ml created with Neutralino. These are lighter than Electron.

Realm Function (purple on diagram)

Those are the main interface between client and backend. There is 5 of them :

addMessage : used to share text
addImage : used to share image
setToken : used to register notification token for mobile app
getMessages : retrieve shared text and images for clients
notifMobile : a function that responds to Realm trigger to create a new notifications

When an image needs to be shared, it is first upload to imgBB by the addImage function and link is then saved using addMessage function

Database trigger (yellow on diagram)

Each time something is shared, a new document is created in MongoDB. Creation of a new document triggers the notifMobile function. If it is detected that the message comes from a computer, the function sends a notification to the phone.

MongoDB Atlas database (green on diagram)

This is the central piece of the architecture. The MongoDB database hosted on MongoDB Atlas saves and retrieves all data for Realm Functions. Each share is saved in a collection with 5 attributes :

platform : mobile or computer
id : the sync key
title : self explanatory
body : self explanatory
time : timestamp of when the share was created

Then, a MongoDB TTL index ensures that shares don't stay more than 7 days on the server.

MongoDB Charts

To check some stats about the app, I've set up a small dashboard using MongoDB Charts. I can see how many different users there are, the repartition per platform and how many shares have been made in a week.
Link to dashboard

MongoDB Realm Hosting

Lotir.ml is hosted using MongoDB Realm Hosting

Open Source projects used

Expo : thanks to a recent update, Realm SDK could be used along Expo
Realm SDK (web and React Native) : those SDKs greatly simplified development and permits me to avoid using http endpoints.
react-native-async-storage : used to save data in Mobile app
Native base : Great UI library for React Native
react-navigation : Router for React Native
React for NextJs and React Native
NextJS : the site isn't optimized but still run fast thanks to NextJS
Chakra UI : Equivalent of Native base but for React and Vue.
Icons 8 : Icons come from this awesome website
Neutralino : Embed the web app in executables
Realm-cli : I could easily push and pull config of Realm Project thanks to CLI.

Demo

To record my phone screen, I use Your Phone from Microsoft

These videos show two situations :

Transfering text to phone
Transfering image to phone