Create and initialize a database

PostgreSQL

From a PostgreSQL SQL session, create a database with:

CREATE DATABASE myapp;

The standard command-line equivalent is:

createdb myapp

See the official PostgreSQL documentation for CREATE DATABASE and createdb, including options for ownership, authentication, encoding, and permissions.

MariaDB

From a MariaDB SQL session, create and select a database with:

CREATE DATABASE myapp;
USE myapp;

See the official MariaDB CREATE DATABASE documentation for details about privileges, character sets, and collation.

Initialize it with Scriptella

Place the PostgreSQL JDBC driver in Scriptella's lib directory. Then save this example as init.etl.xml; it connects to the PostgreSQL database, creates a table, and inserts its first row:

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
  <connection
      driver="postgresql"
      url="jdbc:postgresql://localhost/myapp"
      user="postgres"
      password="password"/>

  <script>
    CREATE TABLE message (
      id INTEGER PRIMARY KEY,
      text VARCHAR(100)
    );

    INSERT INTO message VALUES (1, 'Hello from Scriptella');
  </script>
</etl>

Run the ETL file with Scriptella:

scriptella init.etl.xml

This creates the table and inserts the first row. The same approach works with MariaDB and other JDBC databases by changing the connection URL and JDBC driver.

For larger schemas, SQL can also be kept in separate files and included from the ETL file.

What's next?

Scriptella is useful for organizing and running database scripts and data operations once the database exists. You can use it to:

• Create tables and other schema objects from SQL files.
• Populate initial data from SQL scripts.
• Import CSV, text, XML, and other supported sources.
• Copy or migrate data from another database.
• Run a sequence of setup or migration scripts as one repeatable job.

Follow the Scriptella tutorial for runnable examples, then use the reference documentation and driver and provider matrix for configuration details.