mysql connection & drivers

27 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

dbCredentials config option for MySQL

For MySQL dialect, dbCredentials can be configured via URL string (format: 'mysql://user:password@host:port/db') or via connection parameters object with properties: host (string), port (number), user (string), password (string), database (string), ssl (boolean or string 'require'/'allow'/'prefer'/'verify-full' or ssl options from mysql2 package). Used in commands: push, pull, migrate, studio.

dbCredentials config option - general

The dbCredentials option specifies database connection credentials as either a URL string (format: 'dialect://user:password@host:port/db') or as individual connection parameters, or dialect-specific connection options. No default value. Used in commands: push, pull, migrate, studio.

mysql2 package installation

The mysql2 package is required for querying a MySQL database when using Drizzle ORM.

mysql2 driver for Drizzle with MySQL

Drizzle ORM natively supports the mysql2 driver through the drizzle-orm/mysql2 package. According to the official website, mysql2 is a MySQL client for Node.js with focus on performance. To use Drizzle with a MySQL database, you should use the mysql2 driver.

Prerequisites for Drizzle MySQL setup

To get started with Drizzle and MySQL, you need three prerequisite packages: dotenv for managing environment variables, tsx for running TypeScript files, and mysql2 for querying your MySQL database.

Getting started with Drizzle MySQL workflow

The basic workflow for setting up Drizzle with MySQL consists of eight steps: (1) Install mysql2 package, (2) Setup connection variables with DATABASE_URL, (3) Connect Drizzle ORM to the database, (4) Create a table, (5) Setup Drizzle config file with mysql dialect, (6) Apply changes to the database, (7) Seed and query the database, and (8) Run the index.ts file.

Pull MySQL Docker image

To pull the latest MySQL image from Docker Hub, run `docker pull mysql`. To pull a specific version, use `docker pull mysql:8.2` with the desired version tag.

Start MySQL container with docker run

To start a MySQL container, run `docker run --name drizzle-mysql -e MYSQL_ROOT_PASSWORD=mypassword -d -p 3306:3306 mysql`. The --name option assigns the container name. The -e MYSQL_ROOT_PASSWORD= sets the root password. The -d flag runs in detached mode. The -p option maps port 3306 from container to host. The mysql argument specifies the image to use.

MySQL docker run optional parameters

When using docker run, you can optionally specify: -e MYSQL_DATABASE= to create a new database when the container is created (default is mysql), -e MYSQL_USER= and -e MYSQL_PASSWORD= to create a new user with a password. The MYSQL_ROOT_PASSWORD must still be specified for the root user.

MySQL connection string format

The MySQL connection string format is mysql://<user>:<pa••••••d>@<host>:<port>/<database>. For a local Docker container with the example settings, the URL would be mysql://root:myp••••••rd@localhost:3306/mysql.

Check running Docker containers

To verify if a MySQL container is running, use the `docker ps` command or check the Containers tab in Docker Desktop.

Check pulled Docker images

To see downloaded images, run `docker images` command or check the Images tab in Docker Desktop.

MySQL supports update many with different values

MySQL is supported for the update many with different values pattern using CASE statements and sql operators.

Relational queries mode config for mysql2 driver

Drizzle relational queries require a mode configuration when using the mysql2 driver. When using mysql2 with a regular MySQL database, specify mode: 'default'. When using mysql2 with PlanetScale, specify mode: 'planetscale'. PlanetScale does not support lateral joins of subqueries, which is why a separate mode is required.

mysql2 driver with PlanetScale configuration example

This example shows how to configure Drizzle with the mysql2 driver for PlanetScale: import { drizzle } from 'drizzle-orm/mysql2'; import mysql from 'mysql2/promise'; import * as schema from './schema'; const client = await mysql.createConnection({ uri: process.env.PLANETSCALE_DATABASE_URL, }); const db = drizzle({ client, schema, mode: 'planetscale' });

MySQL timestamp milliseconds truncation fixed

In Drizzle ORM v0.28.2, a bug was fixed that was truncating timestamp milliseconds for MySQL. Timestamps will now preserve milliseconds correctly.

MySQL Proxy Driver in v0.29.0

A new MySQL proxy driver is available in v0.29.0, allowing custom HTTP driver implementation. Import with: import { drizzle } from 'drizzle-orm/mysql-proxy' and import { migrate } from 'drizzle-orm/mysql-proxy/migrator'. The driver accepts a callback: drizzle(async (sql, params, method) => { ... }). Implementation examples are in ./examples/mysql-proxy folder. Requires server endpoints for queries and optional migrate endpoint.

PlanetScale Client instance preferred over connect() function

As of Drizzle v0.29.4, you should use the Client instance from @planetscale/database instead of the connect() function when configuring PlanetScale connections. The connect() function is deprecated in v0.29.4 and will cause an error starting from v0.30.0.

PlanetScale Client instance configuration example

Create a PlanetScale connection using the Client instance with the following pattern: ```ts import { Client } from '@planetscale/database'; import { drizzle } from 'drizzle-orm/planetscale-serverless'; const client = new Client({ host: process.env['DATABASE_HOST'], username: process.env['DATABASE_USERNAME'], password: process.env['DATABASE_PASSWORD'], }); const db = drizzle(client); ```

Deprecation warning when using PlanetScale connect() function

In Drizzle v0.29.4, using the connect() function from @planetscale/database produces a warning. The warning message states that you need to pass an instance of Client instead, and that starting from version 0.30.0, an error will be thrown if anything other than a Client instance is used.

postgres.js driver date handling in v0.30.0

In Drizzle ORM v0.30.0, the postgres.js driver instance was modified to always return strings for dates. Drizzle then provides either strings or mapped dates depending on the selected mode. When you pass a postgres.js driver instance to Drizzle, the behavior of that object changes for dates, which will always be strings. When mapping to the driver, Drizzle will always use .toISOString for both timestamps with timezone and without timezone.

postgres.js parser modification for date types in Drizzle

Drizzle v0.30.0 overrides postgres.js default date parsers for type IDs 1184, 1082, 1083, and 1114 (corresponding to timestamp with time zone, date, time, and timestamp without time zone respectively) by setting both client.options.parsers and client.options.serializers to a transparent parser function that returns values unchanged.

Breaking change in postgres.js timestamp behavior

When using postgres.js driver with Drizzle v0.30.0, if you were using timestamps and expecting a specific response format, the behavior has changed. All postgres.js clients passed to Drizzle will have mutated behavior for dates, with all dates returned as strings in the response.

AWS DataAPI sessions .all, .values, .execute functions fixed

Drizzle ORM v0.30.10 fixed internal mappings for the .all(), .values(), and .execute() functions in AWS DataAPI sessions.

Connect to Xata with xata-http driver

To connect to Xata using the xata-http driver, import drizzle from 'drizzle-orm/xata-http' and pass the Xata client (obtained from the xata init CLI command) to the drizzle function. Example: const db = drizzle(getXataClient());

v0.32.2 bug fix: set transactions in MySQL

Version 0.32.2 includes a bug fix for set transactions in MySQL.

MSSQL connection with options

To connect to MSSQL with specific options in Drizzle: import { drizzle } from 'drizzle-orm/node-mssql'; const db = drizzle({ connection: { connectionString: process.env.DATABASE_URL, ssl: true } });

Give your agent this brain