This is part 1 of Ramaze by Example, a tutorial on web development. In Part 0: Installation and Preparation, we prepared the system by installing the necessary software and libraries.

First, we will design the schema for our database. We will be making a todo list web application, so we’re going to need a table for tasks that need to be done.

-- schema.sql
CREATE TABLE tasks (
    id          SERIAL          PRIMARY KEY,
    description VARCHAR( 1024 ) NOT NULL,
    done        BOOLEAN         NOT NULL DEFAULT FALSE
);

To keep things simple, we won’t make any other tables (like a users table or a tasklist table). But let’s add some sample data:

-- data.sql
INSERT INTO tasks ( description )
  VALUES ( 'Learn Ramaze basics.' );
INSERT INTO tasks ( description )
  VALUES ( 'Say hello in Ramaze IRC channel.' );
INSERT INTO tasks ( description )
  VALUES ( 'Write a blog post about Ramaze.' );

I’ve placed these two files in an sql/ subdirectory under the project root. That’s a personal practice, but because this is Ramaze, you are free to do as you please — there are no forced opinions, no conventions you must conform to.

Anyway, that’s about all we need to do with the database! If you are an experienced database user and know how to create the tutorial database, make the schema and insert the test data, then feel free to skip the rest of this part and go on to Part 2: Base Application. If you need help with any of those steps, continue reading.

Setting up the database

PostgreSQL comes with some convenience tools which you can use to set up the tutorial database. First, we create a database user specifically for the tutorial. At your shell prompt, type:

createuser -U postgres -S -D -R todolist

This makes a user named “todolist”. (Run createuser --help if you want to know what the options do.) Then we’ll make the database:

createdb -U postgres -O todolist todolist

Once that’s done, we make the schema in the database:

cat sql/schema.sql | \
psql -U todolist todolist

And populate the table with the sample data:

cat sql/data.sql | \
psql -U todolist todolist

And that’s it! Go now to Part 2: Base Application.

Related posts:

1. Ramaze by Example - Part 6: Adding Tasks
2. Ramaze by Example - Part 3: Model
3. Ramaze by Example - Part 4: View
4. Ramaze by Example - Conclusion
5. Ramaze by Example - Part 2: Base Application