Grouparoo Docs
Databases
Last Updated: 2020-08-01This document is a collection of the guidelines Grouparoo uses when dealing with Models and Databases internally.
Grouparoo Migration Guidelines
- No sequential IDs. We use app-generated unique IDs for primary keys (UUIDs). We are working with large datasets and database that sometimes can't count.
- Every table gets
id
,createdAt
andupdatedAt
, even if it seems silly now... we will need it eventually! - No
enums
, useVARCHAR(191)
. Migrating an enum is too hard and database-specific. We can use the ENUM type in the model for validations - No default values for strings. Define defaults in the model. Do mark columns as non-null.
- No foreign key constraints in the database... do these checks in the model. But, it's probably a good idea to add a search index to these
- All guids/uuids are 40 characters long - a real V4 UUID (36 characters) + 3 letter prefix + underscore, ie:
app_168c4564-e389-4fbd-8338-db04d62022ba
- Do not use compound indexes for SQLite. There's a Seqeulzie bug
- Instead, do the uniqueness validation in the model in a
@BeforeSave
hook - You can continue to add compound indexes for Postgres (do so with a check against
config.seqeulize.dialect === 'postgres'
)
- Instead, do the uniqueness validation in the model in a
Grouparoo Model Guidelines
- No private methods. This breaks compatibility between Typescript-exported model definitions (to JS) and the TS files themselves. We require this compatibility as plugins both source and export to core... so a single runtime's require path may be
core/dist -> plugin/src
or evencore/dist -> plugin/dist -> core/src
- Instead, Pretend it's 1999 and prefix methods you want to be private with
_
, i.e.async _privateMethod()
- Instead, Pretend it's 1999 and prefix methods you want to be private with
- New models need to be enumerated a few places:
Index.ts
SpecHelper.ts
modules/plugin.ts
- In order to keep models smaller, we make the distinction between the Model itself and ModelOps
- Table/Column definitions, Class methods, Setters, Getters, and Validations belong on the Model
- Methods that mutate the Model (or more than one Model), or interact with a Plugin, belong in a ModelOp
- It is appropriate for convenience to have a Model method call out to a ModelOp (ie:
Profile.import
=>ProfileOpt.import(profile)
)
Querying
- As we are working with larger volumes of data, we cannot assume that our querying in batches (via
limit
&offset
) won't change out from under us. To that end, usingoffset
is a code smell. We should instead be ordering by some non-changing column (id
,createdAt
, etc) and storing the highest value as ahighWaterMark
for the next subsequent query.
-- bad, what if updated_at changed for some of the users as we query?
select * from users order by updated_at asc limit 100 offset 100
-- good
select * from users order by uuid asc limit 100;
-- next time
select * from users where uuid >= previousValue order by uuid asc limit 100;
- Do not order by
rand()
orrandom()
. It's very slow on large datasets. There are other alternatives possible, but may require more than one query.
Other Notes
- If you are loading a model in an initializer in the
@grouparoo/core
project, you need to load it from Core's main export, not the Model file itself. ie:import {Profile} from '../index'
notimport {Profile} from '../models/Profile'
.
Having Problems?
If you are having trouble, visit the list of common issues or open a Github issue to get support.