Getting Started

Almost all applications require a database. Databases allow us to easily and efficiently store and retrieve data. To interact with databases, we need something called an ORM, which serves as a bridge between the application and the database.

In Milkio, Drizzle is the preferred ORM. It is the only ORM that provides both a relational query API and a SQL-like query API, offering the best of both worlds when accessing relational data.

Why Use Drizzle?

Databases use SQL as a language, and while it is simple to use on its own, writing SQL in our code can be cumbersome. SQL is a string without syntax highlighting, auto-completion, and error prompts, making the writing process painful. Additionally, we are prone to writing code vulnerable to SQL injection attacks. Drizzle and other ORM libraries are designed to address these issues, making it easier for us to interact with databases.

Getting Started

Before using Drizzle, you need to install Drizzle and its dependencies, which vary depending on the database you are using. You can refer to the Drizzle documentation for guidance on using your specific database. Below are examples for using MySQL and PostgreSQL. If you are using database services from serverless providers that are compatible with MySQL or PostgreSQL, it’s still recommended to read the Drizzle documentation to understand the best way to combine them.

npm install drizzle-orm mysql2 # MySQL

npm install drizzle-orm @vercel/postgres # PostgreSQL

Milkio and Drizzle

You also need to install the packages required for Milkio to work with Drizzle in order to simplify your Drizzle development experience.

npm install milkio-drizzle

Drizzle requires us to consolidate all database tables. Thanks to the generation phase in Milkio, we can separate the database tables without manually writing a file to combine all database tables.

In your /milkio.toml file, add the following command under the significant section in the generate configuration. This way, after each file modification, Milkio will automatically read the /src/databases directory and merge all .ts files into /generated/database-schema.ts.

[generate]
significant = ['./node_modules/milkio-drizzle/g.ts']

Additionally, ensure that database-schema.ts always exists. Paste the following at the end of your milkio.toml:

[exists.milkio-drizzle]
path = '/generated/database-schema.ts'
content = ''

Database Configuration

Fill in the database connection information in the /.env file. If you are unsure about what information to include, consult your database provider.

DB_HOST=your-database-host
DB_PORT=your-database-port
DB_USERNAME=your-database-username
DB_PASSWORD=your-database-password
DB_DATABASE=your-database-name

Next, write the database connection information. Create a drizzle.ts file in the /src/config directory.

import { env } from "node:process";
import { envToBoolean, envToNumber, envToString } from "milkio";

export const configDrizzle = {
  dbHost: envToString(env.DB_HOST, "your-default-database-host"),
  dbPort: envToNumber(env.DB_PORT, 3306),
  dbUsername: envToString(env.DB_USERNAME, "your-default-username"),
  dbPassword: envToString(env.DB_PASSWORD, "your-default-password"),
  dbDatabase: envToString(env.DB_DATABASE, "your-default-database"),
};

Create a drizzle.ts file in your /src/uses directory and paste the following code. The example code is for MySQL, and you may need to modify it based on the database you are using or the serverless provider you are using. Essentially, you are integrating Drizzle into the Use section of Milkio.

// For MySQL

// @ts-ignore
import * as schema from "../../generated/database-schema";
import { configDrizzle } from "../config/drizzle";
import { drizzle } from "drizzle-orm/mysql2";
import mysql from "mysql2/promise";
import { defineUse } from "milkio";

export const useDrizzle = defineUse(async () => {
  const connection = await mysql.createConnection({
    host: configDrizzle.dbHost,
    port: configDrizzle.dbPort,
    user: configDrizzle.dbUsername,
    password: configDrizzle.dbPassword,
    database: configDrizzle.dbDatabase,
  });
  return drizzle(connection, { schema, mode: "default" });
});

The above code is for MySQL. If you are using PostgreSQL, you can use the following code:

// For PostgreSQL

// Todo..

Why is NoSQL not Milkio’s preference?

The main advantage of relational databases like MySQL and PostgreSQL is their strong typing. Just like using TypeScript instead of JavaScript, types help us make fewer errors and make our code more understandable to other developers.

The biggest pain point of using relational databases is writing SQL and thinking in terms of left join to query data from related tables. Drizzle and other ORM libraries were created to solve these issues, making them a non-issue now.

Furthermore, serverless platforms almost exclusively offer relational databases, such as Vercel’s Vercel Postgres, Cloudflare’s D1, and TencentCloud’s TDSQL-C.

After trying Drizzle, you may likely change your perspective on relational databases. We have dedicated a chapter to help those with zero SQL knowledge quickly get started with Drizzle. You can read the Usage chapter later.