How Do You Seed Your Database?

<p>If I wanted to seed my database with test-data, what would be the better approach? To seed it using a SQL Script or with the language that one intends to use (e.g. GoLang, Python, etc.)?</p> <p>If I were to use the <strong>SQL Script approach</strong> to seed the database, one of the benefits is that I can easily switch programming languages without having to change the SQL Script.</p> <p>On the other hand, if I were to use the <strong>programming language approach</strong>, I could reuse some of the code created for normal use, to seed the database (e.g. use the models, functions to verify input, etc.).</p> <hr/>**评论：**<br/><br/>hybsuns: <pre><p>I seed data using SQL scripts. Seeding data to db is a detail and can be done in a separate process. </p></pre>psaux-a: <pre><p>That&#39;s a good point. Thanks for the input. Would you consider making a separate &#34;component,&#34; in the native language as opposed to SQL script, that handles the separate process? Then, you can just ignore this component in production. Wouldn&#39;t this component essentially be equivalent to the SQL script?</p></pre>hybsuns: <pre><p>I would not create a separate component just to execute the data seeding process, and I don&#39;t believe that is completely equivalent to the SQL script. Here are my reasons:</p> <ol> <li>When such component is implemented, it is possibly going to introduce a lot of maintenance issues. For example, what if the database driver needs to be updated? You have to compile and test that component. When you migrate your database from Postgres to SQL Server? You have to update the component AND the SQL. SQL are designed to run directly on the database server. Adding a component does not really enhance your project. Instead, it may introduce a lot of bugs.</li> <li>Maintaining SQL code for large project is not trivial work, even if it&#39;s just a test database with very little data. If the project is very large and complex enterprise application, your database will be maintained by a designated DBA team, instead of maintained as a side project by one or two developers. I have not worked in a DBA team yet, but from what I heard, DBA team creates SQL script based on the need of the application, so they can optimize the query and indexing of tables. In this case, your DBA does not need to know if you are using Go or .NET. All they need to know is what kind of table you will need and they can optimize the database for you. If you integrate the SQL into your Go project, your DBA team will be required to work with Go to some degree, and it&#39;s not always ideal.</li> </ol></pre>aarondl: <pre><p>If your seed data is boring, use an sql script (like populating a list of categories that are static for example). If you&#39;re seeding for lorem ipsum kinds of reasons e.g. to see what a site looks like when it&#39;s full or something then that needs a bit more logic than I&#39;d care to write in sql and I&#39;d use Go (gotta avoid duplicates on unique indexes etc.).</p> <p>In terms of reusing the code created for normal use, don&#39;t write this code by hand if you were planning to, use one of the Go ORM-style packages out there (kind of sounded like you were, since if the code was generated it could hardly be called reuse).</p></pre>psaux-a: <pre><p>Thanks for the input.</p> <p>This is true, I was thinking of creating my own models for the time being. Initially, I was going to use CockroachDB with SQLBoiler but I think the SQLBoiler team is still in the process of working on getting it to play nicely with CockroachDB. So, I will be using regular SQL for now and hence, I&#39;ll need to create my own models.</p></pre>MisterSkilly: <pre><p>I seed them in Go, for the reasons you&#39;ve listed.</p></pre>Ploobers: <pre><p>Another option is to just use a pre-seeded Docker image with a package like <a href="https://github.com/ory/dockertest" rel="nofollow">https://github.com/ory/dockertest</a>. The image will work for any language, you&#39;d just need to spin up a new container per test.</p></pre>