Drizzle ORM, SQLite and Nuxt JS - Getting Started

Aaron K Saunders - Aug 7 '23 - - Dev Community

The Series

  1. Drizzle ORM, SQLite and Nuxt JS - Getting Started
  2. Drizzle ORM SQLite and Nuxt - Integrating Nuxt Auth, Part 1
  3. Drizzle ORM SQLite and Nuxt - Integrating Nuxt Auth, Part 2

Overview

This is a companion post to support a YouTube video tutorial that will walk you through integrating Drizzle ORM into a Nuxt JS project and creating the server API routes for CRUD actions on a user object. The routes are tested using Thunder Client, a lightweight REST API Client Extension for Visual Studio Code; similar to Postman.

Drizzle is an Object Relational Mapper (ORM) for SQL databases. It is a lightweight and easy-to-use ORM that can map data between a relational database and an object-oriented programming language.

Better SQLite is a wrapper around the SQLite database engine that provides a number of improvements over the standard SQLite API. One of those benefits is type safety, Better SQLite uses TypeScript to provide type safety for queries, which can help to prevent errors.

Drizzle ORM Sqlite

Video

The next video will integrate the API routes into the app, introduce relations and put a clean UI on app using Tailwind

Setup Nuxt app 



npx nuxi@latest init nuxt-drizzle-app
Enter fullscreen mode Exit fullscreen mode

Install drizzle packages and sqlite ORM package better-sqlite3 



cd nuxt-drizzle-app
npm i drizzle-orm better-sqlite3
Enter fullscreen mode Exit fullscreen mode

Install drizzle kit for migrations and for Drizzle Studio 



npm i --save-dev drizzle-kit @types/better-sqlite3
Enter fullscreen mode Exit fullscreen mode

Create configuration file in project root for Drizzle ./drizzle.config.ts

See - Configuration files – DrizzleORM



import type { Config } from "drizzle-kit";

export default {
  schema: "./db/schema.ts",
  out: "./drizzle",
  driver: "better-sqlite",
  dbCredentials: {
    url: "./sqlite.db",
  },
} satisfies Config;
Enter fullscreen mode Exit fullscreen mode

Create basic schema file in ./db/schema.ts

See - SQL schema declaration – DrizzleORM



