Initializing Schema
Once you've defined your database schema using CQL's Schema.define
, the next step is to initialize the database by creating the necessary tables and structures. In this guide, we will walk through how to define your schema and use Schema.init
to initialize the database.
Real-World Example: Defining the Database Schema
Here's an example where we define a schema for a movie database using CQL. The schema includes tables for movies
, screenplays
, actors
, directors
, and a join table movies_actors
to link movies and actors.
AcmeDB2 = CQL::Schema.define(
:acme_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
table :movies do
primary :id, Int64, auto_increment: true
text :title
end
table :screenplays do
primary :id, Int64, auto_increment: true
bigint :movie_id
text :content
end
table :actors do
primary :id, Int64, auto_increment: true
text :name
end
table :directors do
primary :id, Int64, auto_increment: true
bigint :movie_id
text :name
end
table :movies_actors do
primary :id, Int64, auto_increment: true
bigint :movie_id
bigint :actor_id
end
end
Explanation
In the example above, we define:
movies
: Stores information about movies, including an auto-incrementingid
and atitle
.screenplays
: Stores the screenplay contents for movies, linking to themovies
table withmovie_id
.actors
: Stores actor names, with an auto-incrementingid
.directors
: Stores director names, associated with a movie viamovie_id
.movies_actors
: A join table to linkmovies
andactors
, establishing a many-to-many relationship between movies and actors.
Initializing the Database
Once the schema has been defined, the next step is to initialize the database. This is done by calling the Schema.init
method (or in our case, AcmeDB2.init
), which creates the tables based on the schema.
Steps to Initialize
Set up the environment: Ensure that the
DATABASE_URL
environment variable is correctly set to point to your database connection string (for PostgreSQL in this case).Call
Schema.init
: After defining the schema, you initialize the database by calling theinit
method on the schema object.
AcmeDB2.init
This command creates all the tables and applies the structure you defined in the schema to your actual database.
Full Example: Defining and Initializing the Database
# db_context the schema
AcmeDB2 = CQL::Schema.define(
:acme_db,
adapter: CQL::Adapter::Postgres,
uri: ENV["DATABASE_URL"]
) do
table :movies do
primary :id, Int64, auto_increment: true
text :title
end
table :screenplays do
primary :id, Int64, auto_increment: true
bigint :movie_id
text :content
end
table :actors do
primary :id, Int64, auto_increment: true
text :name
end
table :directors do
primary :id, Int64, auto_increment: true
bigint :movie_id
text :name
end
table :movies_actors do
primary :id, Int64, auto_increment: true
bigint :movie_id
bigint :actor_id
end
end
# Initialize the database
AcmeDB2.init
Generated Database Tables
What Happens During Initialization?
When AcmeDB2.init
is called, the following happens:
The database connection is established using the URI provided in the schema (e.g., the PostgreSQL database connection).
CQL creates the tables (
movies
,screenplays
,actors
,directors
, andmovies_actors
) in the database if they don't already exist.Primary keys, relationships, and any constraints are applied as defined in the schema.
Verifying the Initialization
After calling AcmeDB2.init
, you can verify the tables are created in your database by using a PostgreSQL client or a GUI tool such as pgAdmin
. You should see the tables with their respective columns and relationships as defined in the schema.
-- Example SQL to verify the tables were created
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
-- To check the structure of a table, use:
\d movies -- This shows the structure of the 'movies' table
Summary
Initializing the database after schema creation is a simple process with CQL. After defining your tables and relationships using CQL::Schema.define
, you can call the init
method to apply your schema to the actual database.
This method ensures that your database is correctly structured and ready to use, allowing you to focus on developing the application logic without worrying about manual database setup.
AcmeDB2.init # Initializes the schema and sets up the database
Last updated
Was this helpful?