Correct Strategy for DB migrations in Go
We have a go app where we use Postgres as a backend DB.
We use https://github.com/jmoiron/sqlx for communicating with DB.
The code to read all rows from a table is as belows:
rows, err := repo.db.Queryx(ListNodesQuery)
if err != nil {
repo.logger.Log("method", "ListNodes", "error", err)
return nil, err
}
r := *Node{}
for rows.Next() {
var n Node
err = rows.StructScan(&n)
}
After adding a new column in the DB, the code as expected errs as below
missing destination name type in *Node
as db table has more columns. Making change in code to update the struct simultaneously is ofcourse not possible.
I see that one way to turn this err off is to use db.Unsafe as documented here
Is there any other idiomatic way to deal with this situation?
sql postgresql go sqlx
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
add a comment |
We have a go app where we use Postgres as a backend DB.
We use https://github.com/jmoiron/sqlx for communicating with DB.
The code to read all rows from a table is as belows:
rows, err := repo.db.Queryx(ListNodesQuery)
if err != nil {
repo.logger.Log("method", "ListNodes", "error", err)
return nil, err
}
r := *Node{}
for rows.Next() {
var n Node
err = rows.StructScan(&n)
}
After adding a new column in the DB, the code as expected errs as below
missing destination name type in *Node
as db table has more columns. Making change in code to update the struct simultaneously is ofcourse not possible.
I see that one way to turn this err off is to use db.Unsafe as documented here
Is there any other idiomatic way to deal with this situation?
sql postgresql go sqlx
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
Are you using a variant onSELECT * FROM...to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.
– Cosmic Ossifrage
Nov 15 '18 at 10:58
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24
add a comment |
We have a go app where we use Postgres as a backend DB.
We use https://github.com/jmoiron/sqlx for communicating with DB.
The code to read all rows from a table is as belows:
rows, err := repo.db.Queryx(ListNodesQuery)
if err != nil {
repo.logger.Log("method", "ListNodes", "error", err)
return nil, err
}
r := *Node{}
for rows.Next() {
var n Node
err = rows.StructScan(&n)
}
After adding a new column in the DB, the code as expected errs as below
missing destination name type in *Node
as db table has more columns. Making change in code to update the struct simultaneously is ofcourse not possible.
I see that one way to turn this err off is to use db.Unsafe as documented here
Is there any other idiomatic way to deal with this situation?
sql postgresql go sqlx
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
We have a go app where we use Postgres as a backend DB.
We use https://github.com/jmoiron/sqlx for communicating with DB.
The code to read all rows from a table is as belows:
rows, err := repo.db.Queryx(ListNodesQuery)
if err != nil {
repo.logger.Log("method", "ListNodes", "error", err)
return nil, err
}
r := *Node{}
for rows.Next() {
var n Node
err = rows.StructScan(&n)
}
After adding a new column in the DB, the code as expected errs as below
missing destination name type in *Node
as db table has more columns. Making change in code to update the struct simultaneously is ofcourse not possible.
I see that one way to turn this err off is to use db.Unsafe as documented here
Is there any other idiomatic way to deal with this situation?
sql postgresql go sqlx
sql postgresql go sqlx
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
asked Nov 15 '18 at 10:53
Saurav PrakashSaurav Prakash
982413
982413
Are you using a variant onSELECT * FROM...to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.
– Cosmic Ossifrage
Nov 15 '18 at 10:58
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24
add a comment |
Are you using a variant onSELECT * FROM...to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.
– Cosmic Ossifrage
Nov 15 '18 at 10:58
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24
Are you using a variant on
SELECT * FROM... to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.– Cosmic Ossifrage
Nov 15 '18 at 10:58
Are you using a variant on
SELECT * FROM... to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.– Cosmic Ossifrage
Nov 15 '18 at 10:58
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24
add a comment |
1 Answer
1
active
oldest
votes
Making change in code to update the struct simultaneously is ofcourse
not possible.
You can manage DB schemas inside your codebase and have some app startup code that automatically migrates DB to latest schema version to keep code and DB in sync.
One of the options is github.com/golang-migrate.
Run this code on app start to update DB schema to latest version:
migrate, err := migrate.New("folder to sql scrips", "db connection string")
if err != nil {
logger.Panic(err)
}
err = migrate.Up()
if err != nil {
logger.Panic(err)
}
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.GOidiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.
– Dmitry Harnitski
Nov 18 '18 at 13:54
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317824%2fcorrect-strategy-for-db-migrations-in-go%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Making change in code to update the struct simultaneously is ofcourse
not possible.
You can manage DB schemas inside your codebase and have some app startup code that automatically migrates DB to latest schema version to keep code and DB in sync.
One of the options is github.com/golang-migrate.
Run this code on app start to update DB schema to latest version:
migrate, err := migrate.New("folder to sql scrips", "db connection string")
if err != nil {
logger.Panic(err)
}
err = migrate.Up()
if err != nil {
logger.Panic(err)
}
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.GOidiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.
– Dmitry Harnitski
Nov 18 '18 at 13:54
add a comment |
Making change in code to update the struct simultaneously is ofcourse
not possible.
You can manage DB schemas inside your codebase and have some app startup code that automatically migrates DB to latest schema version to keep code and DB in sync.
One of the options is github.com/golang-migrate.
Run this code on app start to update DB schema to latest version:
migrate, err := migrate.New("folder to sql scrips", "db connection string")
if err != nil {
logger.Panic(err)
}
err = migrate.Up()
if err != nil {
logger.Panic(err)
}
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.GOidiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.
– Dmitry Harnitski
Nov 18 '18 at 13:54
add a comment |
Making change in code to update the struct simultaneously is ofcourse
not possible.
You can manage DB schemas inside your codebase and have some app startup code that automatically migrates DB to latest schema version to keep code and DB in sync.
One of the options is github.com/golang-migrate.
Run this code on app start to update DB schema to latest version:
migrate, err := migrate.New("folder to sql scrips", "db connection string")
if err != nil {
logger.Panic(err)
}
err = migrate.Up()
if err != nil {
logger.Panic(err)
}
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
Making change in code to update the struct simultaneously is ofcourse
not possible.
You can manage DB schemas inside your codebase and have some app startup code that automatically migrates DB to latest schema version to keep code and DB in sync.
One of the options is github.com/golang-migrate.
Run this code on app start to update DB schema to latest version:
migrate, err := migrate.New("folder to sql scrips", "db connection string")
if err != nil {
logger.Panic(err)
}
err = migrate.Up()
if err != nil {
logger.Panic(err)
}
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
answered Nov 15 '18 at 13:39
Dmitry HarnitskiDmitry Harnitski
3,85311834
3,85311834
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.GOidiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.
– Dmitry Harnitski
Nov 18 '18 at 13:54
add a comment |
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.GOidiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.
– Dmitry Harnitski
Nov 18 '18 at 13:54
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
But If I have to rollback, then again I have to make change in code to update the struct.
– Saurav Prakash
Nov 18 '18 at 8:29
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.
GO idiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.– Dmitry Harnitski
Nov 18 '18 at 13:54
You have to keep your DB schema and code schema in sync. Adding/removing columns is just one of many cases how that schema evolves.
GO idiomatically wants to handle all errors including mapping explicitly. You are fighting with language intent when you want to swallow mapping inconsistencies.– Dmitry Harnitski
Nov 18 '18 at 13:54
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53317824%2fcorrect-strategy-for-db-migrations-in-go%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Are you using a variant on
SELECT * FROM...to query from the table? Change this to explicitly list the column names of interest. You save wasting database and network resources in moving data you discard, and you can explicitly update the query at the point the code is ready to receive values from the additional columns.– Cosmic Ossifrage
Nov 15 '18 at 10:58
hmm...good point
– Saurav Prakash
Nov 15 '18 at 11:24