Welcome to the new DB series on the Foundry. This is part of a collection surrounding Nuxt 3 and compatible databases and ORM's.
Before we start, I recommend you to setup your Turso DB beforehand and follow their guide to get your database tokens, it's pretty easy. You’ll need the token and database url in your .env file later. Setup your Turso DB
You can grab the complete code on github if you prefer that.
- Open up a terminal and create a new Nuxt 3 project using the cli:
pnpm dlx nuxi@latest init <your-project-name>
- Install the following packages:
pnpm add drizzle-orm @libsql/client drizzle-kit
// and
pnpm add -D @nuxtjs/tailwindcss
- Create a .env variable file with the following content:
TURSO_DB_URL=
TURSO_DB_AUTH_TOKEN=
- Before we start on the frontend, we will first configure the back-end and the Turso database. We can do this by first going to the root of your project and creating a
serverfolder and inside it create autilsand adatabasefolder. 5.** Inside theutilsfolder, create a new file and name itdb.tswith the following code:**
import { drizzle } from "drizzle-orm/libsql";
import { createClient } from "@libsql/client";
// This is bare minimum setup
export const database = createClient({
url: process.env.DATABASE_URL!,
authToken: process.env.DATABASE_AUTH_TOKEN!,
});
export const orm = drizzle(database);
6.** Inside the database folder, create a file called schema.ts with the following code:**
import { InferModel } from 'drizzle-orm'
import { sqliteTable, text, integer, unique } from 'drizzle-orm/sqlite-core'
export const tickets = sqliteTable('messages', {
id: integer('id').primaryKey(),
name: text('name').notNull(),
email: text('email').notNull(),
body: text('body').notNull()
})
// InferModel from drizzle-orm is deprecated, replace asap for Production!
export type Ticket = InferModel<typeof tickets>;
export type InsertTicket = InferModel<typeof tickets, "insert">;
- In the
serverfolder create a new folder calledroutesandapi. Everything we put in theroutesfolder will be accessible in the browser via your development or production url. Theapifolder works a bit different because you need to append/api/to your url in order to reach them. You can learn more by reading the documentation provided at Nuxt Server. Note: Nitro routes look a lot like Pages but it does not offer the same functionality, keep this in mind. - Nuxt scans multiple folders including the content of the
apifolder, create a new folder calledticketsinside of theapifolder. Nuxt will auto-append it to/api/as/api/tickets. Here are some basic examples of how it works under the hood.
-| server/
---| api/
-----| tickets/
--------| index.ts # /api/tickets, index acts as a default
--------| create.ts # /api/tickets/create
--------| update.post.ts # /api/tickets/update, post requests only
---| routes/
-----| hello.ts # /hello
-----| something/
--------| index.ts # /something, index acts as a default
--------| somewhere.ts # /something/somewhere
BONUS:
---| middleware/
-----| log.ts # log each and every incoming request
- To talk with our Turso SQL database we are going to use Drizzle as ORM. Create a new file called
index.get.tsin your./server/api/tickets/folder with the following content:`
import { tickets } from "../../database/schema";
import { orm } from "../../utils/db";
export default defineEventHandler(async () => {
try {
const allTickets = orm.select().from(tickets).all();
return { "tickets" : allTickets}
} catch (e: any) {
throw createError({
statusCode: 400,
statusMessage: e.message,
});
}
});
- Create another file called
createTicket.post.tsin your./server/api/tickets/folder with the following content:
import { tickets, InsertTicket } from "../../database/schema";
import { orm } from "../../utils/db";
export default defineEventHandler(async (event) => {
try {
const body = await readBody(event);
const newTicket: InsertTicket = {
...body
}
const result = orm.insert(tickets).values(newTicket).run();
return { newTicket : result}
} catch (e: any) {
throw createError({
statusCode: 400,
statusMessage: e.message,
});
}
})
- BONUS: Basic Delete by ID, create a folder inside server/api/ickets called deleteById and create [...id].ts inside that with the following content.
import { tickets } from "../../../database/schema";
import { orm } from "../../../utils/db";
import { eq } from "drizzle-orm";
export default defineEventHandler(async (event) => {
try {
const ticketId = event.context.params?.id as string;
if(!ticketId) throw createError({
statusCode: 400,
statusMessage: "Ticket ID is required",
});
const allTickets = orm
.delete(tickets)
.where(eq(tickets.id, parseInt(ticketId )))
.run();
return { tickets: allTickets };
/* or
return {
statusCode: 200,
statusMessage: "Success",
}
*/
} catch (e: any) {
throw createError({
statusCode: 400,
statusMessage: e.message,
});
}
});
- Now we finished up our Nitro back-end, let’s deploy the database to Turso and push the schema so the SQL tables get created. Create a new file in the root of your project called
drizzle.config.tswith the following code:
import type { Config } from "drizzle-kit";
export default {
schema: "./server/utils/db/schema.ts",
driver: "turso",
dbCredentials: {
url: process.env.TURSO_DB_URL as string,
authToken: process.env.TURSO_DB_AUTH_TOKEN as string,
},
} satisfies Config;
- Push your schema to Turso by using the following command in a seperate terminal.
pnpm drizzle-kit push:sqlite --config=drizzle.config.ts
- Time to work on the frontend, edit your
app.vuefile in the root of your project and change it into:
<template>
<div>
<NuxtPage />
</div>
</template>
We will not be using dynamic layouts in this article so with the above, we only tell Nuxt to slot in the pages scanned from the ./pages folder.
15. Create ./pages/index.vue and add the following template code:
<script setup lang="ts">
const errorMessage = ref<string | null>(null);
export interface Ticket {
name: string,
email: string,
subject: string,
description: string,
}
const handleSubmit = async (e: Event) => {
if (!(e.target instanceof HTMLFormElement)) return;
errorMessage.value = null;
const formData = new FormData(e.target);
try {
await $fetch("/api/tickets/create", {
method: "POST",
body: <Ticket>{
name: formData.get("name"),
email: formData.get("email"),
subject: formData.get("subject"),
description: formData.get("description"),
}
});
} catch (e) {
const { data: error } = e as {
data: {
message: string;
};
};
errorMessage.value = error.message;
}
};
</script>
<template>
<section class="m-auto px-6 mt-10">
<div class="max-w-4xl p-6 mx-auto bg-white rounded-md shadow-md dark:bg-gray-800">
<div class="flex justify-between">
<h2 class="text-lg font-semibold text-gray-700 capitalize dark:text-white">Create Ticket</h2>
<NuxtLink to="/tickets"
class="flex items-center px-5 py-2 text-sm text-gray-700 capitalize transition-colors duration-200 bg-white border rounded-md gap-x-2 hover:bg-gray-100 dark:bg-gray-900 dark:text-gray-200 dark:border-gray-700 dark:hover:bg-gray-800">
View all Tickets
</NuxtLink>
</div>
<form @submit.prevent="handleSubmit">
<div class="w-full grid grid-cols-2 gap-6 mt-4">
<div class="col-span-2 md:col-span-1">
<label class="text-gray-700 dark:text-gray-200" for="username">Name</label>
<input id="username" type="text" name="name"
class="block w-full px-4 py-2 mt-2 text-gray-700 bg-white border border-gray-200 rounded-md dark:bg-gray-800 dark:text-gray-300 dark:border-gray-600 focus:border-blue-400 focus:ring-blue-300 focus:ring-opacity-40 dark:focus:border-blue-300 focus:outline-none focus:ring">
</div>
<div class="col-span-2 md:col-span-1">
<label class="text-gray-700 dark:text-gray-200" for="emailAddress">Email Address</label>
<input id="emailAddress" type="email" name="email"
class="block w-full px-4 py-2 mt-2 text-gray-700 bg-white border border-gray-200 rounded-md dark:bg-gray-800 dark:text-gray-300 dark:border-gray-600 focus:border-blue-400 focus:ring-blue-300 focus:ring-opacity-40 dark:focus:border-blue-300 focus:outline-none focus:ring">
</div>
<div class="col-span-2">
<label class="text-gray-700 dark:text-gray-200" for="subject">Subject</label>
<input id="subject" type="text" name="subject"
class="block w-full px-4 py-2 mt-2 text-gray-700 bg-white border border-gray-200 rounded-md dark:bg-gray-800 dark:text-gray-300 dark:border-gray-600 focus:border-blue-400 focus:ring-blue-300 focus:ring-opacity-40 dark:focus:border-blue-300 focus:outline-none focus:ring">
</div>
<div class="col-span-2">
<label class="text-gray-700 dark:text-gray-200" for="description">Description</label>
<textarea id="description" type="description" name="description"
class="block w-full px-4 py-2 mt-2 text-gray-700 bg-white border border-gray-200 rounded-md dark:bg-gray-800 dark:text-gray-300 dark:border-gray-600 focus:border-blue-400 focus:ring-blue-300 focus:ring-opacity-40 dark:focus:border-blue-300 focus:outline-none focus:ring"></textarea>
</div>
<div>
<p class="error">{{ errorMessage }}</p>
</div>
</div>
<div class="flex justify-end mt-6">
<button
class="px-8 py-2.5 leading-5 text-white transition-colors duration-300 transform bg-gray-700 rounded-md hover:bg-gray-600 focus:outline-none focus:bg-gray-600">Save</button>
</div>
</form>
<a href="https://merakiui.com/components/application-ui/forms">Component from Meraki UI, check their awesome
components. </a>
</div>
</section>
</template>
The above template offers a simple card component with a form to post new Tickets to the backend. You can see that we are sending a request to /api/tickets/create.
- Create
./pages/tickets.vueand add the following template code:
<script setup lang="ts">
const { data: tickets, pending, error, status, refresh } = await useFetch("/api/tickets", {
method: "GET",
immediate: false, // Don't fire automagically
server: false, // Don't auto-fire automagically serverside
});
</script>
<template>
<section class="container px-4 mt-10 mx-auto">
<div class="flex justify-between">
<h2 class="text-lg font-medium text-gray-800 dark:text-white">Tickets</h2>
<div class="flex items-center gap-x-4 justify-between mt-6">
<NuxtLink
to="/"
class="flex items-center px-5 py-2 text-sm text-gray-700 capitalize transition-colors duration-200 bg-white border rounded-md gap-x-2 hover:bg-gray-100 dark:bg-gray-900 dark:text-gray-200 dark:border-gray-700 dark:hover:bg-gray-800">
Create Ticket
</NuxtLink>
<button v-if="pending == false" @click="refresh()"
class="flex items-center px-5 py-2 text-sm text-gray-700 capitalize transition-colors duration-200 bg-white border rounded-md gap-x-2 hover:bg-gray-100 dark:bg-gray-900 dark:text-gray-200 dark:border-gray-700 dark:hover:bg-gray-800">
<span>
Refresh
</span>
<svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5"
stroke="currentColor" class="w-5 h-5 rtl:-scale-x-100">
<path stroke-linecap="round" stroke-linejoin="round"
d="M17.25 8.25L21 12m0 0l-3.75 3.75M21 12H3" />
</svg>
</button>
</div>
</div>
<div class="flex flex-col mt-6">
<div class="-mx-4 -my-2 overflow-x-auto sm:-mx-6 lg:-mx-8">
<div class="inline-block min-w-full py-2 align-middle md:px-6 lg:px-8">
<div class="overflow-hidden border border-gray-200 dark:border-gray-700 md:rounded-lg">
<table class="min-w-full divide-y divide-gray-200 dark:divide-gray-700">
<thead class="bg-gray-50 dark:bg-gray-800">
<tr>
<th scope="col"
class="py-3.5 px-4 text-sm font-normal text-left rtl:text-right text-gray-500 dark:text-gray-400">
<button class="flex items-center gap-x-3 focus:outline-none">
<span>Name</span>
<svg class="h-3" viewBox="0 0 10 11" fill="none"
xmlns="http://www.w3.org/2000/svg">
<path
d="M2.13347 0.0999756H2.98516L5.01902 4.79058H3.86226L3.45549 3.79907H1.63772L1.24366 4.79058H0.0996094L2.13347 0.0999756ZM2.54025 1.46012L1.96822 2.92196H3.11227L2.54025 1.46012Z"
fill="currentColor" stroke="currentColor" stroke-width="0.1" />
<path
d="M0.722656 9.60832L3.09974 6.78633H0.811638V5.87109H4.35819V6.78633L2.01925 9.60832H4.43446V10.5617H0.722656V9.60832Z"
fill="currentColor" stroke="currentColor" stroke-width="0.1" />
<path
d="M8.45558 7.25664V7.40664H8.60558H9.66065C9.72481 7.40664 9.74667 7.42274 9.75141 7.42691C9.75148 7.42808 9.75146 7.42993 9.75116 7.43262C9.75001 7.44265 9.74458 7.46304 9.72525 7.49314C9.72522 7.4932 9.72518 7.49326 9.72514 7.49332L7.86959 10.3529L7.86924 10.3534C7.83227 10.4109 7.79863 10.418 7.78568 10.418C7.77272 10.418 7.73908 10.4109 7.70211 10.3534L7.70177 10.3529L5.84621 7.49332C5.84617 7.49325 5.84612 7.49318 5.84608 7.49311C5.82677 7.46302 5.82135 7.44264 5.8202 7.43262C5.81989 7.42993 5.81987 7.42808 5.81994 7.42691C5.82469 7.42274 5.84655 7.40664 5.91071 7.40664H6.96578H7.11578V7.25664V0.633865C7.11578 0.42434 7.29014 0.249976 7.49967 0.249976H8.07169C8.28121 0.249976 8.45558 0.42434 8.45558 0.633865V7.25664Z"
fill="currentColor" stroke="currentColor" stroke-width="0.3" />
</svg>
</button>
</th>
<th scope="col"
class="px-12 py-3.5 text-sm font-normal text-left rtl:text-right text-gray-500 dark:text-gray-400">
e-mail
</th>
<th scope="col"
class="px-4 py-3.5 text-sm font-normal text-left rtl:text-right text-gray-500 dark:text-gray-400">
subject
</th>
<th scope="col"
class="px-4 py-3.5 text-sm font-normal text-left rtl:text-right text-gray-500 dark:text-gray-400">
description</th>
<th scope="col" class="relative py-3.5 px-4">
<span class="sr-only">Edit</span>
</th>
</tr>
</thead>
<tbody class="bg-white divide-y divide-gray-200 dark:divide-gray-700 dark:bg-gray-900">
<tr v-if="pending == true">
<td class="px-4 py-4 text-sm whitespace-nowrap">
<button @click="refresh()"
class="px-4 py-1 border border-gray-300 text-gray-500 transition-colors duration-200 rounded-lg dark:text-gray-300 hover:bg-gray-100">
Fetch
</button>
</td>
<td class="px-4 py-4 text-sm font-medium whitespace-nowrap">
</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr v-else-if="error">
<td class="px-4 py-4 text-sm font-medium whitespace-nowrap">
<div>
<p class="text-sm font-normal text-gray-600 dark:text-gray-400">
{{ error.message }}
</p>
</div>
</td>
</tr>
<template v-else>
<tr v-for="(ticket, i) in tickets" :key="i">
<td class="px-4 py-4 text-sm font-medium whitespace-nowrap">
<div>
<p class="font-medium text-gray-800 dark:text-white ">
{{ ticket.name }}
</p>
</div>
</td>
<td class="px-12 py-4 text-sm font-medium whitespace-nowrap">
<div
class="inline px-3 py-1 text-sm font-normal rounded-full text-emerald-500 gap-x-2 bg-emerald-100/60 dark:bg-gray-800">
{{ ticket.email }}
</div>
</td>
<td class="px-4 py-4 text-sm whitespace-nowrap">
<div>
<p class="text-gray-500 dark:text-gray-400">
{{ ticket.subject }}
</p>
</div>
</td>
<td class="px-4 py-4 text-sm whitespace-nowrap">
<p class="text-gray-500 dark:text-gray-400">
{{ ticket.description }}
</p>
</td>
<td class="px-4 py-4 text-sm whitespace-nowrap">
<button
class="px-1 py-1 text-gray-500 transition-colors duration-200 rounded-lg dark:text-gray-300 hover:bg-gray-100">
<svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24"
stroke-width="1.5" stroke="currentColor" class="w-6 h-6">
<path stroke-linecap="round" stroke-linejoin="round"
d="M12 6.75a.75.75 0 110-1.5.75.75 0 010 1.5zM12 12.75a.75.75 0 110-1.5.75.75 0 010 1.5zM12 18.75a.75.75 0 110-1.5.75.75 0 010 1.5z" />
</svg>
</button>
</td>
</tr>
</template>
</tbody>
</table>
</div>
</div>
</div>
</div>
</section>
</template>
The above template offers a simple table component that loads all available tickets from your database once you fetch for them. You can also see here that we are sending a request to /api/tickets which points to ./server/api/tickets/index.get.ts or /index.post.ts depending on your fetch request method, e.g: method: "GET" | "POST" .
It's a wrap, if you have followed my previous articles, you should be good to go from here incorporating your Turso DB with Nuxt. Otherwise here’s a link to the GitHub repo I made public alongside the article.
Improvements:
- change path in api endpoints from relative to '~'.
- change InferModel, it's deprecated, don't use it in production!
