aliases

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

Table aliasing with alias() function

Tables can be aliased using the alias() function imported from drizzle-orm/mysql-core. This is useful when joining the same table multiple times, such as self-referencing relationships. The alias function takes the table and a string alias name as arguments, for example: const manager = alias(employees, 'manager').

Column aliasing with .as() method

Columns can be aliased using the .as() method on column objects and sql expressions. This maps directly to the SQL AS keyword and lets you control the name of a column in the query result. For example: users.name.as('lower_name') will rename the column in the result.

Using .as() with sql expressions

The .as() method can be used on sql expressions to apply aliases. For example: sql<string>`lower(${users.name})`.as('lower_name') aliases a SQL function result.

Subquery aliasing requirement

When using a subquery as a data source, an alias must be provided using the .as() method. This allows you to reference the subquery's columns in the outer query. For example: db.select().from(users).where(eq(users.id, 42)).as('sq').

Subqueries in joins

Subqueries can be used as data sources in join operations. They must have an alias provided via .as() to be referenced in the join condition. For example: db.select().from(users).leftJoin(sq, eq(users.id, sq.id)) where sq is an aliased subquery.

CTE aliasing with db.$with()

Common table expressions (CTEs) are created and aliased using db.$with('alias').as(...). The alias name is used to reference the CTE in the main query. For example: db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))).

Aliasing sql expressions inside CTEs

When using sql expressions inside a CTE, they must be aliased with .as() to be referenceable in the outer query. For example: sql<string>`upper(${users.name})`.as('name') inside a CTE select allows the transformed column to be referenced as sq.name in the outer query.

Table aliasing example with self-referencing join

Example showing table aliasing for a self-referencing relationship: const employees = mysqlTable("employees", { id: int().autoincrement().primaryKey(), name: text(), managerId: int("manager_id"), }); const manager = alias(employees, "manager"); await db.select({ employeeName: employees.name, managerName: manager.name, }).from(employees).leftJoin(manager, eq(employees.managerId, manager.id));

Column aliasing example

Example of column aliasing: const result = await db.select({ id: users.id, lowerName: users.name.as("lower_name"), }).from(users);

Subquery aliasing example

Example of subquery aliasing: const sq = db.select().from(users).where(eq(users.id, 42)).as('sq'); const result = await db.select().from(sq);

CTE aliasing example

Example of CTE aliasing and usage: const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42))); const result = await db.with(sq).select().from(sq);

Table aliases with alias() function

Import alias from 'drizzle-orm/mysql-core' and use const parent = alias(user, 'parent') to create table aliases. This is needed for selfjoins. Use the alias in joins like .leftJoin(parent, eq(parent.id, user.parentId)).

Selfjoin with alias example

To fetch users with their parents: import { alias } from 'drizzle-orm/mysql-core'; const parent = alias(user, 'parent'); db.select().from(user).leftJoin(parent, eq(parent.id, user.parentId)). The generated SQL is 'select ... from `user` left join `user` `parent` on `parent`.`id` = `user`.`parent_id`'.

relationName parameter renamed to alias in v2

The relationName parameter for relations has been renamed to alias in Relational Queries v2.

Column aliases in MySQL schema

To use different names in TypeScript code versus the database, use column aliases. For example: `firstName: varchar('first_name', { length: 256 })` maps the TypeScript key 'firstName' to the database column 'first_name'.

Column aliases for different TypeScript and database names

To use different names in TypeScript code versus the database, pass the database name as a string parameter to the column type, e.g., varchar('first_name') creates a TypeScript property 'firstName' that maps to database column 'first_name'.

Give your agent this brain