Tutorial 1 - Create Tables In An Existing, Standalone Database

Tutorial 1 - Create Tables In An Existing, Standalone Database#

Let’s use SQL Current to create some new tables in an existing, standalone database. Before proceeding, ensure you’ve followed the steps in the Setup / Install section.

Get a database running#

This is probably the most difficult step if it’s not already done. Make sure you have a database server, such as PostgreSQL or SQL Server, up and running. Also have an empty database created that we can add new tables to.

SQL Current also supports creating the physical database for servers that support it, but that is out of scope for this tutorial.

Write the SQL file create.sql#

Write a PostgreSQL SQL script that creates a couple of tables and save it to create.sql.

create table table1
(
        table1_id int primary key
);

create table table2
(
        table2_id int primary key
);

Note that you could run the above script directly in psql or pgadmin without any changes.

Write the SCS script.txt file#

SQL Current’s language is called SQL Current Script, or SCS for short. Create the following SCS file script.txt and save it in the same directory as create.sql.

database my_db
{
        driver: 'psycopg';
        connString: 'host=127.0.0.1 port=5432 dbname=my_db user=postgres password=postgres';
        create: 'create.sql';
        dir: '';
}

create database mydb;

Here is what happens when you run the above SCS script:

  1. Database my_db is defined.

  2. SQL Current connects to the database server using the psycopg Python driver using the connString property.

  3. SQL Current looks for the create.sql file in the current directory, specified by the empty dir property.

  4. The text inside create.sql is sent to the server to create the new tables.

  5. SQL Current will report success or failure of the script you know what’s going on.

Run the SCS file#

Run the following command in your shell / console / terminal:

% sqlcurrent script.txt

If there are no errors you should see output like this:

my_db: Creating database.
my_db: Running '/Projects/Database_Migrations/create.sql'.
my_db: Success.

However, it’s more common to get errors during initial setup. Check for network connectivity and firewall issues. Verify the credentials are correct. SQL Current will print out any errors or exceptions to the terminal that it encounters. Here is an example of a failed script run against a SQL Server database:

my_db: Creating database.
my_db: Running '/Projects/Database_Migrations/create.sql'.
my_db: Error. (15007, b"'postgres' is not a valid login or you do not have permission.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 11:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 11:\nGeneral SQL Server error: Check messages from the SQL Server\n")

SQL Current will stop for any error.

Continue once you have received a success response.

Verify the tables were created#

Use psql or pgadmin to verify the table was created in your database.

Verify the database version#

select databases;

Verify the update tracking file was created#

SQL Current keeps track of each data in an update tracking file. There is one (1) update tracking file per database definition.

Look for directory sqlcurrent_updatingtracking and find the update tracking file for this database.

Contents