Part 98: Working with Data in Prisma: Reading and Writing with SQLite

[App] Database Integration with Prisma

[App] Database Integration with Prisma

In our previous post, we explored setting up Prisma to work with an SQLite database in your Next.js project. Now, it's time to dive into the exciting part—reading and writing data. This guide will walk you through creating and executing database queries using Prisma's powerful client.

Setting Up Prisma Client

To interact with the database, we need to create an instance of PrismaClient. This acts as our connection to the database, enabling us to perform various operations like creating, reading, updating, and deleting records.

Creating a Standalone Script

Let's start by creating a standalone script to test our database interactions. We’ll name this file test-db.mjs and use the .mjs extension to run it as a module with Node.js.

// File: /path/to/your/project/scripts/test-db.mjs

import { PrismaClient } from '@prisma/client';

// Initialize the Prisma Client
const db = new PrismaClient({
  log: [{ emit: 'stdout', level: 'query' }],
});

// Example: Creating a new comment
const comment = await db.comment.create({
  data: {
    slug: 'fall-guys',
    user: 'Alice',
    message: 'This is a test comment.',
  },
});
console.log('created:', comment);

// Querying the database: Find all comments with a specific slug
const comments = await db.comment.findMany({
  where: { slug: 'fall-guys' },
});
console.log('comments:', comments);

Understanding the Code

• PrismaClient Initialization: We create a new instance of PrismaClient, passing a log option to output SQL queries to the console. This helps us see the actual SQL commands Prisma generates.

• Creating a Comment: The create method allows us to insert new records into the database. We define the data object with required fields (slug, user, and message) based on our schema.

• Reading Comments: We use the findMany method to retrieve records. The where clause filters results to only those with a specific slug. This is crucial for fetching comments related to a particular review.

Running the Script

To execute the script, open your terminal and run the following command:

node /path/to/your/project/scripts/test-db.mjs

This will output the created comment and the list of comments matching the specified slug.

Exploring the Database

After running the script, you might want to inspect the actual data stored in the SQLite database. You can use the sqlite3 command-line tool or a Visual Studio Code extension like SQLite Viewer for a graphical interface.

Using SQLite3

1. Open the Database:

   sqlite3 /path/to/your/project/prisma/dev.db

2. View Data:

   SELECT * FROM Comment;

3. Format Output:

   .mode column
   SELECT * FROM Comment;

Using SQLite Viewer

If you prefer a visual approach, the SQLite Viewer extension for Visual Studio Code provides an easy way to browse your database:

1. Open the dev.db File: Right-click and select "Open with SQLite Viewer."

2. Inspect Data: View tables and rows in a structured format.

Conclusion

Congratulations! You've successfully created and queried data using Prisma in your project. With the ability to both read and write data, you can now build robust features that interact with your database seamlessly. In our next exploration, we'll integrate these database operations into our main application code, allowing you to display and manage comments directly on your web pages. Stay tuned!