Database CRUD

Learn how to use the products table as a starting point for building your own database CRUD operations

This template demonstrates a complete implementation of create, read, update, and delete operations for managing database records. It showcases best practices for building data tables with pagination, search, and form handling using shadcn-vue components.

Overview

This template provides:

Full CRUD operations - Create, read, update, and delete functionality
Paginated data tables - Efficient loading with customizable page sizes
Search functionality - Real-time search across multiple fields
Form handling - Sheet and Drawer components for user input
Type-safe operations - End-to-end TypeScript support
Prisma integration - Type-safe database queries and mutations
Error handling - Consistent error management throughout

Accessing the template

View the working template at /templates/database-crud in your application to see all features in action.

Architecture

The template follows a clean, layered architecture:

app/
├── components/products/
│   ├── ProductsTable.vue           # Main table component
│   ├── ProductsTableActions.vue    # Create button and search
│   ├── CreateProductForm.vue       # Create product form
│   ├── EditProductForm.vue         # Edit product form
│   └── DeleteProductDialog.vue     # Delete confirmation
├── services/
│   └── products-client-service.ts  # Client-side API calls
server/
├── api/products/
│   ├── index.get.ts                # Get all products
│   ├── index.post.ts               # Create product
│   ├── [id].get.ts                 # Get single product
│   ├── [id].put.ts                 # Update product
│   └── [id].delete.ts              # Delete product
└── services/
    └── products-server-service.ts  # Server-side business logic

Key features

Pagination with usePagination

The template uses a custom usePagination composable for managing pagination state:

<script setup>
const paginationComposable = usePagination({
  initialLimit: 10,
})

const {
  data: productsResponse,
  refresh: refreshProducts,
  pending,
} = await useAsyncData(
  'products',
  async () => {
    return await getProducts(paginationComposable.params.value)
  },
  {
    watch: [paginationComposable.params],
    lazy: true,
  }
)
</script>

The composable provides:

Reactive pagination parameters (page, limit, search)
Helper methods for page navigation
URL sync for shareable filtered views

Sheet and Drawer components for forms

Forms are displayed in shadcn-vue Sheet components for a modern, non-intrusive user experience:

Create form (Sheet):

<Sheet v-model:open="isCreateFormOpen">
  <SheetContent>
    <SheetHeader>
      <SheetTitle>{{ $t('products.table.createProduct') }}</SheetTitle>
      <SheetDescription>
        {{ $t('products.table.createProductDescription') }}
      </SheetDescription>
    </SheetHeader>
    <CreateProductForm @success="handleCreateSuccess" />
  </SheetContent>
</Sheet>

Edit form (Drawer):

The edit form uses a Drawer component for larger forms or different UX preferences.

Search functionality

Real-time search across multiple fields:

const handleSearch = (value: string) => {
  paginationComposable.updateSearch(value)
}

The search is debounced and updates the URL for shareable filtered views.

Data table with loading states

The table includes skeleton loaders for better UX during data fetching:

<TableBody>
  <template v-if="pending">
    <TableRow v-for="item in 10" :key="item">
      <TableCell><Skeleton class="w-3/4 h-6" /></TableCell>
      <TableCell><Skeleton class="w-full h-6" /></TableCell>
      <TableCell><Skeleton class="w-1/2 h-6" /></TableCell>
    </TableRow>
  </template>
  <template v-else>
    <!-- Actual data rows -->
  </template>
</TableBody>

Database schema

The products table schema in prisma/schema.prisma:

model Product {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  name        String
  description String?
  price       Decimal? @db.Decimal(10, 2)
  unit        String?  @default("unit")
  active      Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  userId      String?  @db.Uuid
  user        User?    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("product")
}

Key points:

Uses database-generated UUIDs for IDs
Includes audit fields (createdAt, updatedAt)
Optional user relationship for multi-tenant scenarios
Uses appropriate data types (Decimal for prices)

Client-side services

The client service (app/services/products-client-service.ts) provides type-safe API calls:

// Fetch products with pagination
export const getProducts = async (
  params?: ProductPaginationParams
): Promise<GetProductsResponse> => {
  const url = `/api/products?${new URLSearchParams(params).toString()}`
  return await $fetch<GetProductsResponse>(url)
}

// Create a product
export const createProduct = async (
  data: ProductPayload
): Promise<Product> => {
  return await $fetch<Product>('/api/products', {
    method: 'POST',
    body: data,
  })
}

// Update a product
export const updateProduct = async (
  id: string,
  data: ProductUpdatePayload
): Promise<Product> => {
  return await $fetch<Product>(`/api/products/${id}`, {
    method: 'PUT',
    body: data,
  })
}

// Delete a product
export const deleteProduct = async (id: string): Promise<void> => {
  await $fetch(`/api/products/${id}`, { method: 'DELETE' })
}

All services throw errors on failure - handle them with the useErrorHandler composable.

Server-side services

The server service (server/services/products-server-service.ts) handles business logic:

export async function getProducts(
  params: ProductPaginationParams = {}
): Promise<GetProductsResponse> {
  const { page = 1, limit = 10, search = '', sortBy = 'createdAt', sortOrder = 'desc' } = params

  const searchClause = buildSearchClause(search)
  const queryParams = calculateQueryParams(page, limit)
  const orderBy = { [sortBy]: sortOrder }

  const [products, totalCount] = await Promise.all([
    prisma.product.findMany({
      where: searchClause,
      orderBy,
      skip: queryParams.skip,
      take: queryParams.validLimit,
    }),
    prisma.product.count({ where: searchClause }),
  ])

  return {
    data: products,
    pagination: buildPaginationInfo(totalCount, page, queryParams.validLimit),
  }
}

