How to Build a Node.js Database using Prisma and SQLite

Prisma is a modern, type-safe ORM (Object-Relational Mapping) tool that simplifies database access for Node.js and TypeScript applications. It provides an intuitive data model, automated migrations, and a powerful query builder that works across different databases.

SQLite is a popular choice for embedded databases that store data in a single file. It‘s lightweight, fast, and requires no setup or database server.

In this tutorial, we‘ll combine Prisma and SQLite to build a simple Twitter-style database in Node.js. You‘ll learn how to:

• Set up a new Node.js project with Prisma and SQLite
• Define your database schema using Prisma‘s data modeling language
• Use Prisma Migrate to create and evolve your database
• Interact with your data using Prisma Client

By the end, you‘ll have a working database for users and tweets that you can extend further. Let‘s get started!

Setting Up the Project

First, create a new directory for your project and navigate into it:

mkdir twitter-clone 
cd twitter-clone

Next, initialize a new Node.js project and install the required dependencies:

npm init -y
npm install prisma typescript ts-node @types/node --save-dev
npm install @prisma/client 

This installs Prisma, TypeScript, and the Prisma Client library that we‘ll use later to access the database.

To configure TypeScript, create a tsconfig.json file in the project root with the following contents:

{
  "compilerOptions": {
    "sourceMap": true,  
    "outDir": "dist",
    "strict": true,
    "lib": ["esnext"],  
    "esModuleInterop": true
  }
}

Finally, initialize a new Prisma project with SQLite:

npx prisma init --datasource-provider sqlite

This command generates a few important files:

• prisma/schema.prisma: Defines your database models and Prisma Client API
• .env: Holds the database connection URL (defaults to SQLite)
• package.json: Configures dependencies and scripts for your project

Here‘s how your project should look:

With the setup out of the way, let‘s move on to defining the database schema.

Defining Database Models

Prisma uses a declarative syntax for specifying your database models. You define models in the schema.prisma file and Prisma generates the corresponding database tables and TypeScript types.

For our Twitter clone, we‘ll define two models:

• User: Represents a user account with an ID, email, username, and tweets
• Tweet: Represents a tweet posted by a user with an ID, timestamp, text content, and author

Open prisma/schema.prisma and update it with the following:

model User {
  id       Int      @id @default(autoincrement())
  email    String   @unique
  username String
  tweets   Tweet[]
}

model Tweet {
  id        Int      @id @default(autoincrement()) 
  createdAt DateTime @default(now())
  text      String
  userId    Int
  user      User     @relation(fields: [userId], references: [id])
}

Here‘s what‘s going on:

• Each model has an id field marked with @id to uniquely identify records
• Relation fields like tweets and user define connections between models
• Attributes like @unique, @default, and @relation specify constraints and default values

With the schema in place, Prisma can generate migrations to map the models to database tables.

Running Database Migrations

Now that we‘ve defined our models, let‘s create the corresponding tables in the database.

Prisma provides a migrate command to handle database migrations. It examines your Prisma schema, compares it to the current database structure, and generates the SQL commands needed to update the database to match the schema.

To create your first migration, run:

npx prisma migrate dev --name init

This will generate a new migration file in prisma/migrations with timestamped SQL commands:

Prisma applies the migration to the database and generates the Prisma Client library based on your schema. We‘ll look at how to use Prisma Client to read and write data next.

Interacting with Data

To test our new database, let‘s write a short script that creates a user, posts a tweet on their behalf, and retrieves the user with their tweets.

Create a new file index.ts and add the following code:

import { PrismaClient } from ‘@prisma/client‘

const prisma = new PrismaClient()

async function main() {
  // Create a new user
  const newUser = await prisma.user.create({
    data: {
      email: ‘[email protected]‘,
      username: ‘alice‘,
    },
  })
  console.log(‘Created user:‘, newUser)

  // Create a tweet belonging to the user
  const newTweet = await prisma.tweet.create({
    data: { 
      text: ‘Hello World!‘,
      userId: newUser.id,  
    },
  })
  console.log(‘Created tweet:‘, newTweet)

  // Retrieve the new user with their tweets
  const userWithTweets = await prisma.user.findUnique({
    where: { email: ‘[email protected]‘ },
    include: { tweets: true },
  })
  console.dir(userWithTweets, { depth: null })
}

main()
  .catch(e => console.error(e))
  .finally(() => prisma.$disconnect()) 

Here‘s a step-by-step breakdown:

1. Import and instantiate PrismaClient, which generates a type-safe database client based on our schema
2. Define an async main function to hold our database queries
3. Use prisma.user.create to insert a new user record
4. Use prisma.tweet.create to add a tweet for the user
5. Use prisma.user.findUnique to fetch the user by their unique email and include their tweets via the tweets relation
6. Log the results and disconnect from the database when done

To run the script, add the following to the "scripts" section of package.json:

"scripts": {
  "dev": "ts-node index.ts"
}

Then execute:

npm run dev

You should see output like:

Congrats, you just created your first user and tweet via Prisma! The query results show how Prisma nests related data according to the schema.

Next Steps

In this tutorial, we covered the basics of building a Node.js database with Prisma and SQLite. We set up a project, defined a schema with two models, created and applied a database migration, and wrote a script to insert and retrieve data.

There are many ways you could extend this example:

• Add more fields to the User and Tweet models, such as a user bio or hashtags for tweets
• Implement a follows/followers relationship between users
• Define a Like model to let users like and unlike tweets
• Expose the data via a REST or GraphQL API using a web framework like Express

Check out the Prisma docs to learn more about relations, working with data, and deploying your database.

You can find the complete code for this tutorial on GitHub:

https://github.com/gaelgthomas/prisma-sqlite-example

I hope this tutorial helped you get up and running with Prisma and SQLite in Node.js. Feel free to reach out on Twitter with any questions!