import { InferModel, sql } from "drizzle-orm";
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey(),
  firstName: text("first_name"),
  lastName: text("last_name"),
  age: integer("age"),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`),
});

export type User = InferModel<typeof users>;
export type InsertUser = InferModel<typeof users, "insert">;
Enter fullscreen mode Exit fullscreen mode

Above we have created a table and defined the types for the table that we can use in our application. Make sure you export the information that you will need in your application

Drizzle Migration

Next we need to generate migration files which have the sql scripts information to create the tables in the new database.



npm exec drizzle-kit generate:sqlite --out migrations --schema db/schema.ts
Enter fullscreen mode Exit fullscreen mode

Drizzle Push

Next we need to push the information from migration file created to the database. You can also just run the push command directly.

I believe there is a mechanism to allow for the migrations and push to happen automatically when you make changes to the schema file. I did not cover that in this first video and need to do some additional research on the topic



npm exec drizzle-kit push:sqlite
Enter fullscreen mode Exit fullscreen mode

Drizzle Studio

We can now start Drizzle Studio to see the database and add a dummy record for us to query. Drizzle Studio is a graphical UI for you to view and manipulate your database



npm exec drizzle-kit studio
Enter fullscreen mode Exit fullscreen mode

Integrating With Nuxt

Creating a Service to Interact With Database

create a service in the nuxt application ./server/api/sqlite-service-ts



import { drizzle, BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

export const sqlite = new Database('sqlite.db');
export const db: BetterSQLite3Database = drizzle(sqlite);
Enter fullscreen mode Exit fullscreen mode

We are going to create the API routes for the application and they will support the CRUD actions on the user object.



GET - http://localhost:3000/api/users
GET - http://localhost:3000/api/users/[id]
PUT - http://localhost:3000/api/users/[id]
POST - http://localhost:3000/api/users
DELETE - http://localhost:3000/api/users/[id]
Enter fullscreen mode Exit fullscreen mode

This is what your directory in your project will look like after all of the routes are added

Image description

Get All Users

Create a new server api route ./server/api/users.get.ts



import { users } from "../../db/schema";
import { db } from "../sqlite-service";

export default defineEventHandler(async () => {
  try {
    const usersResp = db.select().from(users).all();
    return { "users" : usersResp}
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Add User

Create a new server api route ./server/api/users.post.ts, notice the user of the http method post in the name to ensures that this route will only with with that method.

This is a bit different from the get user because here we need the body of the http request to get the information needed to add the user to the database



import { users, InsertUser } from "@/db/schema";
import { db } from "@/server/sqlite-service";

export default defineEventHandler(async (event) => {
  try {
    const body = await readBody(event);
    const newUser: InsertUser = {
      ...body
    }
    const result = db.insert(users).values(newUser).run();
    return { newUser : result}
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Get User By User Id

Create a new server api route ./server/api/users/[id].get.ts, notice the use of the http method get in the name to ensures that this route will only with with that method.

Also we are using a [id] in the file name, this lets us know that the id parameter will be available in the function



import { users } from "../../../db/schema";
import { db } from "../../sqlite-service";
import { eq } from "drizzle-orm";
export default defineEventHandler(async (event) => {
  try {
    // get id as function parameter from route params
    const userId = event.context.params?.id as string;
    const usersResp = db
      .select()
      .from(users)
      .where(eq(users.id, parseInt(userId)))
      .get();
    return { user: usersResp };
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

Delete User By User Id

Create a new server api route ./server/api/users/[id].delete.ts, notice the use of the http method delete in the name to ensures that this route will only with with that method.

Also we are using a [id] in the file name, this lets us know that the id parameter will be available in the function



import { users } from "../../../db/schema";
import { db } from "../../sqlite-service";
import { eq } from "drizzle-orm";
export default defineEventHandler(async (event) => {
  try {
    const userId = event.context.params?.id as string;
    const usersResp = db
      .delete(users)
      .where(eq(users.id, parseInt(userId)))
      .run();
    return { user: usersResp };
  } catch (e: any) {
    throw createError({
      statusCode: 400,
      statusMessage: e.message,
    });
  }
});
Enter fullscreen mode Exit fullscreen mode

In this route we introduced the use of where and eq to help us find the proper user to delete.

Update User By User Id

Create a new server api route ./server/api/users/[id].put.ts, notice the use of the http method delete in the name to ensures that this route will only with with that method.

Also we are using a [id] in the file name, this lets us know that the id parameter will be available in the function

And this method will also be passed a body in the http request to get the information needed to update the user to the database.




import { InsertUser, users } from "../../../db/schema";

import { db } from "../../sqlite-service";

import { eq } from "drizzle-orm";

export default defineEventHandler(async (event) => {

  try {

    const userId = event.context.params?.id as string;

    const body = await readBody(event);

    const editUser: InsertUser = {

      ...body

    }

    const usersResp = db

      .update(users)

      .set(editUser)

      .where(eq(users.id, parseInt(userId)))

      .run();

    return { user: usersResp };

  } catch (e: any) {

    throw createError({

      statusCode: 400,

      statusMessage: e.message,

    });

  }

});
Enter fullscreen mode Exit fullscreen mode




Testing

In this first part of the video series I did all of my testing using the Thunder Client for VS Code, It has the same functionality of any Postman like REST API tool.

The next part in the video series will cover integrating the Nuxt Auth using Drizzle ORM with SQLite.

Links

Social Media

Image
The 10 Most Scariest Things About Asbestos Lung Mesothelioma
Image
Kajian Penyeleksian Situs Judi Online Bisa dipercaya
Image
The Bitcoin Resurgence: My Unforgettable Experience with Pro Wizard Gilbert Recover
Image
10 Things Your Competitors Lean You On Drip Pot Coffee Maker
Image
How Asbestos Lawsuit Settlements Has Become The Most Sought-After Trend Of 2023
Image
Office cleaning involves keeping a clean, safe, and organized
Image
What Is a Built Oven Single?
Image
One Of The Most Untrue Advices We've Ever Been Given About Repair Bifold Door Bottom Pivot
Image
Why You Should Focus On Improving Accidents Lawyer
Image
10 Things Everyone Hates About Best Accident Lawyer Near Me
Image
trik awasi modal ketika bermain slots online
Image
🌱 New Portfolio Website Live, Would Love Your Feedback!

portfolio, showcase, nextjs, tailwindcss
Image
20 Important Questions To ASK ABOUT Bifold Repairs Near Me Prior To Purchasing Bifold Repairs Near Me
Image
Buzzwords De-Buzzed: 10 Different Methods Of Saying Best Wall Mounted Electric Fires
Image
Pay Attention: Watch Out For How Lawyers For Accident Is Taking Over And How To Stop It
Image
Cat Flap Cover For Winter
Image
10 Myths Your Boss Is Spreading About Asbestos Claim Payouts
Image
Kanapy Marzeń: Jak Wybrać Idealne Miejsce do Wypoczynku?
Image
What is caluanie muelear used for?

caluanie, caluaniemuelearoxidize, caluaniemuelearoxidizeprice, caluaniemuelearoxidizeforsale
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .