It does mean that architectural decisions start in the database as well, which I am trying to govern with "schema linting" ([link redacted]). If you are using Postgres, I would love help with building this.
I'm also massively in favour of having the db as the source of truth. When I first started looking into building my own SaaS I actually tried to go down the database first design, where most of your functionality runs within postgres triggers. But it wasn't near as easy to do things and writing SQL logic is much harder to test.
Figured the next best thing is to just tie the database in extremely closely using typescript into the backend (I rarely define custom types, always try to use Pick/Omit/Partial combinations all the way to the client SDKs).
I ended building generating forms based on the meta data in postgres (select fields know the values from enums and multiselect from enum arrays), so if you define a table in postgres, you just specify which fields you want to render (out of the available columns) and in what order and it pretty much gives you a UI form that is directly rendered in react. Works really well when you have a form component library at your disposal.
For example:
entity,field,type,constraints
delivery,arrived_at,Date,{ before: 0 }
form,field_1,field_2,...
delivery_details,left_at,arrived_at,...
It's not a magical pill, renaming, deleting and custom migrations need to be dealt with manually. But for the most part 80%_of our changes are additions and this way it just works.
But, the jist is, we APIs like this (this can be improved with less generic json schemas):
export const updateSamf: APIFunction<UpdateSamf, void> = async ({ database }, { samfId, ...data }) => {
if (Object.keys(data).length === 0) {
throw new InvalidParametersError('No fields to update')
}
if (data.title && data.title.length < 3) {
throw new InvalidParametersError('Title needs to be at least 3 characters')
}
if (data.tags) {
data.tags = data.tags.map((t) => t.toLowerCase())
}
await database.crudUpdate<Samf>('samf', data, { samfId }, new SamfNotFoundError())
if (data.cover && previousCover) {
await content.delete(previousCover)
}
}
export const routes: APIRoutes = [{
type: 'patch',
route: StudioRoutePath.SAMF_CRUD,
func: updateSamf,
schema: 'UpdateSamf',
permissions: isSamfOwner
}]I usually run a postgres db (as a docker image on circleCI) when I'm running the build. I'm not sure how scalable it is (currently we have a hundred migrations and it's a few seconds so not bad).
The flow is:
- run migrations on an empty DB - create the interfaces and enums - run typescript - derive all the json schemas from the interfaces for API validation
This means we gitignore the actual DB Types which is great in order to reduce the amount of sources of truth and deal with possible issues
This all runs as a pre-commit hook as well. I guess by doing so we sort of validate the SQL is legit via postgres itself. The only issue we face after a commit gets passed the setup stage and tries to run the migration itself is if theres a data* inconsistency we weren't aware of (trying an enum value we thought wasn't used but was). But that sort of things requires special tasks anyways and the migration fails so nothing ever goes wierd (which is why I <3 SQL migrations )
Keep in mind that this might become problematic for folks that are running Postgres inside Docker on macOS or Windows, as IO performance is quite poor.
I put together a up / down migration validation system a while ago (start up two databases, apply all-1 up migrations on A and all up migrations + final down migration on B) for a pretty sizable schema.
Folks that were using Docker had to wait upwards of two minutes, while natively installed Postgres would finish in under 10sec.
As long as their database in docker is being incrementally updated (using the same migration scripts as on production which only apply new ones) is this hit only when spinning up a clean docker image?
I'm considering exporting an SQL file whenever we run the migrations for our dev tools / tests to use the one large file. But I haven't found it as easy as export -> import to try it out yet.
Yes, but being able to quickly return to a known-good state by trashing the database is still useful because getting migrations right without testing them is hard.
Using named volumes helps because they don't need to be bridged through to the host filesystem.
The difference, I suppose, is that by inspecting the schema, you can make assertions that take the whole architecture into account, rather than just the specific piece of SQL of one migration. I don't personally run the tool in CI, I run it before generating types, which is when I am writing a new migration.