Creating a newsletter manager with Turso and Qwik

A newsletter is an email marketing strategy tool used by individuals, publishers, organisations, businesses to share valuable information and engaging content with their customers, helping in promoting sales and driving traffic to their websites.

In this post I’m going to demonstrate how to create a newsletter using Turso, the edge database based on libSQL - the open-source and open-contribution fork of SQLite, and Qwik, the resumable, instant-on applications framework being built by the folks at builder.io.

Before getting to the building part, I’d like to share a couple of reasons why I’m working on this and using the selected technology.

Newsletter platforms are either unreliable or expensive, here’s why I rolled my own

My go-to option for my own newsletters had been Revue, but Twitter recently shut that down, and others like Ghost and ConvertKit are quite expensive for my liking, so I did some research into building my own so you don’t have to.

I have been following Qwik, a resumable, instant-on applications framework, and I love the philosophy behind it, so it was a no-brainer to build with. I paired it with Turso, a DBaaS (Database-as-a-service) that has the best DX (Developer Experience) I’ve come across — which is one of the reasons I ended up joining the company, full disclosure — for the speed and low latency it can provide alongside Qwik.

Creating the newsletter manager with Turso and Qwik

The newsletter manager we are creating has three pages.

The first page is one that subscribers interact with when subscribing, which includes a brief introduction to what the newsletter is all about, and a form.

The second, is a page accessible to the newsletter’s editor which enables them to view the subscribers emails, blogs they subscribed to(if multiple), and the subscription dates.

The third page is the unsubscribing page. Like any good newsletter, you’d want to give your subscribers both the option to opt into and opt out of it.

Ideally, the second page would involve authentication of some sort as it should warrant limited access, but we won’t be covering authentication in this post.

The source code to the project we are working on in this post is also available in this GitHub repository.

Prerequisites:

• Before proceeding, make sure that you have the latest LTS version of Node.js to build Qwik projects and use node modules.

• Sign up for a Turso account to use the Turso CLI to create databases for your projects.

Setting up the Turso database

Instructions on the installation of Turso, database creation, and getting the Turso database URL can be found on the project’s initialization instructions on the GitHub repository.

You’ll need the Turso database URL obtained after creating a database as that is needed inside our application.

The Qwik app

