►
From YouTube: Still Writing SQL Migrations? You Could Use A (Schema)Hero. - Marc Campbell, Replicated
Description
Don’t miss out! Join us at our upcoming events: EnvoyCon Virtual on October 15 and KubeCon + CloudNativeCon North America 2020 Virtual from November 17-20. Learn more at https://kubecon.io. The conferences feature presentations from developers and end users of Kubernetes, Prometheus, Envoy, and all of the other CNCF-hosted projects.
Still Writing SQL Migrations? You Could Use A (Schema)Hero. - Marc Campbell, Replicated
Let’s talk about database schema migrations. All too often, migrations are deployed using legacy tools, or worse yet — (gasp!) manually. That’s because we write migrations the hard way, instead of simply declaring the desired state of the database. Kubernetes has proved that declarative desired state is a great idea, so why not build on top of the Kubernetes API to let the computers figure out how to write database migrations? SchemaHero is an open source Kubernetes CRD/Operator that solves database migrations. Want to add an index, or rename a column? Just kubectl apply the desired definition of a table, and the operator will take over by writing and deploying a migration. Once you stop trying to control the database and just declare the desired state, database migrations are no longer toil, but become predictable and reliable. Let the computers migrate your schema instead.
https://sched.co/Zelq
A
A
My
name
is
mark
campbell,
I'm
the
cto
and
co-founder
of
a
company
called
replicated,
and
the
goal
of
this
talk
is
to
really
present
the
idea
of
declarative
schema
management
as
a
kubernetes
operator
and,
in
particular,
the
implementation
that
we've
open
sourced
in
a
project
we've
been
working
on
it
replicated
called
schema
hero
to
help,
explain
how
I'm
thinking
about
this
problem.
I
want
to
present
a
story
of
two
different
developers.
A
A
I
want
to
walk
through
a
couple
of
scenarios
for
the
day-to-day
of
these
developers.
The
first
scenario
they
both
these
developers
are
adding
a
new
feature
that
requires
a
database
schema
change.
Our
django
developer,
she's,
just
going
to
edit
her
python
file.
Add
a
new
class
save
it
and
push
it
to
heroku.
What
happens
when
she
does
that
is
django
is
going
to
automatically
deploy
the
database
migration
wait
for
that
to
complete
and
then
start
the
new
version
of
the
application.
A
That's
great,
so
he's
using
get
ops
because
everything's
automated
he
commits
that
into
source
control
has
a
code
review
and
let's
once
it's
merged,
let's
flux
automatically
deploy
a
custom,
kubernetes
job
and
that
runs
performs
the
database
migration.
He
monitors
that
and
once
it's
completed,
then
he
merges
his
code
change
and
let
that
deploy
in
order
to
use
the
new
schema.
A
A
A
A
Sort
of
in
this
scenario,
though,
in
older
migration
number
474,
so
a
few
months
ago,
depends
on
a
postgres
module
that
was
implemented
for
a
search
feature
that
was
later
removed
and
rewritten
re-architected.
So
we
didn't
need
that
in
the
end,
but
the
migrations
fail.
They
fail
at
migration
474
for
him
because
he
needs
that
module
in
order
to
allow
it
to
install
it.
Even
though
it's
going
to
uninstall
it
100
migrations
later
there's
a
couple
options
he
could
do.
He
could
rebase
these
migrations.
He
could
delete
that
change.
A
Cherry
pick
through
it,
but
this
is
a
common
problem
that
you
run
into
the
differences
between
the
two
here
are
django
developer
just
deploys
the
desired
state
of
the
database.
She
knows
you
know
here's
our
code,
here's
the
class
lets
the
orm
automatically
deploy
what's
needed
and
our
java
developer.
He
actually
deploys
the
history,
so
he
walks
through
everything
that
converges
to
that
desired.
State
and
another
difference
because
of
this
is
that
our
django
developer
doesn't
need
to
all
of
the
old
dependencies
that
we
ever
needed
and
our
java
developer
needs
everything.
A
That's
ever
been
required.
So
if
a
migration
required
a
certain
encoding
or
a
module
or
a
certain
expected
a
specific
version
of
the
database
engine,
it's
going
to
be
needed
in
order
to
successfully
complete
unless
we
go
back
and
rewrite
that
migration.
Later
final
scenario,
I
want
to
walk
through
is
auditing
the
schema.
So
a
question
is
raised:
is
there
an
index
on
this
column,
and
so
both
of
the
developers
want
to
understand
that?
To
answer
that
question-
and
of
course
we
want
to
do
this
in
a
way
without
direct
access
to
that
database.
A
So
our
django
developer,
just
looks
at
the
python
class,
looks
to
see
if
there's
an
index
defined
in
the
attributes
on
the
class
great
in
our
java
developer.
Everything
is
in
code
also,
so
he
just
opens
up.
His
editor
looks
at
the
migration
files
does
have,
to
you,
know:
command
shift
f
or
whatever
and
search
through
913
migration
files
for
the
table.
Name,
read
them
reads
them
to
see
if
that
index
has
ever
been
created
or
dropped
in
like
what
order
it
is,
and
that
way
he
can
reconstruct
the
final
state
of
that
table.
A
To
answer
the
question.
The
differences
between
here
are
that
she
just
has
a
single
place.
That
has
the
desired
state
and
he
doesn't
again,
he
has
all
of
the
instructions
that
he
can
use
in
his
head
to
converge
on
what
that
desired.
State
is
so.
This
raises
the
question:
what
if
there
was
an
orm
specifically
designed
for
database
schemas,
some
platforms
have
this
today
right
so
django,
as
we
just
talked
about,
has
a
built-in
rm
that
manages
database
schemas,
flask
apps
for
python,
you
can
use
sql,
alchemy
and
other
tools
java
and
spring
have
hibernate.
A
You
can
use
that
for
schema
management
as
an
orm
and
node
has
a
few
projects
they're
a
little
bit
fragmented
in
that
ecosystem.
You
know,
most
most
platforms
do
have
some
platform
specific
functionality
to
manage
schema
orms,
not
all,
but
they
do
java
being
a
popular
language.
You
know
a
question
was
asked
on
stack
overflow
a
while
ago,
should
I
use
hibernate
to
automatically
manage
schema
in
production
and
the
highest
answer
back
from
2016
said
no,
it's
unsafe
and
the
explanation
is
pretty
detailed
and
the
interesting
part
highlighted
in
red
here.
A
A
So,
specifically,
what
we're
built
is
a
kubernetes
operator
to
manage
database
schemas
in
a
language
neutral,
state
or
system,
so
it
doesn't
matter
whether
you're
running
python
or
rails
or
node,
or
go
or
rust
or
java
or
any
other
language,
and
any
other
tooling
it
does
like
schema
hero
is
a
tool
specifically
meant
to
manage
the
database
schema
and
it
does
it
through
a
declarative
state.
In
a
way,
that's
git,
ops,
friendly.
A
How
does
scheme
hero
work?
It's
pretty
simple.
It's
a
kubernetes
operator.
The
first
step
is
that
you
want
to
deploy
a
kind
database.
So
this
is
a
kubernetes
custom
resource
schema.
Hero
operator
implements
the
reconcile
loop
for
it.
This
kind
database,
it
you
give
it
a
name
and
you
deploy
it
to
a
namespace,
but
the
key
part
here
is
that
this
just
contains
the
connection
information
for
the
database
and
a
name.
So
in
this
example,
that's
on
the
screen
right
now
this
is
a
postgres
database
and
the
custom
resource
is
saying
the
connection
url.
A
The
connection
uri
to
connect
to
this
postgres
database
is
in
a
secret
name.
Postgresql
in
a
key
named
uri.
You
can
hard
code,
you
can
put
credentials
in
here
using
secrets.
You
can
put
them
right
in
here.
You
can
use
hashicorp,
vault
or
aws
parameter
store,
there's
various
methods
that
you
can
use,
but
the
database
object
just
is
a
pointer
to
how
to
connect
to
the
database
whether
that
database
is
in
the
cluster
or
out
of
the
cluster.
A
Next,
you
want
to
deploy
a
kind
table
chances.
Are
you
probably
want
to
deploy
more
than
one
of
these,
because
most
databases
have
multiple
tables?
It
looks
something
like
this,
so
this
kind
table
references
the
the
database
object
that
we
just
deployed
and
it
defines
what
the
schema
what
the
desired
schema
of
this
table
is.
On
the
example
that's
on
the
screen
here
we
have
a
couple
of
columns,
code
and
name.
So
this
defines
what
the
columns
are.
A
What
the
types
of
these
columns
are
any
constraints
on
the
columns
and
in
addition,
you
can
define
primary
key
indexes,
unique
indexes
foreign
keys,
any
other
schema.
That's
that
you
need
on
this
table.
You
deploy
this
kind
table.
The
next
step
is
just
to
view
and
approve
the
migration
so
using
the
schema
hero
cli,
which
is
distributed
as
a
coupe
control
plug-in.
You
can
just
view
the
migrations
and
then
describe
a
migration.
So
here
we've
deployed
a
migration,
and
when
I
run
coupe
control
schema
hero
describe
migration.
A
A
Approving
the
migration
will
actually
just
run
that
ddl
statement
that's
presented
on
the
screen
that
schema
hero
calculated
rejecting
the
migration
will
mark
it
as
rejected
not
run
it
and
prevent
it
from
being
executed
and
recalculating.
The
migration
tells
schema
hero
to
go.
Take
the
desired
state
in
in
connect.
Compare
it
to
the
actual
database,
schema,
that's
running
and
regenerate
the
correct,
create
table
statement
that
may
be
useful.
A
If
the
underlying
database
schema
has
changed,
somebody
manually
altered
it
or
another
tool
has
has
edited
the
schema,
so
schema
hero
will
then
regenerate
the
ddl
statements
that
are
necessary
to
help
demonstrate
this.
I
want
to
show
a
little
bit
of
a
demo
on
how
schema
hero
works.
So
here
I
have
a
kubernetes
cluster
set
up.
It's
an
eks
cluster
doesn't
really
matter.
A
So
we
look
here-
and
this
is
like
I
mentioned
it's-
a
kubernetes
116
cluster
running
five
nodes-
I'm
going
to
now
deploy
schema
hero
and
deploy
a
database
to
it.
So,
to
start
with,
I
want
to
deploy
a
secret
and
the
secret
contains
a
connection
string
and
this
connection
string
is
base64
encoded,
but
it's
it's
to
this
test
database
instance
that
we
have
on
on
rds.
A
So
if
I
apply
that
it
may
already
be
applied
so
that
secret
is
there
that's
great,
and
so
the
next
step
I
want
to
do
is
deploy
the
schema
hero
operator.
So
I
have
cube
control
schema,
hero
already
installed.
This
is
distributed
as
a
crew
plug-in,
so
you
can
do
control
crew
with
a
k,
install
schema
hero
and
that'll,
give
you
the
client
side
components
for
to
manage
a
schema
hero,
operator,
running
in
your
production,
environment
or
any
environment.
A
A
A
When
I
do
this,
you
can
see
that
the
migration
name
is
what
I
just
provided
and
the
generated
ddl
statement
here
is
what
what
schema
hero
has
calculated,
that
it's
needed
to
turn
that
rds
instance
into
the
instance
into
the
desired
state,
and
it's
it
didn't
find
that
airport
table,
because
this
is
a
new
database,
and
so
it
just
generated
a
create
table
statement.
It
gives
me
a
couple
options.
I
can
approve
this.
A
I
can
reject
it
or
I
can
recalculate
it
if
I
need
to
I'm
going
to
go
ahead
and
just
approve
it,
and
now
that
I've
approved
it.
When
I
get
migrations
again,
you
can
see
that
it
was
planned
a
minute
ago.
I
approved
it
four
seconds
ago
and
scheming.
Hero
executed
it
four
seconds
ago.
So
I'm
going
to
head
over
here
to
my
ui
that
I
used
to
look
at
my
postgres
cluster
postgres
database
and
I'm
going
to
refresh
the
tables
that
I
have
and
you
can
see
now.
I
have
an
airport
table.
A
A
I'm
not
doing
that.
I
want
that
to
be
a
unique
index
and
I
could
name
the
index
if
I
want
to,
but
I
don't
really
have
a
strong
preference
about
the
name.
So
I'm
going
to
let
schema
hero
go
ahead
and
calculate
that
and
now
when
I
apply
that
migration
again
or
that
yaml
again,
I
have
an
error
because
the
key
is
actually
named
indexes.
A
A
You
can
see
that
it's
generated
a
couple
of
new
c
ddl
statements.
It
wants
to
alter
the
airport
table,
adding
the
postal
code
column
text
not
no,
and
then
it
wants
to
create
a
unique
index
named
this
on
airport
with
that
name
column,
and
it
gives
me
commands
again
to
approve
or
reject
it.
I'm
going
to
go
ahead
and
just
approve
that
for
this
demo,
so
I
approve
it
and
if
I
run
get
migrations
again,
you
can
see
that
again.
A
A
A
You
can
get
migrations
you
can
filter
in
by
database
in
case
you're,
managing
multiple
databases,
and
this
shows
you
all
of
the
different
migrations
that
you've
run
describing
a
migration
you
can
see
here.
You
know
an
example
of
a
little
bit
more
of
a
complex
one
that
that
we
have
where
it's
this
one
table
has
multiple
alter
table
statements
that
it
wants
to
run,
because
that
was
all
deployed
in
one
iteration
one
version
of
that
table
resource.
A
If
you
pass
that
uri
in
the
coupe
control
schema,
hero,
plugin
is
going
to
connect
in
view
the
schema
of
all
of
those
different
tables
and
write
a
yaml
file
for
each
of
those
tables.
So
this
is
a
really
easy
way
to
export
your
schema
from
a
running
database
into
all
of
the
yaml
files
that
are
necessary,
and
then
you
could
edit
one
of
these
yaml
files
and
apply
it,
and
that
would
now
you're
managing
that
schema
through
a
declarative
state
of
scheming
hero,
take
a
step
back
and
like
reviewing.
What
does
schema
hero
do?
A
A
You
could
probably
build
this
yourself
today
by
running
custom,
kubernetes
jobs
deploying
these
that
run
the
ddl
statements.
We
talked
about
that
a
little
earlier,
but
schema
hero
has
this
all
built
in
you
can
now
just
run
coupe
control
apply
or
let
one
of
these
tools
deploy
the
yaml,
and
then
you
have
a
full
get
ops
pipeline.
In
order
to
manage
to
manage
your
database
schemas,
it
enables
policy
enforcement
for
database
schemas
using
tools
like
open
policy,
agent,
gatekeeper
or
conf
test,
or
anything
like
this.
A
Now
that
we
have
our
database
schemas
defined
as
a
declarative,
structured
yaml
file.
Instead
of
imperative
statements,
we
can
run
policy
against
those
apply.
Policies
allows
us
to
do
unique
things
like
have
requirements
and
enforce
requirements
around
primary
key
existence
on
on
tables
foreign
keys
names,
types
of
different
columns,
anything
that
you
want
to
be
able
to
do
that.
You
can
define
in
a
rego
file.
You
can
now
enforce
against
your
database
schema.
A
You
can
have
easy
approval
process.
The
demo
I
just
showed-
and
everything
we've
talked
through-
is
kind
of
walk
through
that
that
two-step
process,
where
I
deploy
the
yaml
file,
allow
scheming
hero
to
plan
the
sql
statement,
and
then
I
decide
to
approve
or
reject
it.
That's
really
useful
if
you
want
to
monitor
to
see
what's
running,
but
if
you
have
other
environments,
dev
test
environments-
or
you
know
different
environments,
where
you
don't
need
that
level
of
control,
you
can
actually
have
schema.
A
This
removes
the
need
to
write
ddl
specifically
like
it's,
not
a
replacement
for
sql.
Your
developers
still
have
to
write
sql
or
use
an
orm
at
the
application
layer,
but
the
the
syntax
around
creating
foreign
keys
or
complex,
alter
table
statements.
There's
lots
of
different
ways
that
you
could
do
this
that
could
lock
a
table
or
not
lock
an
entire
table
depending
on
how
you
write
these
migrations.
A
Hopefully,
you're
cleaning
them
up
better
than
this,
but
you
know
it's
not
uncommon
to
be
using
kubernetes
jobs
in
order
to
manage
database
schemas
right
now,
and
this
eliminates
that
need
schema.
Hero
currently
supports
you
know
the
some
popular
commercial
and
open
source
databases
from
postgres,
cockroachdb
and
mysql
cockroach
is
very
similar
to
postgres,
but
it
is
a
little
bit
different
and
the
schema
management
can
be
a
little
bit
different
at
times,
so
schema
hero
creates
some
new
options
that
we
didn't
have
before.
A
One
of
them
being
to
detect
drift
in
their
databases,
so
now
that
we
have
the
desired
state
of
our
databases
deployed
into
the
kubernetes
cluster,
if
something
out
of
band
has
modified,
the
schema
schema
hero
will
generate
a
migration
to
bring
it
back
into
where
we
want
it
to
be
it'll.
Remove
that
drift.
So
it's
it's
really
useful
to
determine
that
to
to
use
this
as
a
tool
to
automatically
see
if
your
your
databases
is
not
what
you
expect
it
to
be.
A
If
the
schema
changes
in
any
unexpected
ways,
you
can
also
enforce,
and
audit
schemas
using
you
know,
tools,
external
other
cncf
projects
like
gatekeeper,
conf,
test
and
other
tools.
You
know
by
for
things
like
I
mentioned
before,
column
names,
any
kind
of
any
kind
of
policy
or
best
practices
that
you
want
to
implement.
You
can
do
this,
you
can
do
it
in
a
git,
ops
or
in
a
you
know,
commit
check
now
or
in
code
review.
A
You
can
easily
test
schema
on
updated
versions
of
database
engines.
So
as
an
example
here,
if
you
were
running
my
sequel,
5.6
and
now
you're
looking
to
upgrade
to
my
sequel
8,
but
there
was
a
some
functionality
that
was
deprecated
along
the
way
and
removed
in
8.0.
Your
migrations
are
no
longer
going
to
work,
because
if
you
run
that
on
a
new
mysql8
database,
it's
going
to
fail
because
the
the
statement
is
no
longer
compatible
with
the
ddl.
That
mysql8
supports
so
think
of
this.
A
As
like
auto
rebasing,
these
you
could
manually
go
back
through
every
time.
You
want
to
change
database
engines
and
recalculate
all
your
migrations
for
the
new
version
schema
hero.
Just
does
this
automatically
by
nature
of
what
it
does,
and
it's
really
easy
to
test
the
schema
on
a
cloud
native
version
of
the
database
engine.
So
if
you're
looking
at
migrating
from,
say,
postgres
to
cockroachdb
but
you're,
there's
some
slight
differences
in
the
ddl
statements
and
support
between
the
two
engines.
A
Schema
hero
is
a
good
use
for
this,
because
you
can
take
that
schema
run
it
against
cockroachdb
schema
hero
understands
how
to
calculate
the
migration
commands
in
the
in
the
create
table
and
alter
tables
on
cockroachdb
versus
postgrads,
when
there
are
little
differences
between
the
two
or
unsupported
changes,
and
so
it's
really
easy
to
not
think
about
that
abstraction
like
let's
keep
in
here
will
be
that
abstraction.
So
you
don't
have
to
like
understand
those
differences
of
of
implementing
the
schema
on
each
of
them.
A
Schema
hero
doesn't
do
everything,
though
it
doesn't
do
database
migration
or
data
migrations
right
now,
in
particular.
If
you
have
business
logic,
you
need
to
run
around.
You
know
creating
modifying
data,
splitting
columns
doing
something
that
actually
involves
writing
code.
You
know
you're
still,
today
better
using
something
like
goose
or
fly
away,
or
you
know
another
migration
tool
that
can
handle
that
data
migration,
at
least
for
that
layer.
We're
super
open
to
having
this
as
part
of
schema
hero.
A
It's
not
there
today,
there's
a
github
issue
where
we're
discussing
the
the
plan
and
like
the
design
for
how
we
could
do
this
love
to
have
some
help
there
and
it
does
it's
not
an
app
skim.
Hero
is
not
an
application,
runtime
orm.
So
if
you
are
using
an
orm
so
that
developers
don't
have
to
write,
select
statements
and
update
and
delete
statements
at
runtime,
that's
great,
that's
super
compatible
with
everything
schema.
Hero
is
doing
schema.
Hero
wants
to
really
focus
on
becoming
the
schema
orm.