aliases

23 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

Use the `alias` function from 'drizzle-orm/sqlite-core' to create an alias of a table. Pass the table and the alias name as a string. This is useful for joining the same table multiple times, such as in self-referencing relationships. Example: `const manager = alias(employees, 'manager');` creates an alias of the employees table named 'manager' that can be used in joins.

Column aliasing with .as() method

Use the `.as()` method on columns to rename them in query results. This maps to the SQL AS keyword. Example: `users.name.as('lower_name')` renames the name column to lower_name in the result.

Column aliasing with sql expressions

Use `.as()` with `sql` expressions to alias computed columns. Example: `sql<string>`lower(${users.name})`.as('lower_name')` creates an alias for a SQL function result.

Subquery aliasing requirement

When using a subquery as a data source, you must provide an alias using `.as()` with a string name. This allows you to reference the subquery's columns in the outer query. Example: `db.select().from(users).where(eq(users.id, 42)).as('sq')`.

Subquery in joins

Subqueries can be used as a join target in leftJoin and other join methods. The subquery must have an alias created with `.as()` before it can be joined.

CTE aliasing with db.$with()

Create a Common Table Expression alias using `db.$with('aliasName')`. Pass the alias name as a string. Then chain `.as()` with the CTE definition. Example: `const sq = db.$with('sq').as(db.select().from(users).where(eq(users.id, 42)));`.

CTE reference in main query

Reference a CTE in the main query by calling `db.with(sq)` before the select statement, where `sq` is the CTE created with `db.$with()`. Example: `await db.with(sq).select().from(sq);`.

sql expressions in CTEs require aliasing

When using `sql` expressions inside a CTE select, you must alias them with `.as()` to be able to reference them in the outer query. Example: `sql<string>`upper(${users.name})`.as('name')`.

Table aliasing example with self-join

Example showing table aliasing for self-referencing relationships: import { alias, sqliteTable, integer, text } from 'drizzle-orm/sqlite-core'; const employees = sqliteTable('employees', { id: integer().primaryKey({ autoIncrement: true }), name: text(), managerId: integer('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 and usage: 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);

CTE with sql expression example

Example of CTE with aliased sql expressions: const sq = db.$with('sq').as(db.select({ name: sql<string>`upper(${users.name})`.as('name'), }).from(users)); const result = await db.with(sq).select({ name: sq.name }).from(sq);

Create insert schema from Drizzle table

Use createInsertSchema(table) to generate a validation schema for INSERT operations from a Drizzle table. The schema can be used to validate API requests before inserting data.

Create select schema from Drizzle table

Use createSelectSchema(table) to generate a validation schema for SELECT queries from a Drizzle table. The schema can be used to validate API responses.

Create update schema from Drizzle table

Use createUpdateSchema(table) to generate a validation schema for UPDATE operations from a Drizzle table. The schema can be used to validate API requests before updating data.

effect-schema library purpose and features

The effect-schema library generates effect schemas from Drizzle ORM schemas. It supports creating select schemas for tables and views, and creating insert and update schemas for tables. The supported dialect is SQLite.

Override fields in effect-schema

When creating insert, update, or select schemas, you can override specific fields by passing an object as the second argument to createInsertSchema, createUpdateSchema, or createSelectSchema. For example: createInsertSchema(users, { role: Schema.String }) overrides the role field.

Refine fields in effect-schema with check

You can refine schema fields using a callback function that receives the schema and returns a modified schema. For example: createInsertSchema(users, { id: (schema) => schema.check(Schema.isGreaterThanOrEqualTo(0)) }) adds validation that id must be greater than or equal to 0.

Effect schema with table declaration example

Example showing how to declare a SQLite table and generate insert, update, and select schemas using effect-schema: ```ts import { integer, sqliteTable, text } from 'drizzle-orm/sqlite-core'; import { createInsertSchema, createSelectSchema, createUpdateSchema } from 'drizzle-orm/effect-schema'; import { Effect, Schema } from 'effect'; const users = sqliteTable('users', { id: integer().primaryKey({ autoIncrement: true }), name: text().notNull(), email: text().notNull(), role: text({ enum: ['admin', 'user'] }).notNull(), createdAt: integer('created_at', { mode: 'timestamp' }).notNull(), }); const UserInsert = createInsertSchema(users); const UserUpdate = createUpdateSchema(users); const UserSelect = createSelectSchema(users); const program = Effect.gen(function*() { const parsedUser = yield* Schema.decodeUnknownEffect(UserInsert)({ name: 'John Doe', email: 'johndoe@test.com', role: 'admin', }); }); ```

Column aliases for different TypeScript and database names

To use different names in TypeScript code and the database, pass the database column name as a string argument to the column type function. For example, `firstName: text('first_name')` maps the TypeScript property `firstName` to the database column `first_name`.

Automatic camelCase to snake_case mapping

Drizzle provides `snakeCase` and `camelCase` builders from `drizzle-orm/sqlite-core` to automatically map naming conventions. Use `snakeCase.table()` or `snakeCase.view()` to automatically convert camelCase TypeScript properties to snake_case database columns. For example, `fullName` becomes `full_name` in the database.

Column aliases via .as() method

Columns can now be aliased using the `.as()` method in queries. Example: `db.select({ age: users.age.as('ageOfUser'), id: users.id.as('userId') }).from(users)`. The `.as()` method works in SELECT clauses and can also be used in other parts of queries like `orderBy(asc(users.id.as('userId')))`.

Give your agent this brain