DEV Community: Jon Crowell

Remix, SQLite, and Prisma, Oh my!

Jon Crowell — Fri, 07 Jan 2022 18:05:04 +0000

Previously published at fullstackfish.com

Let's create a Remix app and connect it to SQLite with Prisma.

Open up a terminal and create a new Remix app.

npx create-remix@latest

You'll go through a short interview of how you want to set up your app. Pick these options to follow along or modify if you're in the mood for a little adventure.

Call the app whatever you like. I'm calling mine "run-rsp".

Pick the option to deploy to the Remix App Server for now. This is easy to change later.

Pick JavaScript.

Decline the offer to run npm install as we will be using Yarn today.

Once the setup process is complete, cd into the project folder.

cd run-rsp

Open the project in VS Code, or whatever editor you want to use.

code .

Install the dependencies.

yarn

Verify that everything his set up correctly.

yarn dev

You should now see the template Remix app running in your browser.

Now let's replace the default code with our own. We're going to be working primarily in the app folder.

Let's open up the root.jsx file and examine its contents.

import {
  Links,
  LiveReload,
  Meta,
  Outlet,
  Scripts,
  ScrollRestoration
} from "remix";

export function meta() {
  return { title: "New Remix App" };
}

export default function App() {
  return (
    <html lang="en">
      <head>
        <meta charSet="utf-8" />
        <meta name="viewport" content="width=device-width,initial-scale=1" />
        <Meta />
        <Links />
      </head>
      <body>
        <Outlet />
        <ScrollRestoration />
        <Scripts />
        {process.env.NODE_ENV === "development" && <LiveReload />}
      </body>
    </html>
  );
}

The content we are seeing on the home page is coming from the <Outlet /> component. We'll edit that in a second.

Let's make a small change so we can see LiveReload in action.

Change the title of the app in the meta() function to whatever you want. Save the file and notice that the title of your app in the browser changes almost instantly. (Check the browser tab of the app or right-click and view page source)

The home page content is routes\index.jsx. Open the file up and delete everything. Save the file. You'll now see an error in the browser.

Add the following function to fix the error.

export default function Index() {
  return (
    <div>
      <h1>I'll soon be connected to SQLite</h1>
    </div>
  );
}

You may need to refresh your browser to get LiveReload working again. Once you've done that, you'll see your heading rendered with the browser's default styling. Let's add a CSS reset and some global styles to our app so that it looks a little better.

In our root.jsx file, add a links() function. We are already importing the Links component and using it in the head of our document, so whatever we export from our links() function will be used in the Links component.

// root.jsx
export function links() {
  return [
    {
      rel: "stylesheet",
      href: "https://unpkg.com/modern-css-reset@1.4.0/dist/reset.min.css"
    }
  ]; 
}

Now add a styles folder in app and add a file called global.css with a rule or two.

Save the file. Whatever styles you've added won't be applied yet — we haven't linked the stylesheet to the app.

Go back to root.jsx. Import your styles and then add another link to the array in links().

import globalStyles from '~/styles/global.css'
...
// root.jsx
export function links() {
  return [
    {
      rel: "stylesheet",
      href: "https://unpkg.com/modern-css-reset@1.4.0/dist/reset.min.css"
    },
    {
      rel: "stylesheet",
      href: globalStyles
    }
  ]; 
}

Save your changes and you should now see your styles applied in the browser.

Prisma and SQLite setup

Stop the app and let's add some dependencies.

yarn add @prisma/client
yarn add -D prisma

After they finish installing, initialize prisma with the sqlite provider.

npx prisma init --datasource-provider sqlite

This will add a prisma folder with a schema.prisma file. It also adds a .env file if you don't already have one.

By including the datasource provider, we don't need to edit the schema.prisma file to change the default provider to sqlite, and the .env file is populated for us with a connection string to a sqlite file. Nice touch, Prisma.

Add a model to your schema.prisma file for Soccer Teams. Install the Prisma extension if you haven't already to format your Prisma files. Prettier may try to override Prisma's formatting, so if that's the case, right-click the file, select format document, and indicate that Prisma should format the file. Now you don't need to worry about getting your models formatted. We are going to keep things simple here just to show how everything is wired up together. Our database will be a list of soccer teams, or whatever kind of teams you like.

Add the Team model to your schema.prisma file.

generator client {
  provider = "prisma-client-js"
}

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