Key features:

Efficient parallel queries for data and count
Reusable pagination utilities
Type-safe Prisma queries
Flexible search across multiple fields

API endpoints

The template includes RESTful API endpoints:

GET /api/products - List all products with pagination

export default defineEventHandler(async event => {
  const query = getQuery(event)
  const params: ProductPaginationParams = {
    page: Number(query.page) || 1,
    limit: Number(query.limit) || 10,
    search: (query.search as string) || '',
    sortBy: (query.sortBy as 'name' | 'createdAt') || 'createdAt',
    sortOrder: (query.sortOrder as 'asc' | 'desc') || 'desc',
  }

  return await getProducts(params)
})

POST /api/products - Create a new product

GET /api/products/id - Get a single product

PUT /api/products/id - Update a product

DELETE /api/products/id - Delete a product

All endpoints include proper error handling and validation.

Adapting the template for your needs

To create your own CRUD table based on this template:

1. Create your database model

Add your model to prisma/schema.prisma:

model Task {
  id          String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title       String
  description String?
  status      String   @default("pending")
  priority    String?
  dueDate     DateTime?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  userId      String   @db.Uuid
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("task")
}

Run migrations:

pnpm prisma migrate dev --name add-task-model

2. Create server services

Copy server/services/products-server-service.ts to server/services/tasks-server-service.ts and update:

Replace Product types with your model types
Update search fields to match your model
Adjust sorting options as needed
Modify any business logic specific to your domain

3. Create API endpoints

Copy the server/api/products/ directory to server/api/tasks/ and update:

Import your new service
Update type definitions
Adjust validation rules
Modify any endpoint-specific logic

4. Create client services

Copy app/services/products-client-service.ts to app/services/tasks-client-service.ts and update:

Update API endpoint paths
Modify payload interfaces
Adjust any client-specific logic

5. Create components

Copy the app/components/products/ directory to app/components/tasks/ and update:

Component names and file names
Form fields to match your model
Table columns
Internationalization keys
Any custom validation or business logic

6. Add internationalization

Add translations to all locale files (i18n/locales/*.json):

{
  "tasks": {
    "title": "Tasks",
    "description": "Manage your tasks",
    "table": {
      "title": "Title",
      "status": "Status",
      "priority": "Priority",
      "dueDate": "Due date",
      "actions": "Actions",
      "itemName": "task",
      "itemNamePlural": "tasks",
      "createTask": "Create task",
      "updateTask": "Update task",
      "deleteTask": "Delete task",
      "searchPlaceholder": "Search tasks..."
    }
  }
}

7. Create your page

Create app/pages/tasks/index.vue:

<script setup lang="ts">
const { t } = useI18n()

useHead({
  title: t('tasks.title'),
  meta: [{ name: 'description', content: t('tasks.description') }],
})
</script>

<template>
  <div class="mt-12 mb-20">
    <TasksTable />
  </div>
</template>

Best practices

Error handling

Use the useErrorHandler composable for consistent error handling:

const { handleError, handleSuccess } = useErrorHandler()

try {
  await createProduct(data)
  handleSuccess('Product created successfully')
} catch (error) {
  handleError(error)
}

Form validation

Use vee-validate with zod schemas for robust validation:

import { z } from 'zod'

const formSchema = z.object({
  name: z.string().min(1, 'Name is required'),
  description: z.string().optional(),
  price: z.number().positive().optional(),
})

Optimistic updates

For better UX, consider implementing optimistic updates:

// Optimistically update UI
const optimisticData = [...products.value, newProduct]

try {
  await createProduct(newProduct)
  // Success - data is already shown
} catch (error) {
  // Rollback on error
  await refreshProducts()
  handleError(error)
}

Performance considerations

Use pagination to avoid loading large datasets
Implement virtual scrolling for very long lists
Consider caching frequently accessed data
Use database indexes for search fields
Implement rate limiting on API endpoints

Common customizations

Adding filters

Add filter dropdowns alongside search:

<Select v-model="statusFilter">
  <SelectTrigger>
    <SelectValue placeholder="Filter by status" />
  </SelectTrigger>
  <SelectContent>
    <SelectItem value="all">All</SelectItem>
    <SelectItem value="active">Active</SelectItem>
    <SelectItem value="inactive">Inactive</SelectItem>
  </SelectContent>
</Select>

Update the pagination params to include the filter.

Adding bulk actions

Implement row selection and bulk operations:

const selectedRows = ref<string[]>([])

const handleBulkDelete = async () => {
  await Promise.all(selectedRows.value.map(id => deleteProduct(id)))
  selectedRows.value = []
  await refreshProducts()
}

Exporting data

Add export functionality:

const exportToCSV = () => {
  const csv = products.value
    .map(p => `${p.name},${p.description},${p.price}`)
    .join('\n')
  
  const blob = new Blob([csv], { type: 'text/csv' })
  const url = URL.createObjectURL(blob)
  const a = document.createElement('a')
  a.href = url
  a.download = 'products.csv'
  a.click()
}

Reference

This template is designed to be a starting point. Feel free to modify, extend, or simplify it based on your specific requirements.

AI chat

Build AI-powered chat interfaces with pre-built templates for ChatGPT, Claude, and Grok

S3 file upload/download

Learn how to implement file uploads, downloads, and metadata management with S3-compatible storage