Prisma SQLite: Database Management with Prisma

Prisma is an open-source database toolkit that makes it easy for developers to work with databases in their applications. It aims to boost developer productivity and confidence when dealing with databases.

In this comprehensive guide, we will explore using Prisma with a SQLite database. We will start from scratch – initializing a Node.js project, setting up Prisma, modeling our data, and interacting with the database using Prisma Client.

Prerequisites

To follow along, you will need:

• Node.js v16.13.0 or higher
• npm (installed with Node.js)
• A code editor of your choice

Getting Started with Prisma SQLite

To get started, we first need to initialize a new Node.js project and install Prisma:

npm init -y
npm install prisma --save-dev
npx prisma init --datasource-provider sqlite

This will create a prisma directory with the following files:

prisma
├── schema.prisma
└── .env

The main things it does:

• Sets up SQLite as the database
• Creates your Prisma schema file for defining models
• Configures the .env file with database connection details

By default, it will use a SQLite file called dev.db in the prisma folder.

Configuring Prisma for SQLite

The next step is setting up Prisma. Run the following command:

npx prisma init --datasource-provider sqlite

This initializes Prisma in your project by doing a few things:

1. Creates a prisma directory
2. Adds a Prisma schema file schema.prisma
3. Configures the SQLite data source connector
4. Adds a .env file for managing environment variables

The default schema.prisma file looks like this:

// schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "linux-musl-openssl-3.0.x"]
}

datasource db {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

The generator block specifies that we want to generate Prisma Client in JavaScript.

The datasource block points to a SQLite database file, whose path will be defined in the .env file using the DATABASE_URL variable.

Speaking of which, here is the default .env that was created:

DATABASE_URL="file:./dev.db"

This points to a SQLite database file called dev.db in Prisma’s directory.

Defining Models for Prisma and SQLite

Before interacting with the database, we need to model our application data. This involves defining models in the Prisma schema which will be mapped to tables in the database.

For this demo, we will model users and posts for a basic blogging site:

model User {
  id        Int       @id @default(autoincrement())
  name      String?
  email     String    @unique
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt 
  posts     Post[]
}

model Post {
  id          Int       @id @default(autoincrement()) 
  title       String    
  content     String?
  published   Boolean   @default(false)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  author      User?     @relation(fields: [authorId], references: [id])
  authorId    Int?
}

We have defined:

• A User model with id, name, email fields and a one-to-many posts relation
• A Post model with id, title, content, published fields and a many-to-one author relation

The @ symbols provide special model properties, like setting primary keys, defaults, relations, etc.

We now have the basic data model in place!

Database Migration

So far, we only have a Prisma schema – but no actual database tables yet. We can translate the schema into database tables using a migration:

npx prisma migrate dev --name init

This performs a few actions:

1. Generates Prisma Client API based on data model (into node_modules/@prisma/client)
2. Runs the migration, creating User and Post tables in the database
3. Creates a migration directory with the migration file:

├── migrations
   └── 20221125190216_init
       └── migration.sql  

We now have a SQLite database file with tables representing our application models!

Interacting with the Database

It’s finally time to write some code to interact with our database.

Create a new TypeScript file:

touch index.ts

Let’s start with some boilerplate code:

// index.ts

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // queries will go here  
}

main()
  .catch(e => {
    throw e
  })
  .finally(async () => {
    await prisma.$disconnect()
  }) 

This creates a PrismaClient instance to query the database. The main function will contain our queries.

Create a New User

To create a new user record, we can use prisma.user.create():

async function main() {

  const user = await prisma.user.create({
    data: {
      name: 'Alice',
      email: '[email protected]'
    },
  })

  console.log(user)

}

The data property allows us to provide the data for the new record.

Let’s run the code:

npx ts-node index.ts

Output:

{
  id: 1,
  name: 'Alice',
  email: '[email protected]',
  createdAt: 2022-11-25T19:10:57.967Z,
  updatedAt: 2022-11-25T19:10:57.967Z
}

A new user was created in the database!

Read All Users

To fetch all user records, we can use prisma.user.findMany():

async function main() {

  const users = await prisma.user.findMany() 
  console.log(users)

}

Output:

[
  {
    id: 1,
    name: 'Alice',
    email: '[email protected]',
    createdAt: 2022-11-25T19:10:57.967Z,
    updatedAt: 2022-11-25T19:10:57.967Z
  }
]

Create a Post for the User

We can create a post for a user in one transaction using nested writes:

async function main() {

  const post = await prisma.user.update({
    where: { email: "[email protected]" },
    data: {
      posts: {
        create: { 
          title: "Hello World" 
        }
      }
    },
    include: {
      posts: true
    }
  })
    
  console.log(post)

} 

Let’s examine what this does:

• where: Filters to target the “[email protected]” user record
• data.posts.create: Creates a new related Post
• include: Includes the posts relation in response

Output:

{
  id: 1,
  name: 'Alice',
  email: '[email protected]',
  posts: [
    {
      id: 1,
      title: 'Hello World',
      content: null,     
      published: false,
      createdAt: 2022-11-25T19:18:44.659Z,
      updatedAt: 2022-11-25T19:18:44.659Z,
    }
  ]  
}

A new post was created for that user!

Querying Data

To try out some querying, first let’s write a script (e.g. script.ts):

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {

  // Querying logic will go here

}

main()

Then we can start writing some queries, like creating a new user:

const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: '[email protected]'
  },
})
console.log(user)

Which prints:

{
  id: 1,
  name: 'Alice',
  email: '[email protected]',
  createdAt: 2023-01-01T00:00:00.000Z
}

We can also try a nested write query to create a post for the user:

const post = await prisma.post.create({
  data: {
    title: 'My first post',
    content: 'Hello world',
    published: true,
    author: {
      connect: { id: 1 },  
    },
  },
})

Then fetch all posts including their author data using an include:

const allPosts = await prisma.post.findMany({
  include: {
    author: true,
  },
})
console.dir(allPosts, { depth: null }) 

Which prints:

[
  {
    id: 1,
    title: 'My first post',
    content: 'Hello world',
    published: true,
    createdAt: 2023-01-02T00:00:00.000Z,
    updatedAt: 2023-01-02T00:00:00.000Z,
    authorId: 1,
    author: {
      id: 1,
      name: 'Alice',
      email: '[email protected]',
      createdAt: 2023-01-01T00:00:00.000Z 
    }
  }
] 

This shows how we can easily fetch related data using Prisma’s generated types and helpers.

There are many more querying capabilities like filtering, pagination, transactions, and raw SQL access. But this covers some basics with SQLite to get started.

Cascade Delete

When removing a user, associated posts can be deleted automatically using onDelete cascade:

model User {
  // ...
  posts Post[] @relation(onDelete: Cascade) 
}

Now when deleting a user, related posts are deleted automatically.

Next Steps

That covers the basics of using Prisma with SQLite! Here are some next things you may want to explore:

• Queries: Filtering, sorting, pagination, transactions
• Working with relations: include, createMany, connect
• Prisma Studio: GUI to view & edit data
• Adding integration tests
• Deploying app to production

Of course, much more can be done like implementing business logic with an ORM or query builder, building REST or GraphQL APIs, integrating with application frameworks like Next.js, and deploying to production.

Prisma aims to help make working with SQLite and other databases more intuitive and productive for app developers. Things like its declarative data modeling, auto-generated and type-safe querying API, and streamlined migrations help reduce boilerplate and speed up development.