Scriptella ETL tutorial

With Scriptella, you write regular SQL to query and update databases through JDBC. XML provides a thin layer that declares connections and controls when queries and scripts run. Other drivers let you work with CSV, text, and scripting languages in the same ETL file.

In this tutorial, you will run a job from the command line, see how it processes rows, and use the same pattern to copy data between databases.

1. Install Scriptella

You need Java 17 or newer. Check Java, then install Scriptella on macOS or Linux:

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

Follow the installer's instructions to add Scriptella to your PATH. Open a new shell, then verify the installation:

scriptella.sh --version

On Windows, follow the Windows installation instructions and use scriptella.bat for the commands below. See the installation guide for manual installation.

2. Your first ETL job

Generate a SQL file from a CSV file. This job uses the bundled CSV and text drivers, so you need no database or extra driver downloads.

people.csv passes through a Scriptella ETL job to produce load.sql.
Two input rows become two INSERT statements.

Create a new working directory and put these two files in it.

people.csv

id,name
1,Ada
2,Grace

By default, Scriptella treats the first CSV row as column headers (id,name). These names become $id and $name variables, while subsequent rows contain the data. For headerless CSV files, set headers=false in the CSV connection properties.

csv-to-sql.etl.xml

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection id="input" driver="csv" url="people.csv"/>
    <connection id="output" driver="text" url="load.sql"/>

    <query connection-id="input">
        <script connection-id="output">
            INSERT INTO people (id, name) VALUES ($id, '$name');
        </script>
    </query>
</etl>

Run the job

From that directory, run:

scriptella.sh csv-to-sql.etl.xml

Open the generated load.sql. It contains these statements (leading whitespace may differ):

INSERT INTO people (id, name) VALUES (1, 'Ada');
INSERT INTO people (id, name) VALUES (2, 'Grace');

You have read two CSV rows and generated one SQL statement for each. No SQL has been executed against a database. Running this job again replaces the output file.

3. How Scriptella works

An ETL file declares connections and the queries and scripts that use them. A connection can point to a database, a file, or another supported resource.

A source query emits two rows. Each row exposes id and name variables and runs the nested destination script once.
A query supplies rows; its nested script runs once for every row.
  1. <query connection-id="input"> reads the CSV rows. An empty CSV query reads all data rows using the header names as variables.
  2. For the first row, id is 1 and name is Ada. The nested script writes one statement through the output connection.
  3. The query advances to Grace's row and runs the same script with the new values.

The connection-id selects which connection executes a query or script. With a JDBC source, the query contains a SQL SELECT; each result row exposes its columns to the nested script.

4. Copy rows between databases

Now use that pattern to copy a people table from MySQL to PostgreSQL. This example assumes both servers are running and you can create tables in source_db and target_db.

MySQL source_db sends people rows through Scriptella to PostgreSQL target_db using bound JDBC parameters.
The source SELECT supplies values for the target INSERT.

Prepare the databases and drivers

Create this table in both databases. Start with an empty target table:

CREATE TABLE people (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Insert the sample rows in MySQL only:

INSERT INTO people (id, name) VALUES (1, 'Ada'), (2, 'Grace');

Download MySQL Connector/J and the PostgreSQL JDBC driver. Create a lib directory beside your new ETL file. Copy the downloaded JARs into it and rename them to match these paths:

database-copy/
├── mysql-to-postgres.etl.xml
└── lib/
    ├── mysql-connector-j.jar
    └── postgresql.jar

Create mysql-to-postgres.etl.xml

Use the following file, replacing the JDBC URLs, usernames, and password placeholders with the values for your databases. The driver paths are relative to the ETL file.

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
    <connection id="source" driver="mysql"
                url="jdbc:mysql://localhost:3306/source_db"
                user="source_user" password="replace-with-source-password"
                classpath="lib/mysql-connector-j.jar"/>
    <connection id="target" driver="postgresql"
                url="jdbc:postgresql://localhost:5432/target_db"
                user="target_user" password="replace-with-target-password"
                classpath="lib/postgresql.jar"/>

    <query connection-id="source">
        SELECT id, name FROM people ORDER BY id;
        <script connection-id="target">
            INSERT INTO people (id, name) VALUES (?id, ?name);
        </script>
    </query>
</etl>

?id and ?name bind the current row's values as JDBC prepared-statement parameters. Leave them unquoted: JDBC handles value types and string escaping, including names containing apostrophes.

Run and verify

From the directory containing the ETL file, run:

scriptella.sh mysql-to-postgres.etl.xml

In your PostgreSQL client, verify the target data:

SELECT id, name FROM people ORDER BY id;
id | name
---+------
 1 | Ada
 2 | Grace

This example inserts rows into an empty target. A second run with the same IDs will fail on the primary key. For repeatable imports, choose an upsert or clear a dedicated staging table before loading.

5. Where to go next

Looking for a section from the previous tutorial? These examples now live in Examples and patterns: