1

I have a service that populates a SQL Server database. The service is written in nodejs.

Soon I will have to create a website that displays data from the database. And then create a separate website, an admin panel for the database.

Potentially more services that interact with the database will be added soon. New services might not use the same language or platform.

Currently the database schema is contained in a SQL file bundled with the service code. Unit tests use the file to setup a test database before each tests.

What's the best way for 2, 3 or more project to share database schema?

I want to make sure that if I change the database I can not forget to update one of the projects or its tests.

What's the industry best practice? Should the database schema be in a separate module, kept as a dependency? How to solve the problem of using different languages?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Euphe
  • 3,531
  • 6
  • 39
  • 69

1 Answers1

2

This is a complex problem, and I'm not sure there is a single best answer.

The problem you're facing is one of coupling - you have an artefact in your solution that is a dependency for several other artefacts. One of the oldest architectural principles in software is to reduce coupling - it leads to bugs, slower development, and code that's harder to modify.

There are a number of ways you can reduce coupling in software design. The classical answer is to introduce interfaces and dependency injection; that's obviously not practical with a database.

The "cleanest" way is for your database to be accessible via an API. You can use versioning to allow multiple applications to manage that dependency - Application A was coded against version 1.0 of the API, and you promise not to change that version (or at least not without deprecation notices). This does introduce a significant amount of additional work, as well as a potential performance challenge.

A more pragmatic solution is to insist on an automated deployment mechanism for all your environments, and to retrieve the database schema from a central location, and insist that all applications include a set of integration tests to exercise all the database functionality. You might distribute your dev and test databases via VM images, for instance, with "read-only" status for your schema.

I have done this using a variety of techniques. The first step is to create a process for converting your database into text files that can be managed in a source code repository, and played back to create a working database. Here's an SO question with lots of info on how to do this.

Once you have your database under version control, you can decide how to distribute it - either asking projects to check out the database and build it themselves, or as docker images, or VMs or whatever.

You depend on your applications to do the right thing - it's not enforced by a technical feature - but you don't have to introduce significant new layers to your solution.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • How can I make a central source of db schema? My current idea: 1. Create a git repository that stores an sql file to setup the database. 2. Make db docker image, starting from official db image and applying the SQL to it. 3. For each project create a docker composes file, that runs the project + the db – Euphe Oct 06 '17 at 07:33
  • I've updated the answer - the trick is to get your database under version control, so you can update your schema in a controlled way; one you've got that, you can create docker images etc. – Neville Kuyt Oct 06 '17 at 08:55