tRpc - Move Fast and Break Nothing. End-to-end typesafe APIs made easy. Experience the full power of TypeScript inference to boost productivity for your NUXT full-stack application.

In the second video of the series, we are separating routers and adding SQLite, Better SQLite 3, Database to the application to show access to context for performing a simple query and mutation.

This blog is a companion to the video walkthrough of integrating the database into the Nuxt 3 and running with tRpc using the trpc-nuxt module and performing a simple query and mutation.

Series Video Playlist

tPRC Nuxt Module - wobsoriano/trpc-nuxt Documentation

Refactor to Separate Routes

modify server/trpc/routers/index.ts we are going to create a separate route namespace for the original test code and a new route namespace for our actual application API routes.

testRouter for test routes and thoughtRouter for our application.

// server/trpc/routers/index.ts

import { router } from "../trpc";
import { testRouter } from "./testRouter";
import { thoughtRouter } from "./thoughtRouter";

export const appRouter = router({
  testRouter,
  thoughtRouter
});

// export type definition of API
export type AppRouter = typeof appRouter;

We will then move the test route into a separate file server/trpc/routers/testRouter.ts

// server/trpc/routers/testRouter.ts

import { z } from 'zod';
import { publicProcedure, router } from '../trpc';

export const testRouter = router({
    hello: publicProcedure
    .input(
      z.object({
        text: z.string().nullish(),
      })
    )
    .query(({ input }) => {
      return {
        greeting: `hello ${input?.text ?? 'world'}`,
      };
    }),
});

And then create a new file for our routes

// server/trpc/routers/thoughtRouter.ts

import { z } from "zod";
import { publicProcedure, router } from "../trpc";

export const thoughtRouter = router({
  addThought: publicProcedure
    .input(
      z.object({
        thought: z.string(),
        mood: z
          .literal("happy")
          .or(z.literal("sad"))
          .or(z.literal("indifferent"))
          .or(z.literal("angry"))
          .or(z.literal("tired")),
      })
    )
    .mutation(({ input }) => {
      // HERE IS WHERE WE WILL SAVE TO DATABASE
      return {
        thought: {
          text : input.thought,
          mood : input.mood,
          created : new Date()
        },
      };
    }),
});

Now we have seperated the routes and they can be accessed using the namespace created. For the existing test route and any additional ones you might add, they are accessed like this.

$trpcClient.testRouter.hello({text:"some content"});

and the application routes

$trpcClient.thoughtRouter.addThought({thought:"some content", mood : "happy"});

Add BetterSQLite3 Database to Application

Thats was just some project cleanup; now we get to the new functionality.

We are going to connect a database to the application and provide access to the database client through the context provided by tRPC.

Better SQLite 3 - The fastest and simplest library for SQLite3 in Node.js. https://github.com/WiseLibs/better-sqlite3

Install the library and the associated type definitions.

npm install better-sqlite3
npm install --save @types/better-sqlite3

Make some edits to context.ts to expose database through tRPC context.

import better-sqlite3 Database class
create database instance
add code to create table if it doesn't exists
finally we return db object from the context

import { inferAsyncReturnType } from "@trpc/server";
import Database from "better-sqlite3";

/**
 * Creates context for an incoming request
 * @link <https://trpc.io/docs/context>
 */
export const createContext = () => {
  const db = new Database("./my-thoughts.db");

  const createTable = db.prepare(`
    CREATE TABLE IF NOT EXISTS thoughts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      thought TEXT NOT NULL,
      mood TEXT NOT NULL
    );
  `);

  createTable.run();

  return {
    db
  };
};

export type Context = inferAsyncReturnType<typeof createContext>;

Utilize Database From Context in ThoughtRouter

Adding code to create a new thought entry for the database. We will be making changes to /server/trpc/routers/thoughtRoughter.ts

for validation of the inputs, we create a zod schema, we use the min function to ensure a minimum length, and we use the literal function to ensure that only the specified string values are accepted.

z.object({
  thought: z.string().min(10),
  mood: z.literal("happy").or(z.literal("sad")).or(z.literal("angry")),
})

For the mutation, where we will make database call to insert the entry, first we create a type for thought. Then we create the newThought using the values from input

type Thought = {
  id?: number;
  thought: string;
  mood: "happy"| "sad"| "angry";
};

...

const newThought: Thought = {
  thought: input.thought,
  mood: input.mood,
};

finally we make the call to our database

const insertThought = ctx.db.prepare<Thought>(`
  INSERT INTO thoughts (thought, mood) VALUES 
   (:thought, :mood);
`);

const resp = insertThought.run(newThought);

return {
  success: true,
  id: resp.lastInsertRowid,
};

Full source code for changes made to thoughtRouter.ts

addThought: publicProcedure
    .input(
      z.object({
        thought: z.string().min(10),
        mood: z.literal("happy").or(z.literal("sad")).or(z.literal("angry")),
      })
    )
    .mutation(({ input, ctx }) => {
      const newThought: Thought = {
        thought: input.thought,
        mood: input.mood,
      };
      const insertThought = ctx.db.prepare<Thought>(`
      INSERT INTO thoughts (thought, mood) VALUES (:thought, :mood);
    `);

      const resp = insertThought.run(newThought);

      return {
        success: true,
        id: resp.lastInsertRowid,
      };
    }),

Test the mutation in App.vue

const { tests, thoughts } = $trpcClient;
const resp = await thoughts.addThought.mutate({
  thought: "simple though to make me happy",
  mood: "happy",
});

Create Query For All Thoughts

This query is pretty straightforward since there are no parameters. We are just using the db like we did in previous section and making the database call and returning the results.

Full source code for changes made to thoughtRouter.ts

getThoughts: publicProcedure.query(({ ctx }) => {
  const getAllThoughts = ctx.db.prepare(`
  SELECT * FROM thoughts;
`);

  const thoughts = getAllThoughts.all() as Thought[];

  return {
    thoughts,
  };
}),

Test the query in App.vue

const { tests, thoughts } = $trpcClient;
const resp = await thoughts.getThoughts.useQuery();

Social Media

Twitter - https://twitter.com/aaronksaunders
Facebook - https://www.facebook.com/ClearlyInnovativeInc
Instagram - https://www.instagram.com/aaronksaunders/
Clearly Innovative Inc - https://www.clearlyinnovative.com

Build A Full-Stack Typescript Application with Nuxt and tRPC