Codecademy Logo

Databases and Data Access with Prisma

Related learning

  • Skill path

    AI-Assisted Back-End Development

    Learn back-end development with AI tools. Build APIs, work with Node.js, and manage databases using AI coding agents for faster workflows.
    • Includes 2 Courses
    • With Certificate
    • Intermediate.
      4 hours

Backend Database Role

In backend development, a database serves as the backbone for storing and retrieving application data. This ensures that data persists across requests and survives server restarts, making it crucial for maintaining state information and user progress.

```sql
-- Example of creating a basic table in SQL
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);


-- Example of inserting data into the table
INSERT INTO users (id, username, email) VALUES (1, 'sampleUser', '[email protected]');


-- Query to retrieve data
SELECT * FROM users;
```

PostgreSQL vs. SQLite

Relational databases like SQLite and PostgreSQL form the backbone of backend systems. While SQLite excels in local development due to its lightweight nature, PostgreSQL is robust enough for production. The choice between them hinges on your project’s scale and requirements.

-- Connect to a PostgreSQL database
\c my_database;


-- Creating a table in PostgreSQL
drop table if exists users;
create table users (
    id serial primary key,
    name varchar(100),
    email varchar(100)
);


-- Connecting to a SQLite database
sqlite3 my_database.db


-- Creating a table in SQLite
drop table if exists users;
create table users (
    id integer primary key autoincrement,
    name text,
    email text
);

Data Modeling Basics

Data modeling in database design plays a crucial role by helping define entities, attributes, and relationships. This structured plan efficiently mirrors application requirements, fostering a smoother implementation process.

-- Defining entities and relationships in SQL
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
);


CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Node.js ORM Basics

An ORM, or Object-Relational Mapping, enables Node.js developers to manage databases using standard object syntax, simplifying data operations like retrieval and storage without direct SQL commands.

// Define a Sequelize model for a 'User' table
const User = sequelize.define('User', {
  firstName: {
    type: Sequelize.STRING,
    allowNull: false
  },
  lastName: {
    type: Sequelize.STRING,
    allowNull: false
  }
});


// Retrieve all records from the 'User' table
User.findAll().then(users => {
  console.log(users);
});

Prisma Schema & Models

A Prisma schema is crucial as it outlines your database models and configuration. Migrations help in translating these models into actual database structures like tables, and they configure the connection of your application to data sources like SQLite. This link between application code and data is essential for seamless data management.

// Example Prisma schema definition with SQLite
// Prisma schema file


// This Prisma schema connects the application to an SQLite database


datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}


// Model definition
model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}


// Run migration
yarn prisma migrate dev --name init

SQL Migration Basics

Migrations manage schema changes in databases, ensuring consistent updates across different environments. They help track transformations, making it easier to manage and revert changes if needed. This ensures both development and production databases remain synchronized.

-- Create a table to track migrations
CREATE TABLE migrations (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- Example of a migration script
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

Express.js Endpoints

Express.js endpoints handle API requests by validating inputs, performing asynchronous database operations with an ORM like Prisma, and returning structured responses. This ensures your web applications run smoothly and handle data effectively, with less boilerplate code. By ensuring proper validations and using effective ORMs, you’re ensuring the security and efficiency of your applications.

const express = require('express');
const { PrismaClient } = require('@prisma/client');
const app = express();
const prisma = new PrismaClient();


// Middleware for parsing JSON requests
app.use(express.json());


// Endpoint to get user information by ID
app.get('/user/:id', async (req, res) => {
  const { id } = req.params;
  try {
    // Validate input
    if (!Number.isInteger(+id)) {
      return res.status(400).json({ error: 'Invalid User ID' });
    }


    // Fetch user from database
    const user = await prisma.user.findUnique({
      where: { id: parseInt(id) },
    });


    if (user) {
      res.status(200).json(user);
    } else {
      res.status(404).json({ error: 'User not found' });
    }
  } catch (error) {
    res.status(500).json({ error: 'Server error' });
  }
});


// Start the server
app.listen(3000, () => {
  console.log('Server running on port 3000');
});

SQL Relationships

In relational databases, relationships link data across tables using foreign keys. The foreign key ties one table to another, ensuring table data integrity. Transactions group multiple operations, enabling them to execute atomically—all succeed or all fail—thus maintaining database stability and consistency throughout operations.

-- Create two tables and establish a relationship with a foreign key
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);


CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);


-- Create a foreign key relationship
ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);


-- Example of a transaction
BEGIN TRANSACTION;
  INSERT INTO customers (customer_id, customer_name) VALUES (1, 'Alice');
  INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1, '2023-10-10');
COMMIT;

API DTOs Overview

Data Transfer Objects (DTOs) in API design help manage data sent to clients. This crucial step prevents exposing sensitive internal data. Think of DTOs as a controlled interface ensuring only the intended fields reach the client safely.

class UserDTO {
   private String username;
   private String email;


   public UserDTO(String username, String email) {
       this.username = username;
       this.email = email;
   }


   public String getUsername() {
       return username;
   }


   public String getEmail() {
       return email;
   }
}

Learn more on Codecademy

  • Skill path

    AI-Assisted Back-End Development

    Learn back-end development with AI tools. Build APIs, work with Node.js, and manage databases using AI coding agents for faster workflows.
    • Includes 2 Courses
    • With Certificate
    • Intermediate.
      4 hours
Previous