sqlite-core database design

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

Normalization concept: reducing redundancy and improving data integrity

Normalization is the process of organizing data in a database to reduce redundancy (duplication) and improve data integrity (accuracy and consistency). It involves organizing information into logical folders and categories to make data easier to find and manage.

Benefits of normalization: prevents anomalies and redundancy

Normalization reduces data redundancy, improves data integrity, and prevents anomalies including insertion anomalies (difficulty adding new data due to missing related information), update anomalies (having to update the same information in multiple rows), and deletion anomalies (accidentally losing valuable information when deleting something seemingly unrelated).

First Normal Form (1NF): atomic values

First Normal Form (1NF) requires that each column should hold a single, indivisible value with no repeating groups of data within a single cell. For example, an address column storing '123 Main St, City, USA' should be broken down into separate columns: street_address, city, state, zip_code.

Second Normal Form (2NF): eliminate redundant data dependent on part of composite key

Second Normal Form (2NF) applies when a table has a composite primary key (made up of two or more columns). 2NF ensures that all non-key attributes are fully dependent on the entire composite primary key, not just part of it. Partially dependent attributes should be removed and placed in a separate table.

Third Normal Form (3NF): eliminate transitive dependencies

Third Normal Form (3NF) removes data that is dependent on other non-key attributes, eliminating transitive dependencies. Attributes dependent on non-key attributes should be placed into a separate table keyed by the non-key attribute itself.

One-to-One database relationship definition

In a one-to-one relationship, each record in table A is related to at most one record in table B, and each record in table B is related to at most one record in table A. It is a direct, exclusive pairing.

One-to-Many database relationship definition

In a one-to-many relationship, one record in table A can be related to many records in table B, but each record in table B is related to at most one record in table A. This is a parent-child relationship where the child table contains a foreign key referencing the parent table.

Many-to-Many database relationship definition and junction table

In a many-to-many relationship, one record in table A can be related to many records in table B, and one record in table B can be related to many records in table A. This is implemented using a junction table (also called an associative table or bridging table) that acts as an intermediary to link records from both tables.

Many-to-Many junction table example with composite foreign keys

A many-to-many junction table example: CREATE TABLE enrollments (id INTEGER PRIMARY KEY AUTOINCREMENT, student_id INTEGER, course_id INTEGER, enrollment_date TEXT, FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id), UNIQUE (student_id, course_id));

Foreign key constraints: defining and enforcing relationships

Foreign key constraints explicitly define and enforce relationships in SQL. A foreign key tells the database to enforce a relationship rule: every value in a foreign key column must correspond to a valid value in the primary key column of another table.

Referential integrity: maintaining consistency through foreign keys

Referential integrity means that relationships between tables remain consistent and valid over time. Foreign keys prevent orphaned records (records in one table without a corresponding record in the related table). This maintains the logical structure of data and prevents queries and reports from becoming unreliable.

Foreign keys as database documentation

Foreign keys serve as crucial documentation in database schema design. When you see a foreign key, it immediately tells you that Table X is related to Table Y in a specific way. This makes databases easier to understand, maintain, and evolve over time.

When to avoid or use foreign keys with caution

Foreign keys may introduce performance overhead in very high-write environments where the database must perform referential integrity checks on every insert or update. In distributed database systems, cross-node foreign keys can introduce significant complexity and performance overhead requiring communication between nodes. In legacy systems or when integrating with non-relational data, foreign keys may cause data import issues and might require application-level integrity checks instead.

Polymorphic relationships definition

Polymorphic relationships allow a single relationship to point to different types of entities or tables. They create more flexible relationships when different kinds of data share some commonality. Standard SQL does not have direct, built-in support for enforcing polymorphic foreign key constraints in the same way as regular foreign keys.

Polymorphic relationships example: comments related to multiple entity types

A polymorphic relationship example is a Comments table where a comment can be related to different types of content: articles, products, or videos. Instead of having separate article_id, product_id, video_id columns, you use commentable_type and commentable_id columns to identify the type and ID of the related entity.

TypeScript key names match database column names by default

By default in Drizzle, TypeScript property names are used as database column names in SQL queries. For example, a property named `first_name` will generate a column reference `"first_name"` in SQL.

SQLite has no schema concept

In SQLite, there is no concept of a schema like in PostgreSQL or MySQL. Tables are defined within a single SQLite file context.

Reusable column definitions with TypeScript spread operator

Common columns like `updated_at`, `created_at`, and `deleted_at` can be defined separately in a helper file and then spread into multiple table definitions using the TypeScript spread operator. For example, define an object with these columns and use `...timestamps` when creating tables.

Table definition requires at least one column

A table in Drizzle must be defined with at least one column, the same requirement as in the underlying database.

Use sqliteTable for SQLite dialect

When defining tables for SQLite, use `sqliteTable()` from `drizzle-orm/sqlite-core`. Drizzle requires choosing the correct dialect (PostgreSQL, MySQL, SQLite, etc.) when defining tables; there is no common table object across dialects.

Foreign key reference syntax

To create a foreign key reference, use `.references()` with an arrow function that returns the referenced column. For example, `invitee: integer().references(() => users.id)` creates a foreign key to the users table's id column.

Unique index definition

Unique indexes are defined in the table's constraints section (third parameter) using `t.uniqueIndex('index_name').on(table.column)`. For example, `t.uniqueIndex('email_idx').on(table.email)` creates a unique index on the email column.

Regular index definition

Regular indexes are defined in the table's constraints section using `t.index('index_name').on(table.column)`. For example, `t.index('title_idx').on(table.title)` creates an index on the title column.

Example SQLite schema with users, posts, and comments

A complete example schema definition: ```ts import * as t from "drizzle-orm/sqlite-core"; export const users = t.sqliteTable( "users", { id: t.integer().primaryKey({ autoIncrement: true }), firstName: t.text("first_name"), lastName: t.text("last_name"), email: t.text().notNull(), invitee: t.integer().references(() => users.id), role: t.text().$type<"guest" | "user" | "admin">().default("guest"), }, (table) => [ t.uniqueIndex("email_idx").on(table.email) ] ); export const posts = t.sqliteTable( "posts", { id: t.integer().primaryKey({ autoIncrement: true }), slug: t.text().$default(() => generateUniqueString(16)), title: t.text(), ownerId: t.integer("owner_id").references(() => users.id), }, (table) => [ t.uniqueIndex("slug_idx").on(table.slug), t.index("title_idx").on(table.title), ] ); export const comments = t.sqliteTable("comments", { id: t.integer().primaryKey({ autoIncrement: true }), text: t.text({ length: 256 }), postId: t.integer("post_id").references(() => posts.id), ownerId: t.integer("owner_id").references(() => users.id), }); ```

sqliteView function usage

The sqliteView function is imported from 'drizzle-orm/sqlite-core' and is used to declare views. It takes the view name as a string and optionally column definitions. It provides methods like .as() to define the view query and .existing() to mark views that already exist in the database.

Casing API moved to table-level in v1

The legacy `drizzle({ casing: 'camelCase' })` option has been removed. The new API uses `snakeCase.table()`, `camelCase.table()`, etc. at the table/view/schema level. Available methods include: snakeCase.table, snakeCase.view, snakeCase.materializedView, snakeCase.schema, camelCase.table, camelCase.view, camelCase.materializedView, and camelCase.schema. These are imported from 'drizzle-orm/pg-core'.

Give your agent this brain