As discussed, and as you'll notice by observing the source code, the project is built using the Qwik framework, and we are using the libSQL's client for TypeScript (@libsql/client to perform queries on our database. With this, we can work with local SQLite file databases seamlessly just like using our Turso databases on the edge.

Something that we are going to be seeing repeatedly in Qwik code is the $ symbol. Let it not confuse you, Qwik uses it to signify the extraction of code to both the developer and Qwik optimizer. Qwik splits up the application into many smaller pieces dubbed "symbols" using the Qwik Optimizer. More on this can be read in the Qwik docs.

Let's cover the most interesting bits of the newsletter manager.

The newsletter home page

Inside the home page, we are updating the loading state and calling the subscribeToNewsletter() function when the form is submitted <form onSubmit$>.

useSignal() and useStore() are two Qwik hooks you’ll find throughout the pages used to declare reactive state. useSignal() takes an initial value and returns a reactive signal consisting of an object with a single .value property, while the useStore() hook takes an object as its initial value and returns a reactive object.

The server function in Qwik — server$ lets us work in the server layer of the application right next to our client code. Here is more information on it, and here is a demonstration.

subscribeToNewsletter() inside the home page is a server function that submits the subscription information to the Turso database and updates us on the subscription state.

import { createClient } from "@libsql/client";

export const subscribeToNewsletter = server$(
  async (email: string, newsletter: string) => {
    if (!email || !newsletter) {
      return {
        success: false,
        message: "Email cannot be empty!",
      };
    }

    const db = createClient({
      url: import.meta.env.VITE_DB_URL,
    });

    await db.execute("insert into newsletters(email, website) values(?, ?)", [
      email,
      newsletter,
    ]);

    const response = await db.execute(
      "select * from newsletters where email = ? and website = ?",
      [email, newsletterBlog]
    );

    const subscriber = responseDataAdapter(response);

    return subscriber[0]
      ? {
          success: true,
          message: "You've been subscribed!",
        }
      : {
          success: false,
          message: "Sorry something isn't right, please retry!",
        };
  }
);

export default component$(() => {
  const email = useSignal("");
  const loading = useSignal(false);
  const emailRegex = /\b[\w.-]+@[\w.-]+\.\w{2,4}\b/gi;
  const notification = useStore({
    message: "",
    status: "",
  });

  return (
      <form
        preventdefault:submit onSubmit$={async () => {
          if (!emailRegex.test(email.value)) {
            alert("Email not valid!");
            return;
          }
          loading.value = true;
          const response = await subscribeToNewsletter(
            email.value,
            newsletterBlog
          );
          loading.value = false;
          notification.message = response.message;
          notification.status = response.success ? "success" : "error";
        }}
      >
        <input
          onInput$={(e) => {
            email.value = (e.target as HTMLInputElement).value
          }}
          name="email"
          type="email"
        />
        <button
          type="submit"
        >Subscribe</button>
      </form>
  );
});

Qwik also supports SEO (Search Engine Optimisation). To set it up on pages, we export a head object of the DocumentHead type with HTML header metadata. We can see this inside each of the three pages, the home, admin, and unsubscription pages.

Here’s a preview of the home page.

The newsletter admin page

In the component UI of the newsletter admin page, we are using Qwik’s Resource component which renders its children when the passed resource is resolved, and renders a fallback when it is pending or rejected.

In Qwik, resources are created using the useResource$ method. We can see a demonstration of a resource by looking at the subscribersResource needed by the <Resource ... /> component referenced above.

const subscriberRows = (subscribers: NewsletterSubscriber[]) => {
  return subscribers?.length < 1 ? (
    <tr>
      <td
        colSpan={4}
      >
        No subscribers found
      </td>
    </tr>
  ) : (
    subscribers?.map((sub: NewsletterSubscriber, index: number) => {
      return (
        <tr key={sub.id}>
          <td>{index + 1}</td>
          <td>{sub.email}</td>
          <td>
            {sub.website}
          </td>
          <td>
            {formatDate(sub.created_at)}
          </td>
        </tr>
      );
    })
  );
};

export default component$(() => {

  const subscribersResource = useResource$<ResourceResponse>(async () => {
    const response = await db.execute('select * from newsletters');
    const subscribers = response?.success ? responseDataAdapter(response) : [];

    return {
      message: "Fetched subscribers",
      data: subscribers,
    };
  });

  return(
    <div>
      <h1>Newsletter Admin</h1>

      <Resource
        value={subscribersResource}
        onRejected={() => <Noty message="Failed to fetch subscribers" type="error" />}
        onPending={() => <LoadingAnimation/>}
        onResolved={(res: ResourceResponse) => <table>
          <thead>
          <tr>
            <th>No.</th>
            <th>Email</th>
            <th>Website</th>
            <th>Joined</th>
          </tr>
          </thead>
          <tbody>
            {subscriberRows(res.data)}
          </tbody>
        </table>
        }
      ></Resource>
    </div>
  );
});

After adding some subscribers, we should see the table populated with their information when visiting this page.

The newsletter unsubscription page

Normally unsubscription links should be accessible from within the newsletter emails. To unsubscribe users, we need to pass parameters to the unsubscription route.

Qwik’s file-based routing is similar to other frameworks, in that we pass route parameters declaring them as directory names inside square brackets.

To pass the domain and email parameters, we’ve set up the directory structure for the unsubscription page as follows.

└── routes
    ├── unsubscribe
    │   └── [email]
    │       └── [domain]
    │           └── index.tsx

We then use Qwik’s useLocation() function to retrieve a RouteLocation object for the active location from which we acquire the email and domain parameters. We use these two parameters to delete the user’s data from the database, to unsubscribe them from the newsletter inside another server function.

The whole unsubscription process is automated by making sure that unsubscribeFromNewsletter(), which is also a Qwik server$ function, is triggered when the page component is rendered on the browser. We do this by calling this function inside the useBrowserVisibleTask() hook.

import { createClient } from "@libsql/client";

export default component$(() => {
  const location = useLocation();
  const email = useSignal(location.params.email);
  const domain = useSignal(location.params.domain);
  const loading = useSignal(false);
  const notification = useStore({
    message: "",
    status: "",
  });

  const unsubscribeFromNewsletter = server$(async () => {
    const db = createClient({
      url: import.meta.env.VITE_DB_URL,
    });
    const deleteRecord = await db.execute(
      "delete from newsletters where email = ? and website like ?",
      [email.value, domain.value]
    );

    if (!deleteRecord.success) {
      return {
        success: false,
        message: "Sorry, something isn't right, please reload the page!",
      };
    }

    return {
      success: true,
      message: "Unsubscribed!",
    };
  });

  useBrowserVisibleTask$(async () => {
    loading.value = true;
    const res = await unsubscribeFromNewsletter();
    notification.message = res.message;
    notification.status = res.success ? "success" : "error";
    loading.value = false;
  });
});

Here is a demonstration of what would happen when unsubscribing users, starting inside the mail view.

That’s it for the newsletter manager. Visit the project’s repo on GitHub for the source code and to learn more about it.

To learn more about Turso click here to visit the documentation, and as for Qwik, you can read the official documentation over here.