What's the recommended location for SQL (DDL) scripts?

What's the recommended location for SQL, DDL, ... scripts in the Maven standard directory structure?

I bet almost every web project uses a DB and some kind of SQL scripts that need to be stored somewh开发者_运维技巧ere, so what would probably be the "best" place where to keep these files?

Please advise.

I think there is no best practice for this. In my past project, I created a separate directory for storing such SQL script.

For example src/main/db.

It won't be packaged to final JAR by default (which are the preferred way in most case), yet it is convenient enough to let it packaged in assembly. You can even package them in the main artifact JAR, by adding corresponding resource declaration or using maven build-helper plugin.

However, all depends on your usage on this script. My simple "rule of thumb" is that, I would consider putting them in resources/ only when they are really resources to be loaded by the application.

I think it depends entirely on when and how these scripts are processed:

1. Compile Time: these are things that your compiler/toolchain consume and produce artifacts. The maven guidance on this very clear, and thus the files would belong somewhere in src/main/, like src/main/sql or src/main/db. While I wouldn't do this, I could see these being used by a task at compile to modify your DB. I could see liquibase scripts being used here and then executed via a maven task.
2. Runtime: these are used by your runtime environment and either modify it, or are consumed by it to produce results. Placing them in src/main/resources seems reasonable so that your runtime processes can consume them change your DB as you see fit - say as part of your hot-fix processing on deployment, or as part of your normal DB on-the-fly versioning efforts. Again, maybe you ship liquibase with your app, and then do in-place DB changes that way...
3. Design Time: This seems to me to be the most likely scenario. Where should I store my DDL in order to properly track them in VCS, and still maintain my maven-compliant structure? For me, this is src/scripts/sql or src/scripts/db. This places them as "source" files within the purview of maven, but in a place that is designed to used in a more ad-hoc manner.

src/main/resources is a good place, but remember it gets packed into your final jar, so it depends if you want to reveal this in your production code or not.

If not, you can filter out this by adding maven-jar-plugin configuration excerpt to appropriate pom.xml:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-jar-plugin</artifactId>
    <configuration>
         <excludes>src/main/resources/privateSubdir/**</excludes>
    </configuration>
</plugin>

I would use src/main/resources for this purpose. Maybe creating a subfolder there.

I divided my application into multiple Eclipse projects, one for each architectural layer. This includes the database. In essence, I created a new packaging type of "database" just for that project. I created a src/main/sql folder, and under that, instead of Java packages, I put database schemas (eg src/main/sql/security). I suppose if you use catalogs, it would be first the catalogs then under them the schemas. In each of the schema folders, I put a folder for each type of database object (table, view, etc) so that I ended up with, for example, src/main/sql/security/tables and then put all the table definition files for that schema in there. I'm interesting in reading any opinions on doing it this way.

It very much depends on your mileage but first of all it's a good idea to separate your application from the underlying database structure. Therefore i'll recommend that you move all database related stuff to a separate maven project. Having done that, database scripts have a nice slot in /src/main/scripts.