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

If you have not installed Scriptella yet, use the one-command installer:

curl -fsSL https://scriptella.org/install.sh | sh

Follow the installer's instructions for adding Scriptella to your PATH. If it updates a startup file, start a new shell or reload that file before using scriptella.sh; if it says PATH was not changed automatically, add the printed export line.

Download the PostgreSQL pgJDBC driver and place the JAR in ${HOME}/.local/scriptella/lib. Save this SQL as init.sql:

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

INSERT INTO message VALUES (1, 'Hello from Scriptella');

Run init.sql with Scriptella 1.6 or newer, replacing the username and password with your PostgreSQL credentials:

scriptella.sh execute-sql \
  --url jdbc:postgresql://localhost/myapp \
  --user YOUR_POSTGRES_USER \
  --password YOUR_POSTGRES_PASSWORD \
  init.sql

Or you can create a simple ETL process that runs the SQL file. Save this as init.etl.xml alongside init.sql:

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

  <script>
    <include href="init.sql"/>
  </script>
</etl>

Run the ETL file:

scriptella.sh init.etl.xml

Both approaches execute the same SQL file, creating the table and inserting the first row. See direct SQL execution for command options, or the tutorial for more ETL examples.

The same approach works with MariaDB and other JDBC databases by installing the appropriate JDBC driver and changing the connection URL. For MariaDB, use jdbc:mariadb://localhost/myapp.

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.