database-patterns

Name: database-patterns
Author: softspark

161stars21forks

How to install database-patterns?

softspark/ai-toolkit/database-patterns

$npx -y skills add softspark/ai-toolkit --skill database-patterns

Installs into the current project.

Summary

DB schema design and query tuning: normalization, indexing, N+1, transactions, EXPLAIN. Triggers: schema, index, slow query, N+1, PostgreSQL, MySQL, EXPLAIN, deadlock, query plan.

Files · 1

View on GitHub

SKILL.md

1	# Database Patterns Skill
2
3	## ORM Selection
4
5	\| Scenario \| ORM \|
6	\|----------\|-----\|
7	\| Node.js, type-safe \| Prisma \|
8	\| Node.js, SQL-first \| Drizzle \|
9	\| Python, async \| SQLAlchemy 2.0 \|
10	\| Python, simple \| SQLModel \|
11	\| PHP \| Doctrine, Eloquent \|
12
13	---
14
15	## Schema Design
16
17	### Naming Conventions
18
19	```sql
20	-- Tables: plural, snake_case
21	CREATE TABLE user_profiles (...);
22
23	-- Columns: snake_case
24	user_id, created_at, is_active
25
26	-- Indexes: idx_{table}_{columns}
27	CREATE INDEX idx_users_email ON users(email);
28
29	-- Foreign keys: fk_{table}_{ref_table}
30	CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
31	```
32
33	### Common Patterns
34
35	#### Soft Delete
36	```sql
37	ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
38
39	-- Query active records
40	SELECT * FROM users WHERE deleted_at IS NULL;
41	```
42
43	#### Audit Columns
44	```sql
45	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
46	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
47	created_by UUID REFERENCES users(id),
48	updated_by UUID REFERENCES users(id)
49	```
50
51	#### UUID vs Serial
52	\| Use Case \| Type \|
53	\|----------\|------\|
54	\| Internal only \| SERIAL/BIGSERIAL \|
55	\| External/distributed \| UUID \|
56	\| Human readable \| SERIAL with prefix \|
57
58	---
59
60	## Index Strategies
61
62	### When to Index
63	- Foreign keys (always)
64	- Columns in WHERE clauses
65	- Columns in ORDER BY
66	- Columns in JOIN conditions
67
68	### Index Types
69	\| Type \| Use Case \|
70	\|------\|----------\|
71	\| B-tree \| Equality, range (default) \|
72	\| Hash \| Equality only \|
73	\| GIN \| Arrays, JSONB, full-text \|
74	\| GiST \| Geometric, full-text \|
75	\| BRIN \| Large sequential data \|
76
77	### Composite Index Order
78	```sql
79	-- Good: matches query pattern
80	CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
81	SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';
82
83	-- Index used for:
84	-- WHERE user_id = 1
85	-- WHERE user_id = 1 AND created_at > ...
86
87	-- Index NOT used for:
88	-- WHERE created_at > '2024-01-01' (missing leading column)
89	```
90
91	---
92
93	## Query Optimization
94
95	### Explain Analyze
96	```sql
97	EXPLAIN ANALYZE
98	SELECT * FROM users WHERE email = 'test@example.com';
99	```
100
101	### Common Issues
102
103	\| Issue \| Solution \|
104	\|-------\|----------\|
105	\| Seq Scan on large table \| Add index \|
106	\| High row estimate \| Update statistics \|
107	\| Nested Loop on large sets \| Consider hash join \|
108	\| Sort in memory \| Increase work_mem \|
109
110	### N+1 Prevention
111	```python
112	# Bad: N+1
113	for user in users:
114	print(user.orders) # Query per user
115
116	# Good: Eager loading
117	users = User.query.options(joinedload(User.orders)).all()
118	```
119
120	---
121
122	## Migration Best Practices
123
124	### Safe Migrations
125	```sql
126	-- Add column (safe)
127	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
128
129	-- Add NOT NULL column (safe pattern)
130	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
131	UPDATE users SET phone = '' WHERE phone IS NULL;
132	ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
133
134	-- Rename column (use application-level)
135	-- 1. Add new column
136	-- 2. Copy data
137	-- 3. Update application
138	-- 4. Remove old column
139	```
140
141	### Migration Checklist
142	- [ ] Tested on production-like data
143	- [ ] Rollback script ready
144	- [ ] No long locks on large tables
145	- [ ] Indexes created concurrently
146	- [ ] Application handles both states
147
148	---
149
150	## Connection Pooling
151
152	### PgBouncer Settings
153	```ini
154	[pgbouncer]
155	pool_mode = transaction
156	max_client_conn = 1000
157	default_pool_size = 20
158	```
159
160	### Application Settings
161	\| Framework \| Pool Size Formula \|
162	\|-----------\|------------------\|
163	\| General \| (cores * 2) + disk spindles \|
164	\| Read-heavy \| cores * 4 \|
165	\| Write-heavy \| cores * 2 \|
166
167	---
168
169	## Vector Database (Qdrant) Patterns
170
171	### Client Setup
172	```python
173	from qdrant_client import QdrantClient
174	from qdrant_client.models import Distance, VectorParams, PointStruct
175
176	# Sync client
177	client = QdrantClient(host="localhost", port=6333)
178
179	# Async client
180	from qdrant_client import AsyncQdrantClient
181	async_client = AsyncQdrantClient(host="localhost", port=6333)
182	```
183
184	### Collection Management
185	```python
186	# Create collection (single vector)
187	client.create_collection(
188	collection_name="documents",
189	vectors_config=VectorParams(size=384, distance=Distance.COSINE)
190	)
191
192	# Create collection (multi-vector)
193	from qdrant_client.models import VectorParams
194
195	client.create_collection(
196	collection_name="multimodal",
197	vectors_config={
198	"text": VectorParams(size=384, distance=Distance.COSINE),
199	"image": VectorParams(size=512, distance=Distance.EUCLID),
200	}
201	)
202	```
203
204	### Upserting Vectors
205	```python
206	# Single upsert
207	client.upsert(
208	collection_name="documents",
209	points=[
210	PointStruct(
211	id=1,
212	vector=[0.1, 0.2, 0.3, ...], # 384-dim vector
213	payload={"title": "Doc 1", "category": "tech"}
214	)
215	]
216	)
217
218	# Batch upsert
219	points = [
220	PointStruct(id=i, vector=vectors[i], payload=payloads[

People who liked this also viewed

6 picks

Type

azure-storageAzure Storage Services including Blob Storage, File Shares, Queue Storage, Table Storage, and Data Lake. Answers questions about storage access tiers (hot, cool, cold, archive), when to use each tier,SkillsJul 2026471k1.3k
supabase-postgres-best-practicesPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.SkillsJul 2026302k2.4k
supabaseUse when doing ANY task involving Supabase. Triggers: Supabase products (Database, Auth, Edge Functions, Realtime, Storage, Vectors, Cron, Queues); client libraries and SSR integrations (supabase-js, SkillsJul 2026179k2.4k
convex-quickstartCreates or adds Convex to an app. Use for new Convex projects, npm create convex@latest, frontend setup, env vars, or the first npx convex dev run.SkillsJul 202690k39
convex-performance-auditAudits Convex performance for reads, subscriptions, write contention, and function limits. Use for slow features, insights findings, OCC conflicts, or read amplification.SkillsJul 202689k39
convex-setup-authSets up Convex auth, identity mapping, and access control. Use for login, auth providers, users tables, protected functions, or roles in a Convex app.SkillsJul 202689k39

database-patterns

161stars21forks

View on GitHub

How to install database-patterns?

softspark/ai-toolkit/database-patterns

$npx -y skills add softspark/ai-toolkit --skill database-patterns

Installs into the current project.

Summary

DB schema design and query tuning: normalization, indexing, N+1, transactions, EXPLAIN. Triggers: schema, index, slow query, N+1, PostgreSQL, MySQL, EXPLAIN, deadlock, query plan.

Files · 1

View on GitHub

SKILL.md

1	# Database Patterns Skill
2
3	## ORM Selection
4
5	\| Scenario \| ORM \|
6	\|----------\|-----\|
7	\| Node.js, type-safe \| Prisma \|
8	\| Node.js, SQL-first \| Drizzle \|
9	\| Python, async \| SQLAlchemy 2.0 \|
10	\| Python, simple \| SQLModel \|
11	\| PHP \| Doctrine, Eloquent \|
12
13	---
14
15	## Schema Design
16
17	### Naming Conventions
18
19	```sql
20	-- Tables: plural, snake_case
21	CREATE TABLE user_profiles (...);
22
23	-- Columns: snake_case
24	user_id, created_at, is_active
25
26	-- Indexes: idx_{table}_{columns}
27	CREATE INDEX idx_users_email ON users(email);
28
29	-- Foreign keys: fk_{table}_{ref_table}
30	CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
31	```
32
33	### Common Patterns
34
35	#### Soft Delete
36	```sql
37	ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
38
39	-- Query active records
40	SELECT * FROM users WHERE deleted_at IS NULL;
41	```
42
43	#### Audit Columns
44	```sql
45	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
46	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
47	created_by UUID REFERENCES users(id),
48	updated_by UUID REFERENCES users(id)
49	```
50
51	#### UUID vs Serial
52	\| Use Case \| Type \|
53	\|----------\|------\|
54	\| Internal only \| SERIAL/BIGSERIAL \|
55	\| External/distributed \| UUID \|
56	\| Human readable \| SERIAL with prefix \|
57
58	---
59
60	## Index Strategies
61
62	### When to Index
63	- Foreign keys (always)
64	- Columns in WHERE clauses
65	- Columns in ORDER BY
66	- Columns in JOIN conditions
67
68	### Index Types
69	\| Type \| Use Case \|
70	\|------\|----------\|
71	\| B-tree \| Equality, range (default) \|
72	\| Hash \| Equality only \|
73	\| GIN \| Arrays, JSONB, full-text \|
74	\| GiST \| Geometric, full-text \|
75	\| BRIN \| Large sequential data \|
76
77	### Composite Index Order
78	```sql
79	-- Good: matches query pattern
80	CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
81	SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';
82
83	-- Index used for:
84	-- WHERE user_id = 1
85	-- WHERE user_id = 1 AND created_at > ...
86
87	-- Index NOT used for:
88	-- WHERE created_at > '2024-01-01' (missing leading column)
89	```
90
91	---
92
93	## Query Optimization
94
95	### Explain Analyze
96	```sql
97	EXPLAIN ANALYZE
98	SELECT * FROM users WHERE email = 'test@example.com';
99	```
100
101	### Common Issues
102
103	\| Issue \| Solution \|
104	\|-------\|----------\|
105	\| Seq Scan on large table \| Add index \|
106	\| High row estimate \| Update statistics \|
107	\| Nested Loop on large sets \| Consider hash join \|
108	\| Sort in memory \| Increase work_mem \|
109
110	### N+1 Prevention
111	```python
112	# Bad: N+1
113	for user in users:
114	print(user.orders) # Query per user
115
116	# Good: Eager loading
117	users = User.query.options(joinedload(User.orders)).all()
118	```
119
120	---
121
122	## Migration Best Practices
123
124	### Safe Migrations
125	```sql
126	-- Add column (safe)
127	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
128
129	-- Add NOT NULL column (safe pattern)
130	ALTER TABLE users ADD COLUMN phone VARCHAR(20);
131	UPDATE users SET phone = '' WHERE phone IS NULL;
132	ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
133
134	-- Rename column (use application-level)
135	-- 1. Add new column
136	-- 2. Copy data
137	-- 3. Update application
138	-- 4. Remove old column
139	```
140
141	### Migration Checklist
142	- [ ] Tested on production-like data
143	- [ ] Rollback script ready
144	- [ ] No long locks on large tables
145	- [ ] Indexes created concurrently
146	- [ ] Application handles both states
147
148	---
149
150	## Connection Pooling
151
152	### PgBouncer Settings
153	```ini
154	[pgbouncer]
155	pool_mode = transaction
156	max_client_conn = 1000
157	default_pool_size = 20
158	```
159
160	### Application Settings
161	\| Framework \| Pool Size Formula \|
162	\|-----------\|------------------\|
163	\| General \| (cores * 2) + disk spindles \|
164	\| Read-heavy \| cores * 4 \|
165	\| Write-heavy \| cores * 2 \|
166
167	---
168
169	## Vector Database (Qdrant) Patterns
170
171	### Client Setup
172	```python
173	from qdrant_client import QdrantClient
174	from qdrant_client.models import Distance, VectorParams, PointStruct
175
176	# Sync client
177	client = QdrantClient(host="localhost", port=6333)
178
179	# Async client
180	from qdrant_client import AsyncQdrantClient
181	async_client = AsyncQdrantClient(host="localhost", port=6333)
182	```
183
184	### Collection Management
185	```python
186	# Create collection (single vector)
187	client.create_collection(
188	collection_name="documents",
189	vectors_config=VectorParams(size=384, distance=Distance.COSINE)
190	)
191
192	# Create collection (multi-vector)
193	from qdrant_client.models import VectorParams
194
195	client.create_collection(
196	collection_name="multimodal",
197	vectors_config={
198	"text": VectorParams(size=384, distance=Distance.COSINE),
199	"image": VectorParams(size=512, distance=Distance.EUCLID),
200	}
201	)
202	```
203
204	### Upserting Vectors
205	```python
206	# Single upsert
207	client.upsert(
208	collection_name="documents",
209	points=[
210	PointStruct(
211	id=1,
212	vector=[0.1, 0.2, 0.3, ...], # 384-dim vector
213	payload={"title": "Doc 1", "category": "tech"}
214	)
215	]
216	)
217
218	# Batch upsert
219	points = [
220	PointStruct(id=i, vector=vectors[i], payload=payloads[

People who liked this also viewed

6 picks

Type