I have a small project which I want to publish on github. The problem is that my project contains a database. I don’t know what is the best way to attach the database structure.
I may get the create queries and put in a file called db.sql, but is this the best way?
I would do a separation between the schema of your database and the actual initial content. For a simple and small application, you could create a schema.sql and a seed.sql. The schema holds all the statements to create the database and its tables. The seed contains INSERT statements.
If you want to go one step further, seed the database through your code.
There’s a good example of how to accomplish the migration of database structures and seeding the database in the framework Ruby on Rails. Have a look at the migration guide for Rails.
Most O/RM frameworks provide a means to initialize and seed a database. Via Code. For example, Entity Framework allows you to create a custom database initializer and implement a seed method to provide seed data (If you just want to have it generate the database, creating a seed function is not necessary).
If you’re not using an O/RM, you can use the built-in function of your Database to generate a create script. For example, here is a walkthrough of how to do it with SQL Server
Yet another option would be to put your database scripts into source control (if you’re manually managing the database), of course you’d need a way to have the scripts compiled into a single execution, also taking care to order the scripts properly in order to properly handle dependencies between the tables.
1