model Team {
  id        String   @id @default(uuid())
  team      String
  country   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Deploy the model to your database

npx prisma db push

We should now have a dev.db file in the prisma folder next to our schema.prisma file.

Launch prisma studio to see the results.

npx prisma studio

We can add a few teams directly in Prisma Studio. Click the Add record button to do so.

Add a file (app/lib/db.server.js) to configure our connections to the database. Note that the "server" in the file tells Remix to run this on the server.

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

let prisma;

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

export default prisma;

Now let's get the teams from the database and display them in our app. We'll use a loader function (this code runs on the server), which will feed our component with the useLoaderData() hook.

// routes/index.js
import { useLoaderData, Link } from 'remix'
import  prisma from '~/lib/db.server'

export const loader = async () => {
  const data = {
    teams: await prisma.team.findMany(),
  }
  return data
}

export default function Index() {
  const { teams } = useLoaderData()

  return (
    <>
      <div>
        <h1>Soccer Teams around the world</h1>
      </div>
      <ul>
        {teams.map((team) => (
          <li key={team.id}>
            <h1>{team.team}</h1>
            <p>{team.country}</p>
          </li>
        ))}
      </ul>
    </>
  )
}

We are using the Prisma findMany() function to get all the teams in our database.

That's it for now. The app we've built isn't particularly useful as is, but you now have the building blocks to build anything you want and style it however you like.

It's been said many times but remains true: there has never been a better time to be a developer. There are so many wonderful tools to choose from. The biggest challenge can be deciding what to include in your toolbelt. Remix and Prisma should be on your short list. The developer experience with both is top notch.

Enjoy, and Happy New Year!

nvm is for barbarians. Manage Node versions with Volta instead.

Jon Crowell — Tue, 19 Oct 2021 17:35:25 +0000

Previously published at fullstackfish.com

Thanks to Volta, managing multiple versions of Node is no longer a pain.

Under the covers, all the Node version managers are doing the same thing: setting up symlinks to different versions of Node installed on your computer. The difference between them is how they manage those symlinks. If you know the correct version of Node for the project you are currently working on, you can use nvm to set it to the active version, and all is well, as long as you remember to do so. The problem here is that you need to know the correct version of Node. And if you're switching from a project that has different Node version requirements, you have to remember to switch each time you open one of those projects. As Wes Bos would say, that's no way to live your life. That's what barbarians do.

Volta takes a set-it-and-forget-it approach. You "pin" the version of Node you need for your project and Volta auto-magically switches to that version anytime you are working on that project, without any action on your part. You can have multiple projects open at the same time, each with their own, different versions of Node, running simultaneously.

"Because Volta’s toolchain always keeps track of where you are, it makes sure the tools you use always respect the settings of the project you’re working on. This means you don’t have to worry about changing the state of your installed software when switching between projects."

— Understanding Volta

Installation

Go to the getting started page on the Volta site and follow the instructions for your operating system.

Usage

Pin the version of Node you want to your project by running the pin command in the root folder of your project. For example:

volta pin node@14

Volta adds an entry to your package.json.

{
...
  "volta": {
    "node": "14.18.0"
  }
}

Now anyone on your team that has Volta installed will get the benefit of having the correct version of Node pinned. If you don't have the specified version of Node, Volta will download and install it for you, then switch to that version. For any barbarians that may also be working on your project, the entry won't affect them, but will allow them to see the version of Node they'll need to use.

Volta is written in Rust and is completely cross-platform, working in Windows and in all Unix shells.

As web developers, we have more than enough things to worry about. Using Volta means that managing Node versions is no longer one of them.

SQL Server to React in 60 Seconds With Prisma

Jon Crowell — Tue, 05 Oct 2021 16:44:07 +0000

Previously published at fullstackfish.com

Connecting SQL Server to React with Prisma is a straightforward and satisfying experience. This post will guide you through the process of getting your Azure SQL Server data onto a screen in a React app in less than a minute. Really. (As long as you pause your timer while npm is downloading files.)

Of course, there are a few prerequisites before you can get it connected that quickly. Prisma supports working with existing databases (which we are doing in this post) as well as defining the database in your app and then creating the database objects through migrations (which will be covered in other posts).

You must have an existing Azure SQL Server with a database that you have credentials for. You must also have your ip whitelisted in Azure.
The database must have at least one table with data that we can query.
You must have up-to-date versions of Node.js (12.6 or higher) and Visual Studio Code installed. See nvm is for barbarians. Manage Node versions with Volta instead for a great way to manage Node versions.
You must have create-next-app installed globally. (npm global install create-next-app if you don't)
You must have an empty folder on your hard-drive for this project.

And that's it. Let's do this.

Open your empty project folder in VS Code
Open a terminal and run the following command to initialize a new next app:

npx create-next-app .

The dot (.) will create the next app in the current path.

Once the app is initialized, you should see a confirmation. Run the app to verify it compiles and runs correctly.

npm run dev

Open http://localhost:3000 in a browser to verify.

Now stop the app and let's get some data.

You'll need to add the following dependencies, prisma as a dev dependency and @prisma/client as a regular dependency.

npm i -D prisma

npm i @prisma/client

Check your package.json file to verify that you have version 3.01 or greater of prisma in the dev dependencies.

Initialize prisma in your app. This will add a prisma folder in root with a schema.prisma file. It will also add a .env file if you don't already have one, which you shouldn't at this point. For future reference, if you do have a .env file, this step will add a new entry into the file without harming anything else you may have in there.

npx prisma init

The default connection that is configured in the schema.prisma file is for Postgres. Let's fix that.

Edit the file to look like this:

// schema.prisma
generator client {
  provider        = "prisma-client-js"
}

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

Note the constant DATABASE_URL that is referenced. We'll update the value of that constant in the .env file now.

Set the connection string in the .env file to point to your Azure SQL Server database. I'm using a username and password for this example. Explaining all the variations on connection strings is beyond the scope of this post, but I'm assuming you can figure that out.

My connection string, with the sensitive bits masked to protect the innocent:

DATABASE_URL="sqlserver://[SERVERNAME].database.windows.net:1433;database=[DATABASENAME];encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;user=[USERNAME];password=[PASSWORD]"

Connection strings can be long, so you'll have to scroll to the right to see the whole thing.

Now we're ready to introspect the database. This step will populate the Prisma schema based on your database schema. Make sure you have your schema.prisma file open before you run the following. It's fun to watch the magic happen.

npx prisma db pull

Boom! Our app and database are now friends.

Assuming your connection string is correct and you have access to your database, you should now see a list of all the tables in the dbo schema generated as models in your schema.prisma file. This functionality is currently limited to tables in the dbo schema. You won't have views or functions, or stored procs in the schema file, which is true for all databases Prisma works with, so don't get your SQL Server feelings hurt. You can still interact with those objects in your app.

Now that we have a valid schema, let's run the command to generate the Prisma client.

npx prisma generate

Next is a fabulous tool that keeps your backend and frontend code safely separate but extremely easy to work with in the same project, among many other benefits.

Add a lib folder in root with a prisma.js file, and copy/paste or type the following into that file:

// lib/prisma.js
import { PrismaClient } from "@prisma/client";

let prisma;

if (process.env.NODE_ENV === "production") {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

export default prisma;

This is the best practice defined by Prisma and Next for the integration. You'll be able to import a single instance of the prisma client throughout your app (something we'll do shortly), which will handle connection pools for you and protect you from memory leaks. See Best practice for instantiating PrismaClient with Next.js for more details.

Let's define an API route that our front-end will consume.

Add a file in pages/api called products.js. Replace "products" with whatever table you want to work with from your database. I'm using Adventure Works for this example.

Add this to the products.js:

// pages/api/products.js
import prisma from "../../lib/prisma";

export default async function handle(req, res) {
  const products = await prisma.product.findMany();
  res.json(products);
}

Now delete the contents of the pages/index.js file and replace it with the following as a guide. You'll need to use your React skills to create a component that incorporates your data.

You'll need to replace products with whatever table you want from your database, and use fields from that table instead of the ones I'm using below.

// pages/index.js
export const getServerSideProps = async () => {
  const res = await fetch("http://localhost:3000/api/products");
  const products = await res.json();
  return {
    props: { products },
  };
};

export default function Product({ products }) {
  return (
    <div>
      {products.map((p) => {
        return (
          <div key={p.ProductID}>
            <p>
              {p.Name} — ${p.ListPrice}
            </p>
          </div>
        );
      })}
    </div>
  );
}

That's it. Let's see if it worked.

npm run dev

Open http://localhost:3000 in a browser and behold your data!

Hit me up on twitter @jonrcrowell to let me know what you think or if you ran into any issues.

Prisma released the SQL Server connector on September 7, 2021. Read the official announcement here:
Microsoft SQL Server Support in Prisma is Production-Ready