Task 3: Adding Persistence with a Database and ORM
In this task, you will add persistence to your API by setting up a database and an Object-Relational Mapping (ORM) library. We will use Drizzle ORM, a lightweight and flexible ORM for TypeScript, and SQLite as the database engine.
Step 1: Understand the role of SQLite in the project
To persist data in our application, we need a database. A database is a structured collection of data. It supports the storage and retrieval of data in a way that is efficient, reliable, and convenient. Databases are used in many applications, from simple to complex, to store and manage data.
SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the most widely deployed database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled with countless other applications that people use every day.
In our project, we will use SQLite as the database engine. SQLite is a great choice for small to medium-sized applications, as it is lightweight, fast, and easy to set up. It is perfect for prototyping, development, and applications that do not require a full-fledged client-server database system.
Step 2: Understand the role of an ORM in the project
An Object-Relational Mapping (ORM) library is a programming technique for converting data between incompatible type systems. In the context of databases, an ORM library is used to map objects from an object-oriented programming language to a relational database model.
Although the term ORM is often associated with object-oriented programming languages, such as Java, Python, and Ruby, it is also more broadly used for any library that abstract the database interaction in a higher-level programming language. Drizzle falls more into the latter category, as it is designed to work with TypeScript and JavaScript.
Drizzle ORM provides a simple and intuitive SQL-like API for interacting with databases, making it easy to work with relational databases in TypeScript. It supports multiple database engines, including SQLite, PostgreSQL, and MySQL.
In our project, we will use Drizzle ORM to interact with the SQLite database. Drizzle ORM will handle the creation of tables, executing queries, and managing the database schema.
Step 3: Understanding basic concepts of databases
SQLite is a relational database management system (RDBMS) that stores data in tables. Each table consists of rows and columns, where each row represents a record and each column represents a field in the record.
Once can create an entire course on databases, but here are two basic concepts that we will be using in this task: Database Schema and Migrations.
Database Schema
A database schema is a blueprint of the database structure. It defines the tables, columns, relationships, and constraints that make up the database. The schema is used to create, update, and query the database.
Structure Query Language (SQL)
SQL is a domain-specific language used in programming and designed for managing data held in a relational database management system. It is used to create, read, update, and delete data in a database. SQL is the standard language for relational database management systems.
For example, to create a table in SQLite, you would use the following SQL statement:
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
date INTEGER NOT NULL
);In this SQL statement:
CREATE TABLEis the SQL command to create a new table.postsis the name of the table.id,content, anddateare the columns in the table.INTEGER,TEXT, andINTEGERare the data types of the columns.PRIMARY KEYspecifies that theidcolumn is the primary key.AUTOINCREMENTspecifies that theidcolumn will auto-increment.
Primary Key: A primary key is a column or a set of columns that uniquely identifies each row in a table. It enforces the uniqueness of the values in the column(s) and ensures that no two rows have the same primary key value.
Thanks to Drizzle ORM, we don’t have to write raw SQL queries like the one above. Drizzle ORM provides a higher-level API that abstracts the database interaction and allows us to work with TypeScript objects instead of raw SQL.
Relationships
In relational databases, relationships are associations between tables that are based on common columns. For example, a posts table might have a relationship with a comments table, where each post can have multiple comments associated with it. Here is how we would define a relationship between the posts and comments tables:
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
date INTEGER NOT NULL
);
CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER NOT NULL,
content TEXT NOT NULL,
date INTEGER NOT NULL,
FOREIGN KEY (post_id) REFERENCES posts(id)
);In this example, the comments table has a post_id column that references the id column in the posts table. This establishes a relationship between the two tables, where each comment is associated with a specific post.
Foreign Key: A foreign key is a column or a set of columns in a table that establishes a link between data in two tables. It enforces referential integrity between the two tables, ensuring that the values in the foreign key column(s) match the values in the primary key column(s) of the referenced table.
Migrations
Migrations are a way to manage changes to the database schema over time. They allow you to version control the database schema and apply changes in a consistent and repeatable manner. Migrations are typically used to create tables, add columns, modify constraints, and perform other schema changes.
For example, suppose we wanted to add a new column author to the posts table. We would create a migration file that contains the SQL statement to add the column:
ALTER TABLE posts ADD COLUMN author TEXT;When we run the migration, the author column will be added to the posts table. Migrations are essential for keeping the database schema in sync with the application code and ensuring that changes are applied correctly.
Drizzle ORM provides a CLI tool, Drizzle Kit, that generates migration files based on the database schema definition. It allows you to manage the database schema and apply migrations with ease.
Step 4: Set up Drizzle ORM and other dependencies
We start by installing the required packages for Drizzle ORM and SQLite. We will also install the @types/better-sqlite3 package for TypeScript type definitions.
pnpm add drizzle-orm better-sqlite3
pnpm add -D drizzle-kit @types/better-sqlite3-
BetterSqlite3 brands itself as the fastest and simplest library for SQLite3 in Node.js.
-
Drizzle Kit is a CLI tool for managing database migrations and generating SQL schema files. It also provides a studio for visualizing the database schema.
With these packages installed, we can now proceed to set up the database schema and connect to the SQLite database.
Step 5: Create the database schema
Create a new folder db in the src folder. Inside this folder, create a new file schema.ts and add the following code:
import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";
export const posts = sqliteTable("posts", {
id: integer("id").primaryKey({ autoIncrement: true }),
content: text("content").notNull(),
date: integer("date", { mode: "timestamp" }).notNull(),
});In this code snippet, we define a table posts with three columns: id, content, and date.
- The
idcolumn is an auto-incrementing primary key. - The
contentcolumn is a text field that cannot be null - The
datecolumn is an integer field representing a timestamp that cannot be null.
Note the names in quotation marks are the names of the columns in the database whereas the names in the object are the names of the columns in the TypeScript code. This is useful when you want to have different names in the database and in the code.
Step 6: Set up the Hono.js server and database connection
In this step, we will add the necessary files and configuration to the Hono.js server to connect to the SQLite database using Drizzle ORM.
Add drizzle.config.ts file
Create a new file drizzle.config.ts in the project root directory (api folder) and add the following configuration:
import type { Config } from "drizzle-kit";
export default {
dialect: "sqlite",
schema: "./src/db/schema.ts",
out: "./drizzle",
dbCredentials: {
url: "./sqlite.db",
},
} satisfies Config;In this configuration:
dialectspecifies the database engine we are using (SQLite).schemapoints to the schema file we created earlier.outspecifies the output directory for the generated migration files.dbCredentials.urlspecifies the path to the SQLite database file. In other database engines, this would be the connection URL.
Add db:generate script to package.json
Add the following script to the scripts section of the package.json file:
{
"scripts": {
"db:generate": "drizzle-kit generate"
}
}This script will generate the SQL migration file based on the database schema definition.
Generate the database schema
Run the following command to generate the SQL migration file:
pnpm run db:generateThis will create a migration file in the drizzle folder with the necessary SQL statements to create the posts table. You should see an output similar to this:
Reading config file drizzle.config.ts
1 tables
posts 3 columns 0 indexes 0 fks
[✓] Your SQL migration file ➜ drizzle/0000_great_sharon_carter.sql 🚀If you open the generated SQL file, you will see the SQL statements to create the posts table:
CREATE TABLE `posts` (
`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
`content` text NOT NULL,
`date` integer NOT NULL
);Add index.ts file to the src/db folder
Create a new file index.ts in the src/db folder and add the following code:
import { drizzle } from "drizzle-orm/better-sqlite3";
import Database from "better-sqlite3";
import * as schema from "./schema";
// Initialize the SQLite database and export the connection
export const connection = new Database("sqlite.db");
// Create the database and export it
export const db = drizzle(connection, { schema });This code initializes the SQLite database connection and creates the Drizzle ORM database object using the schema we defined earlier. The db object can be used to interact with the database using the Drizzle ORM API.
We also export the connection object, which can be used to close the database connection when needed.
Add migrate.ts file to the src/db folder
Create a new file migrate.ts in the src/db folder and add the following code:
import { migrate } from "drizzle-orm/better-sqlite3/migrator";
import { db, connection } from "./index";
async function runMigrations() {
console.log("Running migrations...");
// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: "./drizzle" });
console.log("Migrations completed successfully.");
}
runMigrations()
.catch((e) => {
console.error("Migration failed:");
console.error(e);
})
.finally(() => {
// Don't forget to close the connection, otherwise the script will hang
connection.close();
});This code runs the migrations on the database using the Drizzle ORM migrator. It applies the migrations in the drizzle folder.
Add db:migrate script to package.json
Add the following script to the scripts section of the package.json file:
{
"scripts": {
"db:migrate": "tsx src/db/migrate.ts"
}
}This script will run our migrations on the database.
Run the migrations
Run the following command to apply the migrations to the database:
pnpm run db:migrateThis will create the sqlite.db file in the project’s root directory (api folder) and apply the migrations to create the posts table.
Open the database with drizzle-kit studio
Drizzle Kit provides a studio for visualizing the database schema. Add the following script to the scripts section of the package.json file:
{
"scripts": {
"db:studio": "drizzle-kit studio"
}
}Run the following command to open the database with Drizzle Kit Studio:
pnpm run db:studioYou should see the posts table in the Drizzle Kit Studio interface.
Add db:push script to package.json
The general workflow for managing the database schema is to generate the migration files and then apply the migrations. However, during the early stages of development, you may want to push the changes directly to the database without generating migration files. This can speed up the development process.
Drizzle Kit provides a push command that applies the changes directly to the database without generating migration files. Add the following script to the scripts section of the package.json file:
{
"scripts": {
"db:push": "drizzle-kit push"
}
}In later tasks, we will use this script to push changes to the database without generating migration files.
Step 7: Seed the database with sample data
In this step, we will seed the database with sample data. Seeding is the process of populating the database with initial data. This is useful for testing and development purposes.
Add seed.ts file to the src/db folder
import { db, connection } from "./index";
import { posts } from "./schema";
async function seed() {
console.log("Seeding the database...");
// Clean the tables
console.log("Cleaning existing data...");
await db.delete(posts);
console.log("Inserting new seed data...");
// Insert sample posts
const [post1] = await db
.insert(posts)
.values({
content: "Do only what only you can do.",
date: new Date(),
})
.returning({ id: posts.id });
const [post2] = await db
.insert(posts)
.values({
content:
"Elegance is not a dispensable luxury but a factor that decides between success and failure.",
date: new Date(),
})
.returning({ id: posts.id });
const [post3] = await db
.insert(posts)
.values({
content:
"The question of whether computers can think is like the question of whether submarines can swim.",
date: new Date(),
})
.returning({ id: posts.id });
console.log("Seeding completed successfully.");
}
seed()
.catch((e) => {
console.error("Seeding failed:");
console.error(e);
})
.finally(() => {
connection.close();
});This code seeds the posts table with three sample posts. Each post has a content field and a date field. The date field is set to the current date and time.
Make note of the following:
-
We start by cleaning the existing data in the
poststable. This is useful when you want to reset the database to its initial state. -
When inserting rows in the database, the
returningmethod can be used to get them back.await db.insert(users).values({ name: "Dan" }).returning();The
returningmethod returns an array of the inserted rows. This is because we can insert multiple rows at once.await db.insert(users).values([{ name: 'Andrew' }, { name: 'Dan' }]);The
returningmethod can also be used to get specific columns back.await db.insert(users).values({ name: "Partial Dan" }).returning({ insertedId: users.id });In our case, we are using the
returningmethod to get theidof the inserted post. This can be useful when you need to reference the newly inserted row in subsequent operations. We will use this feature in later tasks when we add comments to the posts. -
The
datefield is set to the current date and time usingnew Date(). This is a common practice when working with timestamps in databases. -
The
finallyblock ensures that the database connection is closed after the seeding is completed. This is important to prevent the script from hanging and to release the database resources.
Add db:seed script to package.json
Add the following script to the scripts section of the package.json file:
{
"scripts": {
"db:seed": "tsx src/db/seed.ts"
}
}This script will seed the database with sample data. You can run this when you want to populate the database with initial data.
Run the seed script
Run the following command to seed the database with sample data:
pnpm run db:seedYou should see the following output:
Seeding the database...
Cleaning existing data...
Inserting new seed data...
Seeding completed successfully.Next, open the database with drizzle-kit studio after seeding to verify that the sample data has been inserted successfully.
Conclusion
In this task, you learned how to add persistence to your API by setting up a database and an ORM library. You set up a SQLite database and used Drizzle ORM to interact with the database. You learned about database schema, migrations, and seeding. You also learned how to generate migration files, apply migrations, and seed the database with sample data.